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
hi…
Thank u very much…. :)
Thank you!!!
Many thanks for your help :)
Great, saved hours of recreating the database table by table again, and also i said “oh, hope i knew that before, and didn’t waste …..”
Thank you :)
Fantastic ! Thanks a ton !!!
— Juwel
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!
i read it lee .. thx
Great. It worked for me Thanks
great work man,
It simplifies my search
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
Thanks man its a good code
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
great thanks , and i wasn’t expect this solution will be solve my problem.
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 ?
Many thanks Pinal, good things always comes in small packages…this query solved my problem…cheers:)
It Worked ! Thanks :D
Worked like a charm! Thanks!
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
Thanks a lot!
great code. huge thanks!
hey thanks for the great tip! was a big help — jp