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!

About these ads

About floatingfrisbee

A programmer/blogger from New York City
This entry was posted in database, sql and tagged , . Bookmark the permalink.

25 Responses to 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.

  2. pjaaar says:

    Reblogged this on pjaaar and commented:
    How to create a new database from a .bak file. How to not overwrite a database when restore a .bak file.

  3. pjaaar says:

    Thank you for this clear solution! Visit my blog I reblogged it: http://pjaaarnet.wordpress.com/

  4. Amit Bathla says:

    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’

    • 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?

  5. Amit Bathla says:

    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

  6. Hey, Thanks a lot for the post. I have managed to set up the database from the .bak file on my local machine and can now continue working without having to connect to the server.
    Thanks a lot.

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

  8. Baiju says:

    Thanks a lot for saving my remaining strands of hair. It worked just fine.

  9. Bob says:

    Worked Perfectly, Thanks

  10. kasun says:

    Thanks work fine

  11. 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 …

  12. 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.

  13. Hugh says:

    I know people dont alwasy wants to read this but this worked first time for me. Thanks very much.

  14. Miriam says:

    This link on MSDN website has a simple tutorial that explains how to create a new database from a .bak file: http://msdn.microsoft.com/en-us/library/ms186390(v=sql.105).aspx
    In this tutorial, you don’t have to use the command line to restore the file.
    Worked fine for me, I hope it can help other people.
    Regards,

  15. Robin says:

    Thanks Miriam… That worked perfectly fine for me, I was stuck getting errors with the “RESTORE DATABASE” command. This link http://msdn.microsoft.com/en-us/library/ms186390(v=sql.105) shows very simple way to restore backup database to a new database. Thanks again…

  16. 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?

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