This is a step by step guide which will show you how to restore a .bak file in Microsoft SQL Server Management Studio. This technique can be used to move databases between servers or restore an existing database to a previous state.
This step by step guide is complemented by these two guides which will show you how to create the .bak file in the first place:
- Backup your database using Microsoft SQL Server Management Studio Express (Export to .bak)
- Backup your database using an SQL command (Export to .bak)
Like I said, we are going to cover two scenarios here; moving a database and restoring an existing database. In the first case you're going to have create a dummy database so that you can restore over the top of it.
This tutorial requires Microsoft SQL Server Management Studio. If its not installed on your computer (it isn't installed by default when you install sql server) you will need to follow this guide to get it on your system:
Now that I have covered the prerequisites we can get started. Load up SQL Server Management Studio by clicking:
- All programs
- Microsoft Sql Server 2008
- SQL Server Management Studio
When it loads you will need to connect to the SQL server instance. If you are using the free SQL server express then it will be called .\SQLEXPRESS and you should be able to use Integrated Windows Authentication.
After you have connected you will be presented with a list of your databases. If you are moving this database from a different SQL server then you will need to create a dummy database first so right click on the Database node and choose New Database. Type the database name and click OK. If you're just restoring a database then go to the next step.
Now we are ready to restore our database from a .bak file. Right click on the database that you want to restore and choose:
The restore database dialog will appear. At this point it might look like everything is ready for you to restore - a backup set is listed but this is not the correct one. The backup set is actually the same database that you are trying to restore over. To select your .bak file you will need to choose the "From device" radio button and then click the […] button over to the right hand side of that row. The button to click is highlighted with a blue border in this image (click to enlarge):
The Specify Backup dialog will appear. The backup media is preselected to File which is fine. Click the Add button. This dialog has always has a short delay (it had it in SQL Server 2005 and on any server/computer I have tried it on).
When it does pop up you will see that the Sql Server Tools team have decided to implement a file browser which looks like it came straight out of Windows 3.1! You can't paste your file path in anywhere, you have to put just the path in on one line and the file name on another. And that presumes you know know the exact filename, simply pasting in the path does not let you browse that folder. If you want to browse the folder you will have to manually click each node in the file tree to find your .bak.
Anyway as long as you keep your .bak files in the Sql Server designated Backup folder these annoyances will be minimised. At any rate - select your .bak file and click OK.
This will drop you back down to the Specify Backup dialog. Just click OK.
Now you will be back in the main Restore Database dialog. In the "Select the backup sets to restore" gridview you will see a list of all the backup sets the .bak file you selected contains. Tick the one that you want to restore from.
Don't click OK yet! We still have one more step to do. To stop the backup failing with an error message such as "The backup set holds a backup of a database other than the existing 'OtherDatabase' database." you will have to set the database to overwrite mode.
To set this mode you simply click the Options page which is listed down the left hand side of the dialog under "Select a page". This will switch the dialog to the Options page.
Simply tick the top checkbox which is labelled "Overwrite the existing database (WITH REPLACE) and click OK.
A progress circle will rotate briefly in the bottom left hand corner of the dialog and when it has finished restoring the database you will see a dialog like this:
Congratulations, you have successfully restored your database from a .bak file!
Error - Exclusive access could not be obtained because the database is in use. (Microsoft SqlServer.Express.Smo)
If you see this error when trying to restore your backup then you will need to restart your sql server to free up all the handles and gain exclusive access. To do this you just have to:
- Load Sql Server Management Studio
- Connect to your database
- Right click on the top most node in the Object Explorer (your Sql Server instance)
- Choose Restart.
- Wait a few seconds while the service restarts. If you get disconnected then just reconnect to the server.
You can now follow the original tutorial above to restore your database backup.