Restoring a database - In depth guide
Note: the instructions below cover the same process as in the quick guide above but provide more information about the various options being selected.
Launch SQL Server Management Studio from the Start menu.
Select the server and instance that you are using to hold your database. For example, if you are using a local SQL Server with an instance name of “CCHSQL”, this would be select as follows:
If you have appropriate security rights, you can log in using Windows Authentication, otherwise select SQL Server Authentication from the dropdown and login with the “sa” user account that you created when the server instance was installed.
The contents of the server will be displayed in the panel on the left:
Right-click on the Databases folder and click Restore Database in the popup menu. The Restore Database dialog box will appear.
If you wish to restore the data to a new database, type the name of the database to be created in the To database field. If you wish to restore the data overwriting an existing database, select the name of the database from the drop-down list
Warning: if you restore to an existing database this will fully overwrite the existing contents of the database and the original contents will not be retrievable.
Leave the setting in the To a point in time as Most recent possible as, if you are restoring a database backup using the above instructions, the file will contain a full back up.
Select the from device radio button and click on the ellipsis button at the end of the field to display the Specify Backup dialog.
Leave Backup media set to File and click the Add button to select the file to restore from. The Locate Backup File dialog will appear.
Locate the folder containing the file and expand it, then double click on the file name.
Note: if you did not give the original file name an extension of .bak, it will not appear in the folder and you will have to manually type the name in the File name field and then click on OK. Alternatively, you can select All files in the Files of type drop-down to show the file in the folder.
The backup location will appear in the list, check it is correct and click on OK. The system will return to the Restore Database dialog showing the details of the backup file you have selected.
If this is the correct backup, tick the checkbox in the Restore column to confirm that this is the file that you wish to restore from.
Click Options in the panel on the left:
In Restore options, tick Overwrite the existing database (WITH REPLACE). If you are restoring to a new database it is not actually necessary to tick this, however, if you are restoring over an existing database it will need to be set. Leave the other options as set by default.
Leave the recover state options as set by default.
Finally, click on OK to start the restore. The system will show the progress of the restore at the bottom of the left panel and display a success message at the end of the process.