Error 3154: The Backup Set Holds a Backup of a Different Database

Fix it now

RESTORE has noticed that the database already on the target is not the one in the backup set, and it refuses to overwrite until you say plainly that you meant to. That check compares the database family GUID recorded in the backup with the one on the server, so it is not fooled by a matching name. WITH REPLACE removes the objection and MOVE handles the file layout.

SELECT @@SERVERNAME, DB_NAME();
RESTORE HEADERONLY   FROM DISK = N'D:\bk\Source.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\bk\Source.bak';

1. Check you are on the instance you think you are. A production database and last week’s test copy have very similar names at three in the morning.
2. If the target holds anything you might want later, back it up first. REPLACE destroys it and there is no undo.
3. Take exclusive access: ALTER DATABASE [Target] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
4. Restore with REPLACE and one MOVE clause per logical file from the FILELISTONLY output.
5. Return it to service: ALTER DATABASE [Target] SET MULTI_USER;

REPLACE also skips the tail-log backup requirement. On a database in full or bulk-logged recovery that means losing every transaction since the last log backup, silently.

If the restore completes and the application reads its data, you are done. If it is still refused, the next section covers what each of the five codes is asking for.

Why it happens

A backup set records the identity of the database it came from, not merely its name. When you restore into an existing database, SQL Server compares the database family GUID in the backup set with the one recorded on the server, and if they differ the database is not restored. Microsoft calls this an important safeguard, and it is: it is the thing standing between a tired administrator and an overwritten production database.

REPLACE tells the restore to proceed anyway, and Microsoft is unusually direct about what that costs. It documents three separate protections that REPLACE removes. First, you may overwrite an existing database with whatever database is in the backup set, even where the names differ. Second, on a database in full or bulk-logged recovery you may restore without a tail-log backup, which loses the most recently written log. Third, you may overwrite existing files of the wrong type entirely, including files belonging to another database that happens to be offline. Its own guidance is that REPLACE should be used rarely and only after careful consideration.

MOVE handles the other half of the problem. The backup remembers the paths the files had on the original server, and those paths may not exist here. Each logical file needs a MOVE clause pointing somewhere valid on this machine, and the logical names come from RESTORE FILELISTONLY rather than from guesswork. 3234 is the error you get when a name in a MOVE clause is not one of the names in the backup, and its own message tells you to run FILELISTONLY.

ALTER DATABASE [Target] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [Target]
  FROM DISK = N'D:\bk\Source.bak'
  WITH REPLACE,
       MOVE N'Source'     TO N'E:\Data\Target.mdf',
       MOVE N'Source_log' TO N'F:\Log\Target_log.ldf',
       RECOVERY;

ALTER DATABASE [Target] SET MULTI_USER;

Full reference

Which error you have, and what it wants

What REPLACE switches off

Microsoft’s stated rule is that for a database in full or bulk-logged recovery you must in most cases back up the tail of the log before restoring over it, unless the RESTORE statement carries WITH REPLACE or WITH STOPAT. That is worth reading twice: REPLACE is not only a naming override, it is also the switch that lets you discard the tail of the log without being asked.

The safer habit

Restore to a new database name with MOVE clauses pointing at new file paths. Nothing about the original is touched, you keep a working copy while you check the restored one, and the whole class of REPLACE accidents becomes impossible. It costs disk space and nothing else. Where the target genuinely must be overwritten, back it up first even when you are confident, because the cost of that backup is minutes and the cost of being wrong is the database.

After a cross-server restore

1. Confirm the database is online and in the recovery model you expect: SELECT name, state_desc, recovery_model_desc FROM sys.databases WHERE name = N'Target';
2. Confirm the files landed where you intended: SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'Target');
3. Fix orphaned database users. A database user whose SID does not match any login on this instance cannot be used, and the application will report a permissions problem rather than a restore problem.
4. Re-create anything that lived outside the database: SQL Agent jobs, linked servers, server-level permissions, credentials.
5. Take a fresh full backup, so the restored database has a chain of its own.

Reading HEADERONLY properly

When a file holds several backup sets, Position is the column that matters, and WITH FILE = <position> is how you select one. DatabaseName and BackupFinishDate together tell you whether this is the file you meant; a mismatch there is the cheapest possible moment to find out. If you are restoring from a file you did not create, read HEADERONLY before anything else, every time.

Every code this article covers

Confirm the fix worked

1. Confirm the database is online: SELECT name, state_desc, recovery_model_desc FROM sys.databases WHERE name = N'Target';
2. Confirm the files landed where you intended: SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'Target');
3. Check the restore history: SELECT TOP 5 destination_database_name, restore_date FROM msdb.dbo.restorehistory ORDER BY restore_date DESC;
4. Have the application connect and read a known row, and fix orphaned database users if logins do not match.
5. Take a fresh full backup of the restored database so it has its own chain.

Questions people ask about this