Sql-server – Restoring a differential database file “the backup set holds a backup of a database other than the existing”

sqlsql serversql-server-2012

I am trying to test restoring a SQL database using a full/differential approach to a different server than it was backed up from. The database is in simple recovery mode as it is a test data warehouse.

I've got a full backup which is roughly 5 days old and also a differential from today.

Now when I try to apply the differential, I get the following message:

RESTORE DATABASE PAS_DWH_ME
   FROM DISK='C:\MTWSQL01_Differential_Database.bak'
   WITH REPLACE

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'PAS_DWH_ME' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I originall tried through SSMS, however it said something about breaking the LSN chain. Google suggests this is a bug with our version of SSMS.

Google suggests the way to fix this is to use the WITH REPLACE option, however I am using that clause.

More info:

restore headeronly from disk = 'C:\MTWSQL01_Full_Database.bak'
restore headeronly from disk = 'C:\MTWSQL01_Differential_Database.bak'

CheckpointLSN of the Full = 1063466000005158400170

DatabaseBackupLSN of the Differential = 1063466000005158400170

Best Answer

The OP and I figured it out in an interactive chat session. It turns out that the backup file in question had multiple backup sets. The one that he needed to restore was not in position 1, and so didn't match the full backup that he'd restored originally. The solution was to add a with file = n (where n was the position of that backup set in the file).

Related Topic