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 a lot :)
If you use SQLExpress instead of applying these commands, follow these steps:
1-Select the base you created for the restoration right
2 – Tasks> Restore> Database
A new window will open.
1 – Select “From Device” and click on … to choose the origin
2 – Click Add
3 – Select aorigem Base and OK and OK
4 – Check the box where his base appears to be restored
5 – Go to Options and check the “Overwrite the existing base”
So far, this seems more complicated than just typing the command, but in practice it is not. It does not take even 5 seconds.
If you use SQLExpress instead of applying these commands, follow these steps:
1-Select the base you created for the restoration, and click the right mouse button
2 – Tasks> Restore> Database
A new window will open.
1 – Select “From Device” and click on … to choose the origin
2 – Click Add
3 – Select aorigem Base and OK and OK
4 – Check the box where his base appears to be restored
5 – Go to Options and check the “Overwrite the existing base”
So far, this seems more complicated than just typing the command, but in practice it is not. It does not take not even 5 seconds.
“The comment above is bad transalted, choose this one end ignore this line for post”
Thank you sir,
You saved my day
Hi pinal,
I need your help in SQL server restoring issue. Error is as follows.
“The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)”
I know that it is not possible to restore SQL server 2005 database in SQL server 2000 database.
Do we have any way to export the data from 2005 and import it to SQL server 2000?
Please assist me.
Hi
I do not agree that all those answers are for the subject :
The backup set holds a backup of a database other than the existing database
Yes you could do : restore with replace
But the answer for the above subject is :
If you get that message “The backup set holds a backup of a database other than the existing database”
This is happening with a restore to SS 2005 from a SS2000 backup. This article says to use the ‘WITH REPLACE’ clause. The SS2005 management studio does not seem to allow this via the GUI.
Hi,
I am new to SQL Server.I have faced the same problem.When i used the query which you have given i got an error
Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘India’ 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.
Please help me to resolve
Regards
Nidhin
Hi,
First of all..thanks a lot for this post..It helped me to restore.
I tried from GUI (slect override checkbox)..but didnt work.
Answer for last post – Connect to master database and then try this, it would work.
Rgds,
Ekta
Hello Alex Farias,
Great job.Double Thanks for the tips. It works.
Venki
ThankYou,
Thanks,
Tnx………………………
It worked,…
Thanks for this beautiful little piece of code, saved a lot of effort!!!
Thanks pinal
Thank you so much. Thank you for your time, for publish that kind of tips .
Dear Mr. Pinal,
I have a database in my organizaion. DB File Size is about 4GB and Transaction Logfile Size is about 82 GB. I am taking full backup of the database everyday at 12:10 AM by a job. When I tried to restore the database in another Server its after 48 hours… the Database restore is still going on. The Source Server is IBM x3600 8GB RAM M2 with 1TB Space and Target Server is IBM x3600 16GB RAM M3 with 1TB Space.
I also tried the following thing:
1. Backup only PRIMARY Filegroup on Target Server by
Backup Database FileGroup=’PRIMARY’ TO DISK=’C:\test.bak’
2. Copy test.bak from Source Server C: Drive to Target Server C:\
3. Restore that database on Target Server by
Restore Database FROM DISK=’C:\test.bak’
But still it shows me “The backup set holds a backup of a database other than the existing ” Message.
Could you please help
Thanks and Regards
Aziz
That worked great! Thanks!
Thank u so much…
Thank u so much your blog have solved my problem so many times.
Thanks a lot.
how to restore sqb backup in sql . in this back up contain 3 mdf file and 1 log file .please guide me to restore
Great! It worked for me…. Thanks a lot Pinal!!!!!!