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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQLAuthority News – Book Review – Programming SQL Server 2005 [ILLUSTRATED]
Next Post
SQL SERVER – Introduction and Example for DATEFORMAT Command

Related Posts

302 Comments. Leave new

  • thanks. it worked for me finally.

    Reply
  • Works like a charm.. Thanks

    Reply
  • Dave, you saved me again. you’re Awesome.

    Reply
  • Thnx a lott. saved me from a lott of work …

    Reply
  • Thanks Man,You rock!!

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

    Reply
  • I didn’t understand ur post plz post clearly
    Thanks

    Reply
  • 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_dataEmpservDB_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_dataEmpservDB_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

    Reply
  • Thanks Pinal, helped me a lot. :)

    Reply
  • great man! Thanks, works totally!

    Reply
  • Very nice solution. Worked without any hitch

    Reply
  • Elisha Horance
    July 31, 2014 8:12 am

    You rule. Like so many solutions, elegantly simple once you know how.

    Reply
  • Thanks it worked

    Reply
  • Excellent !!!! You save mi head!!!

    Reply
  • thanks a lot it helped me :)

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

    Reply
  • By doing above solution i am getting error
    Msg 3241, Level 16, State 7, Line 1
    The media family on device ‘f:\TSPL_CRM.bak’ is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    I am getting error in sql2008 by restoring database and i am not able to do in windows authentication.

    Reply
  • An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) .

    i have installed sql2008 setup i am not able to login in sql by sql server mode. do i have to install anything else

    Reply
  • thanks very much

    Reply
  • iwant to restore database to sql 2005.
    sql 2005 don’t have WITH REPLACE option
    i’m getting the same error
    how can i restore my backup to sql 2005

    Reply

Leave a Reply