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 bunch for the tip!
Hi Pinal,
I took a full back-up from a server and tried to restore it on another server by creating a new database.
Since it was a full-back up, I had three files
abc.mdf
abc_log.ldf
sysft_abcCatalog
When I try to restore, the other machine did not have full-text installed. Thus I went for partial restore with replace option enable…. but the replace does not work.
I get the error Error 3154: The backup set holds a backup of a database other than the existing database. ….. even through I have written REPLACE option in the restore query:
Below is my query:
RESTORE DATABASE XYZ
FROM DISK = ‘C:nusrathabc.bak’
WITH REPLACE,
FILE=1,
PARTIAL,
MOVE ‘abc’ TO ‘c:DATAXYZ.mdf’,
MOVE ‘abc_log’ TO ‘c:DATAXYZ_log.ldf’,
NOUNLOAD,
STATS = 10
How should I work around ? Please help.
Thanks,
Nusrath
Did you ever get your question answered? I’m having the exact same problem and can’t get it to restore because it is looking for the full text catalog location. Please help!
@Dan Did you get a resolution? I’m having the same problem.
I resolved this by renaming my mdf and ldf files to match the source mdf and ldf file names. I used the WITH REPLACE option, though I’m not sure if it was required in my case or not.
I was just able to migrate a 2000 sql database to a 2008 with this little tidbit.
THANX!
H! Pinal , thanks a lot .i tried da way u suggested . i am able to create database from my backup file . but still getting db blank with no tables . i am using ms-sql 2005 server.
could any one suggest me whats the issue i m facing .
i am doin like”RESTORE DATABASE RelinkDB
FROM DISK = ‘C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBackupRelinkDB.bak’
WITH REPLACE”
Thanks mate. Your post was quite useful.
Great hint. Cheers!
THanxxxxxxxxxxxxxxxxxxxx a lot!
Thank you!!!!! I don’t think i’ve ever had an issue resolved so fast and simply!!!
Thanks for this, I was having the same problem, but that has fixed it!
Thank you. That was a big help.
Dax
Great Article! It helped us a lot. Thank you very much!
Thanks Man! :D
This is really a great work around and it resolved the issue.
Thanks a lot Pinal
Thanks for the tip, Pinal. I’ve had to use it on several occasions.
Hello Nusrath
I had exactly the same issue. It seems to happend when we make a backup of several databases appendes on a same bak fIle.
In my case I had one backup file named J04Vendredi.BAK on which where 4 databases backup
1-master
2-msdb
3-lienWinXS
4-aeosdb
I wanted to restore aeosdb on a database called aeosdbLFSB on the target server located physically ON
C:\Data\aeosdbLFSB.mdf’,
C:\Data\aeosdbLFSB_log.ldf’,
After 2 hours of unsuccessfull tries, searches on the net, in the doc and a lot of dirty words I got it that way:
You have to know what was the name of the logical file on the source server with
RESTORE FILELISTONLY
FROM disk = ‘C:\LFSBVM002\J04Vendredi.BAK’ (replace with your path and filename
WITH file = 4
(You have to specify the sequence number where is the wished database !!!)
The first colum showed the logical names of the source server
-aeosdb_dat
-aeosdb_log
Then, I had to write the SQL statement below
RESTORE DATABASE aeosdbLFSB
FROM DISK = ‘C:\LFSBVM002\J04Vendredi.BAK’
WITH
REPLACE,
MOVE ‘aeosdb_dat’ TO ‘C:\Data\aeosdbLFSB.mdf’,
MOVE ‘aeosdb_log’ TO ‘C:\Data\aeosdbLFSB_log.ldf’,
FILE=4
@Pierre, Thanks a lot. You solved my problem :)
Thank you very much!
Thanks alot!!! Your code very useful…
Thanks a lot. Worked in minutes !!
You saved me a lot of time. Thanks!!!
cool, solve my issue:
RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\BackupAdventureworks.bak’
WITH REPLACE