SQL Server 2008 – Creating a database from a .bak file

I’m not a database expert!

Every so often I run into something that seems so simple that should just work but doesn’t. Searching for solution is a tedious chore of sifting through jargon and unclear explanations, somehow more so in database land than with other technologies… what is it with these “database types”?

Anyways, creating a database from a .bak file was one of those things. In SSMS, it should really be as simple as

1. Choose the .bak file
2. Give a name to the destination database
3. Press Go

But it’s not. Yes, there are probably good reason for it… actually no, all those good reasons are just travesty!

By default, you can only restore databases from .bak files that were created from them. If you try to do something else, you will get the following error.

Similarly if you try to restore using T-SQL

RESTORE DATABASE newdatabase
FROM DISK = 'c:\DatabaseBackups\dev2012021601.bak'

You will see the following error if the database “newdatabase” already exists

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'jb' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

or something like this one if it doesn’t already exist

Msg 1834, Level 16, State 1, Line 1
The file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\devdb.mdf' cannot be overwritten. It is being used by database 'devdb'.
Msg 3156, Level 16, State 4, Line 1
File 'dbname' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\devdb.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\devdb_1' cannot be overwritten. It is being used by database 'devdb'.
Msg 3156, Level 16, State 4, Line 1
File 'devdb_log' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\devdb_1.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

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:\DatabaseBackups\dev2012021601.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 dbForSocialMigration 
FROM DISK = 'c:\DatabaseBackups\dev20120307.bak' 
WITH 
MOVE 'dbname' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\dbForSocialMigration.mdf', 
MOVE 'dbname_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\dbForSocialMigration_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!