If you want to create a working backup of one of your production databases that you can use without jeopardizing the production data, you’ll need to copy the database in its entirety to a new name. In this article I’ll review the steps you need to use to do this (in SQL Server Management Studio 2014).
Also, if you find this article helpful, feel free to check out the other articles on my site covering various SQL examples at: https://jwcooney.com/category/sql/
Overview
Somewhat unintuitively, the SQL Server Database ‘Copy’ function is only really useful for duplicating a database on a different SQL Server but retaining it’s existing name. As handy as this can be, you may wish to have the database copied on the same SQL Server under a different name. In that case, the best option for you is to restore the database from backup.
Steps to Restore a Database into a New Copy
Below are the steps that you’ll need to take to restore a database from backup into a newly named database, and then optionally to rename the database to something else.
- Log onto the physical server that is hosting your SQL Server instance
- Log into SQL Server Management Studio (SSMS) with your sa level account that also has permission to the folder containing the SQL Server backup file. Note that I am using SSMS 2014
- Before you start it’s important to note that you should not create a new blank database to restore into… you specify a new blank name in the actual restore screen as the target destination, otherwise the restore will fail.
- In SSMS, right-click on ‘Databases’ (this is the node right below the server name main node)
- Now click the option to ‘Restore Database’
- You will see three left hand side navigational tabs: General, Files, Options
- Under the General Tab:
- Under Source, click the ‘Device’ radio button.
- Click the ‘…‘ button
- In the Select backup devices popup window, click the ‘Add’ button
- Now browse the filesystem to the correct .bak file. Note that the results are usually oldest, least relevant backup first, with lots of .trn files that are of limited use. Scroll to the very end for the most useful .bak file version.
- Click Ok and then Ok again. You should now be in the original ‘Restore Database’ screen with the correct .bak selected
- *IMPORTANT* Under ‘Destination’ enter an entirely new database name. If you enter an existing one, the backup will fail with an unclear error message.
- Under the Files Tab make sure to click the checkbox to: Relocate all files to folder. Also make sure to set your file paths to somewhere unique since you don’t want to introduce problems by having multiple databases pointing to the same location.
- Under the Options Tab:
- Check the checkbox titled: Overwrite the existng database (WITH REPLACE)
- Check the checkbox titled: Preserve the replication settings (WITH KEEP REPLICATION)
- Un-Check the checkbox titled: Tail-Log backup
- Now with all of these options checked, click the OK button.
- Right-click the Databases node in SSMS to refresh your list of databases. You should now see your newly created restore database.
Renaming your New Database:
This step is obviously optional since you already have a backup of your database on the same server. However, if you do want to do some renaming (for example, if you want to soft archive the existing database and make the backed up version the live version). Here’s how you can do this:
- Likely now you’ll want to archive (aka: rename) your old database and also rename your new database
- To rename a database, first you’ll need to be the only user using it by setting it to: Single User.
- Right-click the database to rename and click Properties
- Now click on Options in the left hand navigational bar.
- Scroll to the bottom of the ‘Other Options’ list. Under the ‘State’ heading you will see the ‘Restrict Access’ option
- Use the drop-down list to change the Access from MULTI_USER to SINGLE_USER
- Click the OK button
- Now you can rename the database either by right clicking it in SSMS and selecting the ‘Rename’ option, OR using TSql
USE master GO ALTER DATABASE NewDB Modify Name = ANewDBName
- Finally, after your database has been successfully renamed, you’ll want to allow access to it again. To do so, change the Access from SINGLE_USER back to MULTI_USER
Changing the Database Owner
It’s likely that you’ll also want to reassign ownership of the database to another user account. Here is the command you can use to do so. Just remember to enter your database name as well as the user account that you want to transfer ownership to.
ALTER AUTHORIZATION ON DATABASE::EnterDBName TO UserAccount GO
Conclusion
I hope these steps have been clear and helpful. Feel free to write me a note in the comments section below if you have any questions or feedback.
Justin Cooney 