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!

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

30 thoughts on “SQL Server 2008 – Creating a database from a .bak file

  1. sporo says:

    this also gives the same error :
    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “c:\Progam Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Windows.mdf” failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
    Msg 3156, Level 16, State 3, Line 1
    File ‘Magnum_Windows’ cannot be restored to ‘c:\Progam Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Windows.mdf’. Use WITH MOVE to identify a valid location for the file.
    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “c:\Progam Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Windows_log.mdf” failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
    Msg 3156, Level 16, State 3, Line 1
    File ‘Magnum_Windows_log’ cannot be restored to ‘c:\Progam Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Windows_log.mdf’. 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.

    1. Can you post the output from the following command?

      RESTORE FILELISTONLY
      FROM DISK = ‘name of your .bak file’

      And also can you post the exact command that you are using for the restore?

  2. It gives the same error in SQL server 2008 R2
    ********Error******
    Msg 3154, Level 16, State 4, Line 2
    The backup set holds a backup of a database other than the existing ‘PR’ database.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    *********command i tried**********
    RESTORE DATABASE PR
    FROM DISK = ‘c:\vcloud_backup_2012_08_14_220005_2904190.bak’
    WITH
    MOVE ‘vcloud’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PR.mdf’,
    MOVE ‘vcloud_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PR_log.ldf’

    1. What is the output of this command?

      RESTORE FILELISTONLY
      FROM DISK = ‘name of your .bak file’

      Also, do the database “PR” and the files

      C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PR.mdf
      C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PR_log.ldf

      already exist?

  3. This is the output of RESTORE FILELISTONLY command
    vcloud d:\sqldata\vcloud.mdf D PRIMARY 19946668032 41943040000 ……
    vcloud_log l:\sqldata\vcloud.ldf L NULL 6811549696 2199023255552 ……

    Yes, Database PR and those files already exists.
    ***************
    If DB don’t exist, it throws another error
    Msg 3118, Level 16, State 1, Line 1
    The database “PR1″ does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Thanks for the help

    1. I’m not sure why you are getting Msg 3118… if the database PR and its files don’t already exist, your RESTORE statement looks like it should have worked.

      The only difference I can see is that the physical names of your data and log files (from your RESTORE FILELISTONLY command) show different drive names.

      Maybe this link is helpful?

      http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/4d419f83-9415-4669-a1c4-1e57a2587cb0

  4. Gary says:

    Gives the same error message as before.

    restore database db1
    from disk = ‘C:\file.bak’
    with
    move ‘datafile’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\data.mdf’
    ,move ‘datalog’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\data.ldf’,
    norecovery

  5. Prescott Chartier says:

    Don’t know if this blog is still monitored but here goes …..

    I have a database backup file eTicketDB created with SQL Server 2005 Standard Edition and want to restore it to SQL Server 2008 R2 Enterprise Edition. The FILELISTONLY option returns:

    eTicketDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\eTicketDB.mdf D PRIMARY 3145728 35184372080640 1 0 0 CCCF3E91-2A2A-4F32-832E-9AFFB76F47C8 0 0 2555904 512 1 NULL 60000000028800049 69D4DFFF-7CB7-4672-94FD-5ACCCACEA567 0 1 NULL
    eTicketDB_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\eTicketDB_log.ldf L NULL 5832704 2199023255552 2 0 0 DA2F33E4-0863-4E95-86CC-11CCF6FC514D 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

    I then run the following restore command:

    RESTORE DATABASE eTicketDB
    FROM DISK = ‘C:\backup\eTicketDB_BackUp.bak’
    WITH
    MOVE ‘eTicketDB’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eTicketDB.mdf’,
    MOVE ‘eTicketDB_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eTicketDB.ldf

    The database eTicketDB does NOT exist, I get the following error:

    Msg 3118, Level 16, State 1, Line 1
    The database “eTicketDB” does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Any assistance would be greatly appreciated. Thanks,

    Prescott …

  6. Paula says:

    Thanks – this was just what I needed. I got my database restored with no dramas after I put the .bak files on the server where SQL Server was.

  7. follow your post ..

    my Query

    RESTORE DATABASE dbForSocialMigration
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.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’

    and this is error

    Msg 3132, Level 16, State 1, Line 1
    The media set has 2 media families but only 1 are provided. All members must be provided.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    how to fix it?

  8. PRASAD RANE says:

    Find Logical names as said above from
    STEP 1 and STEP 2:
    RESTORE FILELISTONLY
    FROM DISK = ‘c:\DatabaseBackups\dev2012021601.bak’

    (You will get 2 file names like ‘dbname’ and ‘dbname_log’ ) as stated above

    STEP 3:
    ********************************** LOOK HERE **********************************
    But only small mistake in STEP 3 is – the file extension for ‘dbname_log’ . It should be .ldf (and not .mdf) as given below :
    ********************************************************************

    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.ldf’

    Just try. Now it works completely fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s