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! worked for me!
For SQL2005 do not use ‘WITH REPLACE’ but only ‘REPLACE’ indeed, the word WITH must not be present
Thanks Pinal , this script worked for me.
At first I thought the script was freezing because I ran it for 25 minutes and it didn’t do anything. I thought it was freezing up.
I finally added “STATS = 1” to the script because I needed to see when the script completed at 1% intervals. It turns out that the database I was restoring was so big (60+ GBs) that the script ran for nearly 2 hours.
Thanks again!
-Jody
It works really great for me. Thank You
Thanks Pinal Sir, it worked for me loads of thanks
Great post !!!!
Thanks much :)
Thanks for sharing your expertise, it helped me restore
my databases successfully.
:-)
Thanks alot !!
workedi n my case .:)
Excellent post!!!!!!!!!!!!!!!!!!
Thank you very much! :D It has solved my problem!
Thank you very much ! It works well.
Once again, you have provided a solution to another problem I encountered!
Thank you very much, and keep up the god work :)
Hi Pinal
hope u doing good, i used your code and also i tried to restore my backup with Overwrite flag, still having some issue.
Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘Database Name’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks,
Jimmy
Thank you so much for this! :)
Hello Jimmy,
First take the database in SINGLE_USER mode and then execute the restore command. For details please visit the below article:
Regards,
Pinal Dave
Excellent Solution, thanks for the great tip !
I want to restore the database with a new name, so I can use it as my test database
Yes you can do it by giving different database name while restoring it
Really interesting…thanks a lot.
thank you sir it’s working.