MS SQL 2008. Create new database from .bak file

gistfile1.txt

From http://codeonaboat.wordpress.com/2012/02/16/sql-server-2008-creating-a-database-from-a-bak-file/

If that’s where you are stuck, the following will help you.

1. In SSMS, open  a query window in the master database of the database server. That’s where you will run the following queries.

2. See what the “LogicalName” of the database that has been backed up in the .bak file is 

RESTORE FILELISTONLY 
FROM DISK = 'c:\tmp\tridioncm.bak'
This will give you the logical name of the database and its associated log file. Lets assume that the names are “dbname” and “dbname_log”

3. Now run the following restore command. Make sure that a database with the name you are trying to create doesn’t already exist (in the code sample below, dbForSocialMigration doesn’t exist).

RESTORE DATABASE tridion_cm_XXXX
FROM DISK = 'c:\tmp\tridioncm.bak' 
WITH 
MOVE 'tridion_cm' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tridion_cm_XXXX.mdf', 
MOVE 'tridion_cm_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tridion_cm_XXXX_log.mdf'

 C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA is the directory where SQL Express usually keeps its data files. You can find the directory your database server is using by selecting a database from it, right clicking and opening the properties dialog and selecting the “Files” option from the left.

That should work, and at that point you should be able to access the database “dbForSocialMigration” populated with all the tables and data from the .bak file.

Good Luck!