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
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
Charming little piece of code!! thanks!
great!
Great! thanks.
check the ‘Restore on Existing Database’ option box if you are using the GUI
Thanks for the GUI (ie novice aka hack) user consideration, Arjun. Very helpful.
Thanks Arjun
Thanks Arjun!
Nice post. Could have stopped our Go live ! Thanks a lot.
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?
Thanks man, I was trying to restoring using the wizard, but it work beautifully, with your code
Thanks for this. Seems small to one who knows but I had some pain trying to restore a db until I found this. Thanks
This helps me a lot!
Great! it helped!
you just saved my life! Thanks!!!
Thankyou so much !! gr8 help
Thank you so much, it worked great
You can also go to the options page of the Restore Data Base dialog (In Management Studio Express) and select the option, Overwrite Existing Database.
Pinal you are great!!!!!
Three cheers for Pinal….right from my heart…..
Thank you very much….
Hi Pinal,
Thank you very much…
It’s working fine….
Simply super coding…
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?
Thanks alot man, helped me a lot :)
I have tried to use the GUI, and the ‘Restore on Existing Database’ option, but I still get the error.