Home Search

DriveWorks Pro 22
How To: Restore a SQL Server Database (KB13010806)

Send Feedback

How To: Restore a SQL Server Database

This topic demonstrates 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 information on backing up a SQL database.

Applies To:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2005 Express
  • Microsoft SQL Server 2008
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2012
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2016
  • Microsoft SQL Server 2016 Express
  • Microsoft SQL Server 2019
  • Microsoft SQL Server 2019 Express

Prerequisites

To perform the steps in this document you will need one of the SQL Server Management Studio editions mentioned in the Applies To section. The guides below use the SQL Server Management Studio version 2005, 2012 and 2017 as well as SQL Server Express editions 2005, 2012 and 2016, which is freely available from the link given in the Applies To section.

To connect to SQL Server 2005 or SQL Server 2005 Express you can use any of the listed (or later) versions of the Management Studio.

To connect to SQL Server 2008 or SQL Server 2008 Express you will need one of the 2008 editions (or later) of the Management Studio.

To connect to SQL Server 2008 RC or SQL Server 2008 RC Express you will need one of the 2008 RC editions (or later) of the Management Studio.

To connect to SQL Server 2012 or SQL Server 2012 Express you will need one of the 2012 editions (or later) of the Management Studio.

To connect to SQL Server 2014 or SQL Server 2014 Express you will need one of the 2014 editions (or later) of the Management Studio.

To connect to SQL Server 2016 or SQL Server 2016 Express you will need one of the 2016 editions (or later) of the Management Studio.

Steps

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.

Expand the appropriate section for the version of SQL Server you are using:

SQL Server 2005 and 2008
Step 1Dialogue boxStart SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Right click the "Databases" folder and choose "Restore Database..."
Step 4The "Restore Database" dialog appears, click the "Device" option, and then click the button to the right of it (...)
Step 5A 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".
Step 6Browse for the database backup file, and click "OK"
Step 7The backup file will appear in the list, click "OK"
Step 8Information 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 9If 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.
Step 10Choose 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 11When the restore is complete, a message similar to the one on the left will appear.
SQL Server 2012 and 2014
Step 1Connecting to Microsoft SQL Server 2012.Start SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Right click the "Databases" folder and choose "Restore Database..."
Step 4The "Restore Database" dialog appears, click the "Device" option, and then click the button to the right of it (...)
Step 5A 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".
Step 6Browse for the database backup file, and click "OK".
Step 7The backup file will appear in the list, click "OK".
Step 8Information 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 9If 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.
Step 10Choose 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 11When the restore is complete, a message similar to the one on the left will appear.
SQL Server 2016 and newer
Step 1Start SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Right click the "Databases" folder and choose "Restore Database..."
Step 4The "Restore Database" dialog appears, click the "Device" option, and then click the button to the right of it (...)
Step 5

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".

Step 6

Browse for the database backup file, and click "OK"

Step 7The backup file will appear in the list, click "OK"
Step 8Information 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 9If 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.
Step 10Choose 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 11When the restore is complete, a message similar to the one on the left will appear.

Troubleshooting

There are a few things that can go wrong when restoring a database, the most common are described below.

You must select a restore source

This means that you didn't select any of the check boxes in the list on the main restore dialog.

Restore failed for Server...

It is fairly common to see the message below:

When SQL Server restores a database, it restores it to the same location as the original database. However, if the original database was on a drive that doesn't exist, or if the directory it was originally in doesn't exist, you will get the above error message.

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
© DriveWorks Ltd 2024 - All rights reserved.
Privacy Policy | Terms & Conditions