TROUBLESHOOTING DIFFERENTIAL RESTORE FAILURES
When restoring a differential database backup, SQL Server may reject the restore with the following error:
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
A differential backup can only be applied on top of the specific full backup it was derived from. The very latest full backup must be located and restored prior to any differential that was derived from it – a differential cannot be restored over an earlier full backup.
Cause – a broken backup chain
This error occurs when you attempt to restore a differential backup on top of a full backup that isn't the most recent one. The primary culprit is typically an external backup process creating full backups that SQL Backup Master is unaware of – in other words, a full backup was taken in between the full and differential backups being performed by SQL Backup Master. This results in a broken backup chain: the differential is now based on the interloping full backup, not the one you're restoring.
Inspecting the backup chain in msdb
SQL Server records every backup taken against the instance – regardless of which product
took it – in the msdb database. Run the following query to see the complete
backup history, including the log sequence numbers (LSNs) that tie each differential to its
base full backup:
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.differential_base_lsn,
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Diff'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON
msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Reading the results
The query returns one row per backup, ordered chronologically per database. To correlate a differential backup with its base, take the differential_base_lsn value from the differential (Diff) row and match it against the LSN recorded for the Full backup rows that precede it – the differential's base LSN corresponds to the first LSN of the full backup it was derived from. When the chain is healthy, each differential's base LSN points at the most recent full backup taken by SQL Backup Master.
When the chain is broken, you'll instead see an extra Full row – one you didn't expect – sitting between the SQL Backup Master full backup and the failing differential, and the differential's differential_base_lsn will match that unexpected full backup rather than yours. The physical_device_name column reveals where that interloping backup was written, which usually identifies the third-party product or maintenance task responsible.
Solution
Ensure that only SQL Backup Master creates backups for the databases it manages. Locate the external process identified above and disable its SQL Server backup activity – competing automated backup services and scheduled tasks are the usual sources. Once no other product is taking full backups, run a new full backup in SQL Backup Master to establish a clean chain; subsequent differential backups will restore correctly against it.
Related resources
If you need help interpreting your backup history, please open a support request and we'll be happy to take a look.