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
Worked great! Thank you sir.
Thank you. There is several times that you blog was save my time…
thanks a lot..
thank you it’s helpful
Thanks mate, helped me get around an issue.
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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLDEVMSSQLBackupDBname .bak’
WITH REPLACE
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
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
Is that a valid backup? How was the backup done?
Thank you very much for such a great help
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..
Fantastic – as alway, clear and simple instructions. Thank you
Thank you pinal
Thanks, It worked like a charm!
It works!!! Thank you!
v v thanxs sir
Man!! you are a genious!!!! thanks!!
Excellent Pinal Dave!!!!! Very simple & effective solution, saved lot of time & efforts :)
Worked for me, thanks!
Many thanks! Saved me some time.
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