Restoring SQL Server backups manually

After installing SQL Server Express 2005 and the Studio Express application I set to to restore a database from a SQL backup file (.BAK file). Quite a simple task you first might think? Not when you have the data sets stored on one partition, logs on another and the catalog on another. First off Studio Express threw up lots of errors which didn’t really help me (”The data is the error” – nice one Microsoft!!).

After much google searching and finding no real helpful answers I decided to check out the technet manuals.

I found the following:

RESTORE FILELISTONLY FROM DISK = ‘[full file system path to the .BAK file]‘

This command displays the logical sets and the physical paths they should be stored. From this I was able to see the “LogicalNames” of each of the data sets in the backup and where they are currently stored. From here I used the following command:

RESTORE DATABASE [name of database (doesn't need to exist)] FROM DISK = ‘[full file system path to the .BAK file]‘ WITH MOVE ‘[LogicalName]‘ TO ‘[destination file name]‘, REPLACE

As your backup may include multiple sets (LogicalNames) you can duplicate the MOVE ‘..’ TO ‘..’, section as many times for each of the respective row returned in the first query.

Hope this saves someone else some time..