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)
302 Comments. Leave new
Great. It worked for me, Thank Pinal Dave so much
Error 3154: The backup set holds a backup of a database other than the existing database.
(Full View His Sample)
https://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/
Thanks LuisTiet.
Solved the issue. Thanks Pinal :)
arsalwali – Thanks for letting me know. I am glad that it was helpful.
Awesome! It worked. Thank you very much for your help, Pinal Dave!
Awesome! It worked! Thanks Pinal Dave!
Thank you, It works and it helps me…
Funny, this used to work but it doesn’t anymore
Got it – You have to right click on Databases in SQL Server Mgt Studio and do the restore from there with the option set as mentioned in this article. Right clicking on the particular database and trying to restore from there will not.
Glad that you would it helpful.
Excellent Solution, thanks for the great tip !
great workaround thanks a lot…
brilliant stuff, works a charm!
thanks! Worked for me too. Didn’t know where to put the REPLACE but figured it out from another of your posts.
Thank you very much. Very Helpful!!!!!
Hello i want to backup up a database from a windows 2000 server and retore it on 2008 and i was getting this error.. How can i get this done.. Thanks
Did you mean SQL 2000 to SQL 2008? You can do direct restore. What’s the error? Use “WITH REPLACE”
Hi Pinal,
I’m getting the same error.
But in my case i’m trying to restore a database from sql 2014 to sql 2014 express. This could be the reason of the problem? If yes, there’s solution?
Did you try WITH REPLACE?
there is also a work around you don’t need to replace the backup files if they already exist and it is used by another DB.
Solution:
Don’t create an empty database and restore the .bak file on to it.
Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.
Also change the file names at “Files” if the other database still exists. Otherwise you get “The file ‘…’ cannot be overwritten. It is being used by database ‘yourFirstDb'”.
Thanks for sharing it Saad Sheikh
Using smo tried to restore 2008 database into 2016 database but getting error for unable to get exclusive access to the database. Implement all the possible factor to solve the issue but no luck yet. Can you please help me with it.
Thanks,
Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume ‘C:\’ to create the database. The database requires 33648934912 additional free bytes, while only 9142841344 bytes are available.
Msg 3119, Level 16, State 4, 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.
I have some error when run this script please help me.