The Backup Set Holds a Backup of a Database Other Than the Existing Database (Error 3154)

This is a safety check, not a corruption. There is a database called Sales on this instance, your backup file also contains a database called Sales, and SQL Server can tell they are not the same database. It is refusing to overwrite one with the other until you say that is what you meant.

Names match. Lineage does not. A database restored from a different server, or dropped and recreated, carries a different family GUID even with an identical name.

Read the File Before You Decide

RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales.bak';

That reads the backup without touching anything, and the columns that matter are DatabaseName, ServerName, BackupStartDate, RecoveryModel and BackupType. Two questions get answered at once: is this the file you thought it was, and is it from the server you thought.

Then compare against what is on the instance:

SELECT  d.name,
        d.recovery_model_desc,
        d.create_date,
        (SELECT MAX(b.backup_finish_date)
         FROM   msdb.dbo.backupset AS b
         WHERE  b.database_name = d.name) AS last_backup
FROM    sys.databases AS d
WHERE   d.name = N'Sales';

If create_date on the instance is newer than the backup, someone has recreated the database since that file was taken, which is exactly the situation the check exists to catch.

The Three Ways Forward

The middle option is the one to prefer on a production instance, because nothing is destroyed until you have proved the restore is good.

RESTORE DATABASE SalesRestore
FROM   DISK = N'D:\Backups\Sales.bak'
WITH   MOVE 'Sales'     TO N'E:\Data\SalesRestore.mdf',
       MOVE 'Sales_log' TO N'F:\Log\SalesRestore_ldf.ldf',
       RECOVERY, STATS = 5;

Get the logical names from the file rather than guessing them:

RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales.bak';

What WITH REPLACE Actually Waives

This is the part worth knowing before you type it, because WITH REPLACE is usually described as “force it” and that undersells the risk.

RESTORE DATABASE Sales
FROM   DISK = N'D:\Backups\Sales.bak'
WITH   REPLACE, RECOVERY;

It waives two checks, not one:

• the database-identity check that produced 3154
• the check that the tail of the existing log has been backed up

The second one is the dangerous half. Any committed transaction written to the existing database since its last log backup is destroyed with no further warning. On a FULL recovery database that is real, committed, customer-visible work.

So the honest sequence, when overwriting is genuinely what you want:

BACKUP LOG Sales TO DISK = N'D:\Backups\Sales_tail.trn' WITH NORECOVERY;

Then restore with REPLACE. If the tail backup fails because the data files are already gone, that is fine, and it is also the moment to be certain about what you are overwriting.

Related Scripts

• Get Backup Chain Integrity, find the gaps before you need them
• Get Last Restore History, what was restored onto this instance, and when
• Database Cannot Be Opened, Mid-Restore, the state a half-finished restore leaves behind

Common Questions

Related Scripts

• Get Backup Chain Integrity, find the gaps before you need them
• Get Last Restore History, what was restored onto this instance, and when
• Database Cannot Be Opened, Mid-Restore, the state a half-finished restore leaves behind