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:
- Start
- 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:
- Tasks
- Restore
- Database…
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.
18 comments :
Hello,
I've a problem while trying to restore a .bak
This file is on a network drive but SqlServer Management Studio doesn't want it. I could just select files which are on the C:
Have you a solution?
Thanks, David.
David, it depends what level of access you have to the server. You could map a drive letter to it and access it that way? Or if you can download the .bak file you could mount it in another server and then script its contents out to a .sql file: http://forums.asp.net/p/1487540/3489098.aspx#3489098
i get an new error message when i try to restore:
Exclusive access could not be obtained because the database is in use. (Microsoft SqlServer.Express.Smo)
please help
ashysys, this is because somebody has visited the website recently and the file is marked as in use. You can restart the mysql server to get around this. When you are connected to your sql server you can right click on the server node in the Object Explorer and choose "restart".
very helpful article and staright forward.
very use full Artical .it help me a lot
I get an error message that says "db was backed up on a server running version 10.50.2550. That version is incompatible with this server which is running version 10.00.2531. Either restore the db on a server that supports the backup or use a backup that is compatible with this server."
How can I run the version required.
I am on SQL 2008 server R2
nice and easy article, keep it up
Helpful. Thanks
Hi, I've spent ALL DAY with this problem, and seen many many wrong answers, and wasted alot of time. This is the only anwer that worked.....can't thank you enough
Hi
when I specify the path to the file, the grid does not populate and system generates an error 'specified cast is not valid'. how can I go thru this????
As said by you that after ticking on "OVERWRITE" option click on "OK", but even after doing that my "OK" button still remains disabled so how can I click "OK"????
Thank you! It's very clear.
thank you verymuch
ITS VERY
USEFUL
I am facing error while restoring .bak file to new database.
Error says: "The backup set holds backup of a database other than the existing'' database."
Also i already selected that option from option page.
But still i am facing this issue.
FYI: I am having incremental .bak file. Please gimme some suggestion to restore this to new local database.
Thanks.
I am facing error while restoring .bak file to new database.
Error says: "The backup set holds backup of a database other than the existing'' database."
Also i already selected that option from option page.
But still i am facing this issue.
FYI: I am having incremental .bak file. Please gimme some suggestion to restore this to new local database.
Thanks.
I am facing error while restoring .bak file to new database.
Error says: "The backup set holds backup of a database other than the existing'' database."
Also i already selected that option from option page.
But still i am facing this issue.
FYI: I am having incremental .bak file. Please gimme some suggestion to restore this to new local database.
Thanks.
Post a Comment