Restoring Up a SQL Database
Occasionally you may need to restore a database. Below are the steps to restore a database.
Click here for a PDF copy of this article.
Step 1: Open Microsoft SQL Server Management Studio.
Start\All Programs\Microsoft SQL Server 20xx\SQL Server Management Studio
Step 2: Log into SQL Server. Login with the SA account or windows account with sufficient privileges.
Step 3: Expand Databases by clicking on the + sign next to it. Your database should be listed below.
Step 4: Right click on your database. A fly out menu should appear. Click on the listed item “Tasks” and select “Restore” from the options. Select “Database” from the fly out menu.
Step 5: The Restore Database – Windows will open. On the General Page, click on the Device radio button and the box with the ellipsis to expand the “Select backup devices” dialog box.
Step 6: Click the Add button on the Select backup devices dialog box.
Step 7: The “Locate Backup File” dialog box will appear. Locate your backup file and click the “OK” button.
Step 8: The backup file will be listed in the “Select backup devices” dialog box. Click the “OK” button.
Step 9: The backup file will appear listed in the “Backup Sets to restore:” window. Click the Options page.
Step 10: Click on the “Overwrite the existing database (WITH REPLACE)” checkbox.
Step 11: Click the “OK” Button to restore your database.
Step 12: If the restore is successful, the following prompt will appear. Click the OK button.
Restore of Database ‘ACCESSCONTROL’ failed.
Database restore fails if the database is in use. To overcome this, stop all the services associated with the program that is connecting to the database, or restart the SQL server and restore the database before anything else can connect to it.
Proceed with steps 1-10 above preparing the restore process right up until the point you click the “OK” button to execute the command.
Right click on the database server.
Select the “restart” option.
The following prompt will appear. “Are you sure you want to restart the MSSQLSERVER service on (your computer name)?”
Click the “OK” Button.
You will receive a progress bar indicating the progress of the restart.
Once the restart has completed successfully, you will see the green arrow button next to the database server. Now, click the “OK” button to complete the restore. You must be quick to complete this after the restart or the database will be reconnected and communicating with the program that is holding on it and the restore will fail again.