Restore SQL Server Database from .BAK backup file

You would think that restoring a backup would be fairly straight forward, but usual, this isn't always the case. We need to create an MDF (database) and LDF (log file) from the .BAK file, and plug this into SQL Server. urgh.

First things first... Run the below command to find out the logical names of the MDF and LDF files. I recommand running this under the master database or you will receive a 'database in use' error message.

RESTORE FILELISTONLY

FROM DISK = 'C:\dbbackup.bak'

Now we have these names, we can restore to them using the following command. We can specify a UNC path to the BAK file incase we want to update over a network, but the MDF and LDF file paths cannot be UNC.

RESTORE DATABASE [TESTDB]

FROM DISK = 'C:\dbbackup.bak'

WITH REPLACE,

MOVE 'TESTDB' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TESTDB.MDF',

MOVE 'TESTDB_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TESTDB_log.LDF'

The database name will be the logical name of the MDF file... In my case, I could not get this to work until I had created a database with the same name before hand.
Ensure the directories above exist otherwise it will not work. I chose to place them with my other SQL Server table definitions.