Saturday, 31 October 2009

Learn exactly how you can restore a .bak file in SQL Management Studio with this step-by-step guide

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:

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:

    1. Start
    2. All programs
    3. Microsoft Sql Server 2008
    4. 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.

mssms-restorebak-login

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.

mssms-restorebak-newdatabase

Now we are ready to restore our database from a .bak file. Right click on the database that you want to restore and choose:

  1. Tasks
  2. Restore
  3. Database…

mssms-restorebak-step1

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):

mssms-restorebak-step2

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

mssms-restorebak-step3

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.

mssms-restorebak-step4

This will drop you back down to the Specify Backup dialog. Just click OK.

mssms-restorebak-step5

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.

mssms-restorebak-step6

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.

mssms-restorebak-error 

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.

mssms-restorebak-step7

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:

mssms-restorebak-step8

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:

  1. Load Sql Server Management Studio
  2. Connect to your database
  3. Right click on the top most node in the Object Explorer (your Sql Server instance)
  4. Choose Restart.
  5. 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.

kick it Shout it vote it on WebDevVote.com

19 comments:

David said...

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.

rtpHarry said...

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

ashsys said...

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

rtpHarry said...

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

jagdeesh.net said...

Good article

Anonymous said...

very helpful article and staright forward.

Ali Hasan said...

very use full Artical .it help me a lot

Anonymous said...

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

habib said...

nice and easy article, keep it up

vihar said...

Helpful. Thanks

Ollie said...

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

Anonymous said...

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????

Basileen Sebastian said...

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

Anonymous said...

Thank you! It's very clear.

Anonymous said...

thank you verymuch

Anand said...

ITS VERY
USEFUL

Rajesh - INDIA said...

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.

Rajesh - INDIA said...

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.

Rajesh - INDIA said...

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.


-->