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
WITH REPLACE – thanks! You’re in my favorites now.
thank u very much sir
it saved my time
gr8 man gr8
gr8, with using above code I restored database file of sql server 2005 in sql server 2008.
thank you for your interesting posts…
thank you!! :)
Server: Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “G:\SQL DATA\MSSQL\DATA\material_data1.ndf” failed with the operating system error 3(The system cannot find the path specified.).
Server: Msg 3156, Level 16, State 1, Line 1
File ‘material_1_Data’ cannot be restored to ‘G:\SQL DATA\MSSQL\DATA\material_data1.ndf’. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
This is really helpful to take the backups.
Hello ,
i want to restore database in sql server but i have ony *.ibu
file.i have used this syntax but i am failed….
can you give me some solution how to restore this database?
Thanks a lot. you just help me again and again.
:D
Thanks man !!!
You really saved valueable time :)
Cheers !
Excellent,
Thanks. I knew it was simple; just needed the reminder
This is very helpful solution to me.
Thanks for your greatfull help realy it is too simple but for professionals like you maybe we are too beginners to find the solution in such way you do.
thanks a lot…this cud be best solution.
Saved my gourd. Had accidentally ran script which fouled up production database files in big way. Original restoration procedure failed. That simple code (we rarely use) restored , accountants are now back at work.
Thanks,
its great to have people like you around!
Thanks a tonnnnnn……
Its working fine
Thanks
Thank you very much