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
Thanks… I restored the database correctly… Once again thanks…
Thanks Alot!
Really Greate and Wonderful
Hi,
For developing/testing purposes with original data I need to restore / copy data from one database to another with same database structure (tables, SPs, views, etc) but a different database name. from a backup file from original.
What is the best approach for this? CAn I use the restore command for this?
Take a backup of the existing database and restore it by giving new database name
Thanks for the article…
Have a nice day.
It worked!!
Thanks
U DA MAN
Thanks Pinal. You have very good common sense in programming. Now let’s see what cool chakra graphic I get!
Hi, I got a question…I backuped my database from an Win2003 Server English Edition and when I try to restore the database into my development enviorment (wich is an XP Professional Spanish Edition) it shows an error like this:
Mens. 5133, Nivel 16, Estado 1, LÃnea 1
Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDataBase.mdf” failed with the operating system error 3(El sistema no puede hallar la ruta especificada.).
Mens. 3156, Nivel 16, Estado 3, LÃnea 1
Of course I dont Have “Program Files”, in spanish it is called “Archivos de Programa” I dont how I can change that..hope you can help me…Thanks..
(by the way, El sistema no puede hallar la ruta especificada. = The system could not find the specified path.)
hai,
i tried many time to restore backup file through GUI restore.
i wasn’t able to restore.
i tried your query within second finished.
Many Thanks,
Bala
Thank you so much, your solution worked for me!!
thanks a lot, this was the my problem.
this solved my problem..
thank a lot
Great!!! Works like a charm :)
Sorry, it doesnt work for me please help…
Thank you very much for your simple example. It works
Another individual happy to have stumbled upon your post. Thanks for sharing.
Thank you sir! I can go to bed now!!
Thanks a lot. It work !!!!!!!!!!!
Thanks a lot for this is very helpful
You’re the king man, a great piece of code that helped me a lot. Thanks kindly