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
Execellent one..
Thanks you are a star…
Thank a lot ! With a tiny REPLACE that works great !
Funcionó de 10. Gracias
Thanks; Such a simple fix; I have heard of people doing all sorts of things like attaching the .mdf files etc but this worked perfectly :)
Please update the installation instructions to use the SQL command
RESTORE DATABASE ScotlandYard FROM DISK = ‘[path to backup folder]\ScotlandYard.bak’ WITH REPLACE
as the instructions posted don’t work.
You are genius….
Hi Pinal,
I followed the same as mentioned by you. But I am still getting the same error message
I have trouble with multiple restore, get the media issue
[Msg 3231, Level 16, State 1, Line 1 The media loaded on “D:\db\db1.bak” is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.]
Trying to restore one bak file with REPLACE option is replacing the previously restored tables. But without REPLACE option get the following error
[Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing ‘SIMSDB’ database.]
Any ideas?
Man, you are amazing, i find the answers always in your blog :), thanks so much
Thanks
Thank you.
hi pinal i had tried to restore the database by using your way but it give this error msg 3102 level 16,state 1,line 2
restore cannot process database ‘mydatabase’ because it is use by this session it is recommended that the master database be used when performing this operation 3013 level 16 state 1, line 2
restore database is terminating abnormally
Thanks its really helped
I’ve got same error,
I fixed it by selecting the Overwrite the existing database option
Hi Pinal,
i am new to sql development and I was trying this..
when I replaced “With move” with “with Replace” it says
Replace is not a recognized option.
where did I go wrong..
Worked great! So simply explained. You are the best!
Thank you very much!
Worked great! Thank you sir.
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I’m getting this error. Any thoughts? I’m not running anything on this DB.