Proving the Restore, Part 6: The Differential Base Has to Match

2026-08-28 · by Hannah Vernon · in recovery

A differential backup contains every extent changed since its base full backup. Not since the last differential, and not since whichever full is newest on disk, but since one specific full backup.

Restore it onto a different full and SQL Server refuses. It’s better to find that out now than during a recovery, when the full you kept and the differential you kept turn out never to have been a pair.

Both headers carry enough information to prove the pairing in advance.

A woman engineer in a workshop holds up two large machined metal tokens whose engraved green patterns match, while a third token with a different pattern lies discarded on the bench.

The columns that establish the link

Four columns from RESTORE HEADERONLY establish the relationship, and three of them have similar names.^[1]

Column	Meaning
`CheckpointLSN`	The most recent checkpoint at the time this backup was created
`DatabaseBackupLSN`	The most recent full database backup, expressed as the begin-of-checkpoint LSN triggered when that full started
`DifferentialBaseLSN`	For a single-based differential, the `FirstLSN` of the differential base. NULL for non-differential backups
`DifferentialBaseGUID`	For a single-based differential, the unique identifier of the differential base

The usual pairing check is between the differential’s DatabaseBackupLSN and the full’s CheckpointLSN. Those match because both describe the same event: the checkpoint SQL Server triggers when a full backup begins. The full records it as its own CheckpointLSN; a later differential records it as the full it depends on.

DifferentialBaseGUID is the more direct test, because it’s an identifier rather than a position. LSNs can coincide across databases; GUIDs are not expected to.

Checking a pair

Assuming you’ve captured headers into a table as in part 2, with a column recording which file each row came from:

WITH [full_backup] AS
(
    SELECT
          [h].[source_file]
        , [h].[CheckpointLSN]
        , [h].[BackupSetGUID]
        , [h].[FamilyGUID]
        , [h].[BackupFinishDate]
    FROM
        #header_results AS [h]
    WHERE
        [h].[BackupType] = 1                /* full database backup */
)
, [diff_backup] AS
(
    SELECT
          [h].[source_file]
        , [h].[DatabaseBackupLSN]
        , [h].[DifferentialBaseLSN]
        , [h].[DifferentialBaseGUID]
        , [h].[FamilyGUID]
        , [h].[BackupFinishDate]
    FROM
        #header_results AS [h]
    WHERE
        [h].[BackupType] = 5                /* differential database backup */
)
SELECT
      [full_file]     = [f].[source_file]
    , [diff_file]     = [d].[source_file]
    , [lsn_match]     = CASE WHEN [d].[DatabaseBackupLSN]    = [f].[CheckpointLSN]  THEN 1 ELSE 0 END
    , [guid_match]    = CASE WHEN [d].[DifferentialBaseGUID] = [f].[BackupSetGUID]  THEN 1 ELSE 0 END
    , [lineage_match] = CASE WHEN [d].[FamilyGUID]           = [f].[FamilyGUID]     THEN 1 ELSE 0 END
FROM
    [full_backup] AS [f]
    CROSS JOIN [diff_backup] AS [d]
ORDER BY
      [f].[BackupFinishDate]
    , [d].[BackupFinishDate];

WITH [full_backup] AS

(

SELECT

[h].[source_file]

, [h].[CheckpointLSN]

, [h].[BackupSetGUID]

, [h].[FamilyGUID]

, [h].[BackupFinishDate]

FROM

#header_results AS [h]

WHERE

[h].[BackupType] = 1 /* full database backup */

)

, [diff_backup] AS

(

SELECT

[h].[source_file]

, [h].[DatabaseBackupLSN]

, [h].[DifferentialBaseLSN]

, [h].[DifferentialBaseGUID]

, [h].[FamilyGUID]

, [h].[BackupFinishDate]

FROM

#header_results AS [h]

WHERE

[h].[BackupType] = 5 /* differential database backup */

)

SELECT

[full_file] = [f].[source_file]

, [diff_file] = [d].[source_file]

, [lsn_match] = CASE WHEN [d].[DatabaseBackupLSN] = [f].[CheckpointLSN] THEN 1 ELSE 0 END

, [guid_match] = CASE WHEN [d].[DifferentialBaseGUID] = [f].[BackupSetGUID] THEN 1 ELSE 0 END

, [lineage_match] = CASE WHEN [d].[FamilyGUID] = [f].[FamilyGUID] THEN 1 ELSE 0 END

FROM

[full_backup] AS [f]

CROSS JOIN [diff_backup] AS [d]

ORDER BY

[f].[BackupFinishDate]

, [d].[BackupFinishDate];

BackupType is a smallint, not a character. 1 is a full database backup, 5 is a differential database backup, 2 is a transaction log. msdb.dbo.backupset uses 'D', 'I', and 'L' for the same concepts, so code reading from both sources carries two vocabularies for one idea. Writing WHERE [BackupType] = 'D' against header output raises a conversion error, since 'D' has no smallint representation.

A differential that pairs with a full has all three flags set to 1. Cross joining every full against every differential is deliberate: it shows you which pairs are valid rather than assuming the newest of each belong together.

Picking the right differential

Having several differentials is normal. A weekly full with nightly differentials gives you six, all sharing one base. For a point-in-time restore you want the newest differential that finished at or before your target time.

DECLARE @stopat datetime2(3) = '2026-08-17T14:30:00';

SELECT TOP (1)
      [h].[source_file]
    , [h].[BackupFinishDate]
    , [h].[DatabaseBackupLSN]
FROM
    #header_results AS [h]
WHERE
    [h].[BackupType] = 5
    AND [h].[BackupFinishDate] <= @stopat
    AND [h].[DatabaseBackupLSN] = @full_checkpoint_lsn
ORDER BY
    [h].[BackupFinishDate] DESC;

DECLARE @stopat datetime2(3) = '2026-08-17T14:30:00';

SELECT TOP (1)

[h].[source_file]

, [h].[BackupFinishDate]

, [h].[DatabaseBackupLSN]

FROM

#header_results AS [h]

WHERE

[h].[BackupType] = 5

AND [h].[BackupFinishDate] <= @stopat

AND [h].[DatabaseBackupLSN] = @full_checkpoint_lsn

ORDER BY

[h].[BackupFinishDate] DESC;

The BackupFinishDate <= @stopat predicate is easy to omit. A differential that started before your target and finished after it contains changes from after the point you're recovering to. Restoring it puts the database past your target, and log restores afterwards only move forward.

Filtering on DatabaseBackupLSN in the same query keeps you from selecting a differential belonging to a different full.

Skipping the differential

A differential is an optimisation rather than a requirement. Full plus logs reaches the same point as full plus differential plus logs, given a log chain that covers the period.

So the differential is the right thing to drop when validation fails. If none pairs cleanly with your full, restoring the full and replaying logs across the whole period reaches the same point, more slowly. A validation routine that finds no usable differential should report a warning rather than an error, provided the log chain covers the gap.

What breaks the pairing

A copy-only full. Copy-only backups deliberately don't become a differential base, so that an ad-hoc backup doesn't disturb the scheduled chain.^[2] Take one, and differentials after it still reference the previous scheduled full. The newest full on disk isn't necessarily the base.

A restore between backups. Restoring the database resets the differential base. Differentials taken before the restore reference a base the current incarnation no longer has, which is what FamilyGUID catches, and part 9 covers.

Multi-based differentials. For file or filegroup differentials taken against multiple bases, DifferentialBaseLSN and DifferentialBaseGUID are NULL, and the base has to be determined per file through RESTORE FILELISTONLY.^[3] NULL here means the base is recorded per file, not that there's no base. Treating it as a failed match reports a break that isn't there.

Full and differential get you to a point. Transaction logs get you from there to the moment you care about, and they have to form an unbroken sequence to do it. Proving that sequence has no gaps is part 7.

Have you had a differential turn out to be based on an older full than the one sitting next to it on disk? Bluesky or LinkedIn.

References

RESTORE HEADERONLY (Transact-SQL) - Microsoft Learn. Defines CheckpointLSN, DatabaseBackupLSN, DifferentialBaseLSN, and DifferentialBaseGUID, and documents BackupType as a smallint with 1 for database, 2 for log, and 5 for differential database. ↩
Copy-Only Backups - Microsoft Learn. Establishes that a copy-only full backup does not become the base for subsequent differentials. ↩
RESTORE FILELISTONLY (Transact-SQL) - Microsoft Learn. Where the per-file differential base is found when a differential is multi-based and the header columns are NULL. ↩

Tags: backup, differential backup, Disaster Recovery, LSN, restore

Proving the Restore, Part 6: The Differential Base Has to Match

The columns that establish the link

Checking a pair

Picking the right differential

Skipping the differential

What breaks the pairing

Next

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Proving the Restore, Part 6: The Differential Base Has to Match

The columns that establish the link

Checking a pair

Picking the right differential

Skipping the differential

What breaks the pairing

Next

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta