Steps to Restore a Database into a New Copy
- 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:
- 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
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.