SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

Our Jr. DBA ran to me with this error just a few days ago while restoring the database. SQL Server showed Error 3154, and the fix was simpler than he thought.

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.

What SQL Server is actually telling you

The message sounds confusing, so here it is in plain words. You asked SQL Server to restore over a database that already exists, and the backup file came from a different database. SQL Server will not quietly write one database over another, so it stops and asks you to be explicit. That is a safety feature, not a bug, and the day it saves you from restoring the wrong backup over production you will be glad it is there.

Check what is inside the backup first

Before you force anything, look at the backup. This takes two seconds and tells you which database it came from and when it was taken.

RESTORE HEADERONLY
FROM DISK = 'C:\Backup\AdventureWorks.bak';

Read the DatabaseName and BackupFinishDate columns. If they are not what you expected, stop right there. You have the wrong file, and no amount of REPLACE will make it the right one.

Fix 1: use WITH REPLACE

If you are sure, WITH REPLACE tells SQL Server that you mean it. View Example

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\AdventureWorks.bak'
WITH REPLACE;

Fix 2: drop the conflicting database

Delete the older database which is conflicting and restore again using RESTORE command. If nothing needs that database, this is the cleaner route, because you start from nothing and there is no chance of confusion.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

When the file paths are different too

Restoring onto a different server usually brings a second error right behind this one, because the original data and log paths do not exist on the new machine. Get the logical names first.

RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\AdventureWorks.bak';

Take the values from the LogicalName column and use them with MOVE. Note the logical names are the ones inside the backup and rarely match your file names on disk.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\AdventureWorks.bak'
WITH REPLACE,
     MOVE 'AdventureWorks_Data' TO 'D:\Data\AdventureWorks.mdf',
     MOVE 'AdventureWorks_Log'  TO 'E:\Log\AdventureWorks_log.ldf',
     STATS = 10;

STATS = 10 prints progress every ten percent, which keeps you sane on a large restore.

One warning about REPLACE

REPLACE switches off a safety check, so it is worth knowing which one. Normally SQL Server refuses to overwrite a database without first backing up the tail of its log, so that the work since the last backup is not lost. REPLACE says skip that. If the database you are about to overwrite holds anything you still need, back it up before you type REPLACE, not after.

Restoring to a brand new database name instead of overwriting one is often the safer move. You can compare the two and drop the old one once you are happy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.