How to Restore a MSSQL 2005 Database

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. Most likely, 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 add ‘everyone’ to have complete acess 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. Under “Destination for restore” and then in the “To database” pulldown, choose the database that you created in step 4.
8. Under “Source for restore” click the “From device” radiobutton, 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. Then in the right column of this window, click options.
11. First, click the checkbox “Overwrite the existing database”
12. Now, you have 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.