This document will show you how to use SQL Server Management Studio to restore a database from a backup. Please see the HowTo: Backup a SQL Server Database topic for a walk-through of the process of backing up a database.
To perform the steps in this document you will need one of the SQL Server Management Studio editions mentioned in the Applies To section. This guide uses the SQL Server 2005 Management Studio Express edition, which is freely available from the link given in the Applies To section.
If you are working with a database on a server managed by an IT department, make sure you consult your IT department before following any of these steps. They may already have particular backup policies that you will need to adhere to.
If you are unsure and have an IT department, you should consult them first.
|Step 1||Start SQL Server Management studio and select the server to which you want to connect.|
|Step 2||Once you are connected, you should see a screen similar to the one on the left.|
|Step 3||Right click the "Databases" folder and choose "Restore Database..."|
|Step 4||The "Restore Database" dialog appears, click the "From Device" option, and then click the button to the right of it ("...")|
A dialog will appear to ask you for the file you wish to restore, make sure the drop down list is showing "File", and click "Add".
Browse for the database backup file, and click "OK"
The backup file will appear in the list, click "OK"
|Step 8||Information about the backup will appear in list, make sure the correct backup is ticked (there might be more than one backup set in a single backup file).|
|Step 9||If you don't know the name of the original database, scroll the list to the right and it will be shown under the "Database" column.|
Choose either Option A or Option B below
Option A - Restoring to a New Database
Type the original name of the database into the "To database" text box at the top of the dialog.
Option B - Restoring to Overwrite an Existing Database
***Please make sure any existing database being overwritten has been backed up prior to performing this operation***
Select the database to be overwritten from the To database drop down
Click Options from the Select a page section
Check the option to Overwrite the existing database
You are now ready to restore the database, click "OK".
|Step 11||When the restore is complete, a message similar to the one on the left will appear.|
There are a few things that can go wrong when restoring a database, the two most common are dealt with below.
This means that you didn't select any of the check boxes in the list on the main restore dialog.
It is fairly common to see the message below:
To resolve the problem, all you need to do is tell SQL Server to restore to a drive and directory that does exist.
Click the "Options" page on the main restore dialog:
Here you can see “SampleGroup” and “SampleGroup_log”, the first of these is the database file that the restore wizard is going to create, the second is something called a transaction log file – don’t worry about what it does, all you need to do, is to make sure that the “Restore As” column contains valid paths – you can either browse for a correct path, or type one in.
Once you’ve picked valid locations, click “OK” again and the restore should succeed.
|Knowledge Base Article Ref:||KB13010806|