SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

Our Jr. DBA ran to me with this error just a few days ago while restoring the database.

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command. View Example

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

273 thoughts on “SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

  1. This was a huge help.
    But I think you forgot the slash in the example
    FROM DISK = ‘C:\

    (it didn’t work for me until I added the slash.)

    Anyways, thanks a lot, you saved me from having to go to my senior dba

    • Hi pinal,

      i have dought in sql admin :

      1) how to check in sql server port number in command prompt.

      2)how to check database or table which extent is using , where can i find out , any query is there please tell me

      Thanks&Regrads
      harish

  2. Good for you man!!!!! A question: Why it did not work when I tried to do it through the RESTORE option available in Microsoft SQL 2005? The script generates “WITH FILE = 1, NOUNLOAD, STATS = 10″ instead of “WITH REPLACE”. Did I miss anything in the set up?

  3. Great article, especiall full example with MOVE. BTW, is there some workaround in order not to use all the files-to-move? In my case there was 5 files or so. Is there some way to point all of them to some default location?

  4. Thanks a lot for this post. People using the GUI should tick the following option: “Overwrite the existing database”. (SQL Server Management Studio Express)

  5. If you are using the wizard you can achieve exactly the same by clicking the radio button that says “restore over the existing database” (or similar) it’s the top option on the options bit … but nobody’s gonna read my comment it’s too low on the page!

  6. Hi Pinal Dave,

    And thanks Pinal Dave too much you know why . b/c when ever i have problem with sql server query i search in your site and i got the rite solution :) .
    i m happy to see you when ever i see your site.

    i have RESTORE DB in sql server 2000 so i use your this solution.

    RESTORE DATABASE AdventureWorks
    FROM DISK = ‘C:\BackupAdventureworks.bak’
    WITH REPLACE

    it is very halp full for me Thanks again Dave :)
    ok
    Take care
    bye bye

  7. hi,
    thanks for the response.
    right now i am searching for sql dba openings.
    can any body help me the important and frequent interview questions on core sql dba.

    thanks

  8. Great Idea, but if I want to restore without replacing the existing database ?? I want to insert tables inside the backup file to be added to the tables already there in the existing database !!
    Any help ?

  9. These 2 lines of codes save my lots times, whenever i goes to restore dayabase i always face this problem … I search problem on google and always yout these lines works ,.

    Thanks a ton

  10. Hi Pinal,
    I took a full back-up from a server and tried to restore it on another server by creating a new database.

    Since it was a full-back up, I had three files

    abc.mdf
    abc_log.ldf
    sysft_abcCatalog

    When I try to restore, the other machine did not have full-text installed. Thus I went for partial restore with replace option enable…. but the replace does not work.

    I get the error Error 3154: The backup set holds a backup of a database other than the existing database. ….. even through I have written REPLACE option in the restore query:

    Below is my query:

    RESTORE DATABASE XYZ
    FROM DISK = ‘C:\nusrath\abc.bak’
    WITH REPLACE,
    FILE=1,
    PARTIAL,
    MOVE ‘abc’ TO ‘c:\DATA\XYZ.mdf’,
    MOVE ‘abc_log’ TO ‘c:\DATA\XYZ_log.ldf’,
    NOUNLOAD,
    STATS = 10

    How should I work around ? Please help.

    Thanks,

    Nusrath

    • Did you ever get your question answered? I’m having the exact same problem and can’t get it to restore because it is looking for the full text catalog location. Please help!

  11. H! Pinal , thanks a lot .i tried da way u suggested . i am able to create database from my backup file . but still getting db blank with no tables . i am using ms-sql 2005 server.
    could any one suggest me whats the issue i m facing .
    i am doin like”RESTORE DATABASE RelinkDB
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\RelinkDB.bak’
    WITH REPLACE”

  12. Hello Nusrath

    I had exactly the same issue. It seems to happend when we make a backup of several databases appendes on a same bak fIle.
    In my case I had one backup file named J04Vendredi.BAK on which where 4 databases backup
    1-master
    2-msdb
    3-lienWinXS
    4-aeosdb

    I wanted to restore aeosdb on a database called aeosdbLFSB on the target server located physically ON

    C:\Data\aeosdbLFSB.mdf’,
    C:\Data\aeosdbLFSB_log.ldf’,

    After 2 hours of unsuccessfull tries, searches on the net, in the doc and a lot of dirty words I got it that way:

    You have to know what was the name of the logical file on the source server with

    RESTORE FILELISTONLY
    FROM disk = ‘C:\LFSBVM002\J04Vendredi.BAK’ (replace with your path and filename

    WITH file = 4
    (You have to specify the sequence number where is the wished database !!!)

    The first colum showed the logical names of the source server
    -aeosdb_dat
    -aeosdb_log

    Then, I had to write the SQL statement below
    RESTORE DATABASE aeosdbLFSB
    FROM DISK = ‘C:\LFSBVM002\J04Vendredi.BAK’

    WITH
    REPLACE,
    MOVE ‘aeosdb_dat’ TO ‘C:\Data\aeosdbLFSB.mdf’,
    MOVE ‘aeosdb_log’ TO ‘C:\Data\aeosdbLFSB_log.ldf’,

    FILE=4

  13. Hi,
    For developing/testing purposes with original data I need to restore / copy data from one database to another with same database structure (tables, SPs, views, etc) but a different database name. from a backup file from original.
    What is the best approach for this? CAn I use the restore command for this?

  14. Hi, I got a question…I backuped my database from an Win2003 Server English Edition and when I try to restore the database into my development enviorment (wich is an XP Professional Spanish Edition) it shows an error like this:
    Mens. 5133, Nivel 16, Estado 1, Línea 1
    Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDataBase.mdf” failed with the operating system error 3(El sistema no puede hallar la ruta especificada.).
    Mens. 3156, Nivel 16, Estado 3, Línea 1

    Of course I dont Have “Program Files”, in spanish it is called “Archivos de Programa” I dont how I can change that..hope you can help me…Thanks..
    (by the way, El sistema no puede hallar la ruta especificada. = The system could not find the specified path.)

  15. hai,

    i tried many time to restore backup file through GUI restore.
    i wasn’t able to restore.
    i tried your query within second finished.

    Many Thanks,

    Bala

  16. Thanks Pinal , this script worked for me.

    At first I thought the script was freezing because I ran it for 25 minutes and it didn’t do anything. I thought it was freezing up.

    I finally added “STATS = 1″ to the script because I needed to see when the script completed at 1% intervals. It turns out that the database I was restoring was so big (60+ GBs) that the script ran for nearly 2 hours.

    Thanks again!

    -Jody

  17. Hi Pinal

    hope u doing good, i used your code and also i tried to restore my backup with Overwrite flag, still having some issue.

    Msg 3102, Level 16, State 1, Line 1
    RESTORE cannot process database ‘Database Name’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    thanks,
    Jimmy

  18. Server: Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “G:\SQL DATA\MSSQL\DATA\material_data1.ndf” failed with the operating system error 3(The system cannot find the path specified.).
    Server: Msg 3156, Level 16, State 1, Line 1
    File ‘material_1_Data’ cannot be restored to ‘G:\SQL DATA\MSSQL\DATA\material_data1.ndf’. Use WITH MOVE to identify a valid location for the file.
    Server: Msg 3119, Level 16, State 1, Line 1
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  19. Hello ,

    i want to restore database in sql server but i have ony *.ibu
    file.i have used this syntax but i am failed….

    can you give me some solution how to restore this database?

  20. Thanks for your greatfull help realy it is too simple but for professionals like you maybe we are too beginners to find the solution in such way you do.

  21. Saved my gourd. Had accidentally ran script which fouled up production database files in big way. Original restoration procedure failed. That simple code (we rarely use) restored , accountants are now back at work.

  22. If you use SQLExpress instead of applying these commands, follow these steps:

    1-Select the base you created for the restoration right
    2 – Tasks> Restore> Database
    A new window will open.

    1 – Select “From Device” and click on … to choose the origin
    2 – Click Add
    3 – Select aorigem Base and OK and OK
    4 – Check the box where his base appears to be restored
    5 – Go to Options and check the “Overwrite the existing base”

    So far, this seems more complicated than just typing the command, but in practice it is not. It does not take even 5 seconds.

  23. If you use SQLExpress instead of applying these commands, follow these steps:

    1-Select the base you created for the restoration, and click the right mouse button
    2 – Tasks> Restore> Database
    A new window will open.

    1 – Select “From Device” and click on … to choose the origin
    2 – Click Add
    3 – Select aorigem Base and OK and OK
    4 – Check the box where his base appears to be restored
    5 – Go to Options and check the “Overwrite the existing base”

    So far, this seems more complicated than just typing the command, but in practice it is not. It does not take not even 5 seconds.

    “The comment above is bad transalted, choose this one end ignore this line for post”

  24. Hi pinal,

    I need your help in SQL server restoring issue. Error is as follows.

    “The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)”

    I know that it is not possible to restore SQL server 2005 database in SQL server 2000 database.

    Do we have any way to export the data from 2005 and import it to SQL server 2000?

    Please assist me.

  25. Hi
    I do not agree that all those answers are for the subject :
    The backup set holds a backup of a database other than the existing database

    Yes you could do : restore with replace

    But the answer for the above subject is :
    If you get that message “The backup set holds a backup of a database other than the existing database”

    This is happening with a restore to SS 2005 from a SS2000 backup. This article says to use the ‘WITH REPLACE’ clause. The SS2005 management studio does not seem to allow this via the GUI.

  26. Hi,

    I am new to SQL Server.I have faced the same problem.When i used the query which you have given i got an error

    Msg 3102, Level 16, State 1, Line 1
    RESTORE cannot process database ‘India’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Please help me to resolve

    Regards
    Nidhin

  27. Hi,

    First of all..thanks a lot for this post..It helped me to restore.

    I tried from GUI (slect override checkbox)..but didnt work.

    Answer for last post – Connect to master database and then try this, it would work.

    Rgds,
    Ekta

  28. Dear Mr. Pinal,

    I have a database in my organizaion. DB File Size is about 4GB and Transaction Logfile Size is about 82 GB. I am taking full backup of the database everyday at 12:10 AM by a job. When I tried to restore the database in another Server its after 48 hours… the Database restore is still going on. The Source Server is IBM x3600 8GB RAM M2 with 1TB Space and Target Server is IBM x3600 16GB RAM M3 with 1TB Space.

    I also tried the following thing:
    1. Backup only PRIMARY Filegroup on Target Server by
    Backup Database FileGroup=’PRIMARY’ TO DISK=’C:\test.bak’
    2. Copy test.bak from Source Server C: Drive to Target Server C:\
    3. Restore that database on Target Server by
    Restore Database FROM DISK=’C:\test.bak’

    But still it shows me “The backup set holds a backup of a database other than the existing ” Message.

    Could you please help

    Thanks and Regards
    Aziz

  29. Pinal Deva ki jai ho…… ,

    I was trying to restore that database by wizard and also selecting Replace / overwirte than too its was going me Error:3154
    And i tried by query it work perfect …. dont know how this happen but my problem get solved for now i will find the cause y such happen …..

    ThnQ boss :)

    cu soon
    in TechDays :)

  30. Hi Guys,

    This is for all you techie’s who like me might have scratched their head for sometime.

    Thanks for all the people who had posted in this page, reading your postings I could arrive at my solution.

    I had to use a combination of both REPLACE and MOVE commands.

    My posting is done based on SQL 2005. I hadn’t tested it in other versions.

    My Situation:
    I backed up a database in T-SQL, created a new DB with a diff name and used the backup file to restore onto the new DB and came up with the dreaded error – “The backup set holds a backup of a database other than the existing ‘xxx’ database.”

    My Predicament:
    I cannot use the Enterprise manager, because this is an application where the coding is done and it is a process that needs to create the database doing the above steps.

    My Solution:
    RESTORE DATABASE NewDBName FROM DISK = N’C:\OldDB.BAK’ WITH REPLACE, MOVE ‘OldDBLogicalName_data’ TO ‘C:\Location\NewDB_Data.mdf’, MOVE ‘OldDBLogicalName_Log’ TO ‘C:\Location\NewDB_Log.ldf’, STATS = 10

  31. Hi pinal,

    I need your help in SQL server restoring issue. Error is as follows.

    “The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)”

    I know that it is not possible to restore SQL server 2005 database in SQL server 2000 database.

    Do we have any way to export the data from 2005 and import it to SQL server 2000?

    Please assist me.

  32. Hi All,

    I tried to restore a .bak file from 2005 SQL server to 2008 SQL server, but the process errors out using the above method. I have got a list of errors, one of them is

    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “M:\MSSQL\MARKET\MARKET_primary_01.mdf” failed with the operating system error 3(The system cannot find the path specified.).

    The process is referring to M:\MSSQL\MARKET, the location where the MARKET_primary_01.mdf was residing originally in the source server, whereas it should refer to the location where the destination sever is.(correct me if I am wrong)

    Most of the errors are for the incorrect locations.

    Can anyone help me out with this?

    Thanks in advance.

    Regards,
    Litesh

  33. Hi Pinal

    1)How to restore the .bak file in sql server 2005..
    But that .bak was created by using sql server 2000….

    2) How to store the IP address..

  34. Hi Pinal,

    1. I have 30 GB database, this database contain 3 mdf files and 1 log file. It’s possible to merge 1 mdf files. if possible how we can do it.

    2.My drive free space contain 70 GB while i Restore 30 GB database It’s required nearly required 82 GB why it’s happen

    Please guide on this………….

  35. Thanks
    For me it worked with the WITH MOVE clause

    RESTORE DATABASE [XXX]
    FROM DISK = ‘D:\XXX.bak’
    WITH MOVE XXX’ TO N’D:\MSSQL\MSSQL.2\MSSQL\DATA\XXX.mdf’
    , MOVE N’XXX_log’ TO N’D:\MSSQL\MSSQL.2\MSSQL\DATA\XXX.ldf’
    , NOUNLOAD, REPLACE, STATS = 10

    Cheers

  36. Please update the installation instructions to use the SQL command
    RESTORE DATABASE ScotlandYard FROM DISK = ‘[path to backup folder]\ScotlandYard.bak’ WITH REPLACE
    as the instructions posted don’t work.

  37. I have trouble with multiple restore, get the media issue

    [Msg 3231, Level 16, State 1, Line 1 The media loaded on “D:\db\db1.bak” is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.]

    Trying to restore one bak file with REPLACE option is replacing the previously restored tables. But without REPLACE option get the following error

    [Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing ‘SIMSDB’ database.]

    Any ideas?

  38. hi pinal i had tried to restore the database by using your way but it give this error msg 3102 level 16,state 1,line 2
    restore cannot process database ‘mydatabase’ because it is use by this session it is recommended that the master database be used when performing this operation 3013 level 16 state 1, line 2
    restore database is terminating abnormally

  39. Hi Pinal,

    i am new to sql development and I was trying this..
    when I replaced “With move” with “with Replace” it says
    Replace is not a recognized option.
    where did I go wrong..

  40. Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    I’m getting this error. Any thoughts? I’m not running anything on this DB.

  41. I use this script to drop active connection before the restore script

    USE [master]
    GO
    ALTER DATABASE [X] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    cheers

    • Thanks for this tip. I used this before I could restore as below:
      USE [master]
      GO
      ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      GO

      RESTORE DATABASE DBname
      FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLDEV\MSSQL\Backup\DBname .bak’
      WITH REPLACE

  42. Wonderful! Wonderful! Wonderful!…thanks for your help….i deleted the database folder causing the conflict and just restore the backup…i did not use command, just the GUI….. thanks

  43. Hi Pinal,

    As per your advise i have given the correct path to restore the database,but its not succeed.
    here my SQL query to restore the database is

    restore database customer_test
    from disk = ‘D:\DB BAK\customer.bak’ with replace

    after execute this query i got an error :

    Msg 3203, Level 16, State 1, Line 4
    Read on “D:\DB BAK\customer.bak” failed: 13(The data is invalid.)
    Msg 3013, Level 16, State 1, Line 4
    RESTORE DATABASE is terminating abnormally.

    The database size is : 13GB
    SQL Server Version : 2008R2

    kindly give me suggestion in this

  44. vrish

    1) i want to create a procedure to copy 1 database schema including all objects (views,index,procedures,tables,table data etc.) to another database schema within 1 server without generating script..but with SMO object
    2) after getting all schema i want to get all updates which is done in source database without droping the previous data and schema..with SMO object..
    plz let me knw as early… thank you..

  45. So simple but soooo effective. I was setting up a new server and wanted to restore the data to a db with the same name but kept getting the error above. 8 plus wasted hours until I found this code. Oh yeah, the gui works as well using the Option: Overwrite Existing Database in the restore wizard. Hard to believe this advice is 5 yrs old. Thanks

  46. hello,
    Can any one tell me the erroe occuring to restore the database in sql server
    script :
    RESTORE DATABASE TESTProjectENGPAY
    FROM DISK = ‘F:\New folder\BACKUP.bak’
    WITH REPLACE

    Error

    Msg 1834, Level 16, State 1, Line 1
    The file ‘E:\MSSQL\PROJECTENGPAY.mdf’ cannot be overwritten. It is being used by database ‘PROJECTENGPAY’.
    Msg 3156, Level 16, State 4, Line 1
    File ‘PROJECTENGPAY’ cannot be restored to ‘E:\MSSQL\PROJECTENGPAY.mdf’. Use WITH MOVE to identify a valid location for the file.
    Msg 1834, Level 16, State 1, Line 1
    The file ‘E:\MSSQL\PROJECTENGPAY_log.ldf’ cannot be overwritten. It is being used by database ‘PROJECTENGPAY’.
    Msg 3156, Level 16, State 4, Line 1
    File ‘PROJECTENGPAY_log’ cannot be restored to ‘E:\MSSQL\PROJECTENGPAY_log.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.

  47. Pingback: SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database – SSMS | Journey to SQL Authority with Pinal Dave

  48. this solution not work because i get this error message

    Msg 5133, Level 16, State 1, Line 4
    Directory lookup for the file “J:\sql_data\EmpservDB_BK1_1.ndf” failed with the operating system error 3(The system cannot find the path specified.).
    Msg 3156, Level 16, State 3, Line 4
    File ‘EmpservDB_FG1′ cannot be restored to ‘J:\sql_data\EmpservDB_BK1_1.ndf’. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 4
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 4
    RESTORE DATABASE is terminating abnormally.

    Pls Succession For Me

  49. This solution was not working for me. I had an empty database & try to restore with replace. It was throwing same error. Than I deleted empty database & tried to restore the bak file, bak file created the database itself & restored it successfully.

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