SEARCH

How-To Geek

Restoring a SQL Database Backup Using SQL Server Management Studio

We have previously covered a simple SQL database restore using the command line which is ideal for restoring backup files created on the same SQL Server installation, however if you are restoring a backup created on a different installation or simply prefer a point and click interface, using SQL Server Management Studio (or the Express edition) makes this task easy.

Note: SQL Server experts might want to skip today’s lesson, as it’s aimed at beginners.

Before starting, you will need to copy the SQL backup file (typically has a .BAK extension) to a local hard drive on the destination SQL Server machine.

Open SQL Server Management Studio and login to the SQL Server you want to restore the database to. It is best to either login as a Windows Administrator or as the SQL ‘sa’ user.

image

Once logged in, right click on the Databases folder and select ‘Restore Database’.

image

Click the ellipses button next to ‘From device’ under the ‘Source for restore’ section.

image

Set ‘File’ as the backup media and then click ‘Add’.

image

Browse to the SQL backup (BAK) file you want to restore.

image

image

In the Restore Database dialog, type or select the name of the database you want this backup restored to.

  • If you select an existing database, it will be replaced with the data from the backup.
  • If you type a database name which does not currently exist in your SQL Server installation, it will be created.

Next, select the restore point you want to use. Since a SQL backup file can hold multiple backups you may see more than one restore point listed.

image

At this point, enough information has been entered for the database to be restored. However, SQL backup files store information about where data files are copied so if there are any file system problems such as a the destination directory not existing or conflicting data file names an error will occur. These problems are common when restoring a backup created on a different SQL Server installation.

To review and change the file system settings, click the Options page on the left in the Restore Database dialog.

image

On the options page, you will want to make sure the ‘Restore As’ column points to valid folder locations (you can change them as needed). The the files do not have to exist, however the folder path must exist. If the respective files do exist, SQL Server follows a simple set of rules:

  • If the ‘To database’ (from the General page) matches the restore database backup (i.e. restoring to matching databases), the respective files will be overwritten as part of the restore.
  • If the ‘To database’ does not match the restore database backup (i.e. restoring to a different database), the ‘Overwrite the existing database’ will need to be checked for the restore process to complete. Use this function with caution as you can potentially restore database backup information on top of data files from a completely different database.

Generally, you can tell the databases differ based on the ‘Original File Name’ which is the internal name SQL Server uses to reference the respective files.

image

Once your restore options are set, click Ok.

image

Conclusion

SQL Server Management Studio makes the restore process simple and is ideal if you seldom perform database restores. This process works in every version of SQL Server from Express to Enterprise. If you are using the Express version, you can download SQL Server Management Studio Express to get access to this interface.

Links

Download SQL Server Management Studio Express from Microsoft

Jason Faulkner is a developer and IT professional who never has a hot cup of coffee far away. Interact with him on Google+

  • Published 07/30/10

Comments (1)

  1. Jayvee

    Hi! Im having issues restoring an sql backup created using an express version to enterprise. Keep on getting error that the version is not compatible. I am using sql 2008.

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!