How to Restore a MSSQL 2005 Database

Restoring a MSSQL 2005 database can be hell for those that do not know how to do it properly. Especially if you are restoring this database on a different server than what it was created on.

I have watched developers spend hours on this simple, but trivial task. And, I at times, have even found myself spending entirely way too much time trying to restore a backup. It’s one of those tasks that pops up (for me anyway) only a few times a year. So, to save time, I thought it would be helpful to document the process.

Now, if there is something I missed, or if there is a better way of doing this, please let me know.

Thanks, and good luck.

Oh, and just for your reference, and if you want to sift through the ocean of info – Microsoft explains it here

Restoring a MSSQL 2005 Database
(to a database on a different server)

1. Copy your .bak file to: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
2. The security permissions should be sufficient, however, there are times when I run into security issues. So I simply right click on the .bak file and on the security tab, I go through the process, and allow ‘everyone’ to have complete access to the .bak file.
3. Launch Microsoft SQL Management Studio.
4. Create a new database – This will create a .mdf and a .ldf in this directory: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
5. In the SQL Server manager Object Explorer, right click on Databases and choose “Restore database…”
6. This will open the “Restore Database” window.
7. Chose “Destination for restore”, then in the “To database” pull down, choose the database that you created in step 4.
8. Under “Source for restore” click the “From device” radio button, then click the browse option. This will open a window to “Specify Backup”. Click “Add” then navigate to the path where you put your .bak file (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup). Choose your .bak file and click “OK”, then “OK” agian.
9. You should now see your backup file loaded in the grid. Click the checkbox next to your backup.
10. In the right column of this window, click options.
11. First, click the checkbox “Overwrite the existing database”
12. Now, you need to set the location of the .mdf and .ldf you want to restore. Remember when you created the new database in step 4 – and it created a new .mdf and .ldf? Well, these are the two files you want to restore. You should see at the least, two rows in the grid (under “Restore the database files as”). The first one should be the name of the database you are restoring, and the second one will probably end in “_log”.
13. Click the browse option for the first File name. Then navigate to .mdf file you created in step 4. (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) Click the .mdf file you created and then click “OK”
14. You are going to repeat the same process for the next item in the grid (ending with _log). Click the browse option for the second File name. Then navigate to .ldf file you created in step 4. (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) Click the .mdf file you created and then click “OK”.
15. After doing all of the above steps, you should be ready to rock and roll. Click the “OK” button and the backup should begin.

Obviously, if you get a “success” message, the backup worked. If you get an “error” message…well, try try again.

Tagged as: database, restore, sql server

Related posts:

1. Online Communities – Migration And Control Over The Community Database
2. 5 Ways To Improve Your Adsense Earnings

Tagged as: database, restore, sql server