Proving the Restore, Part 8: Proving the Log Chain Has No Gaps

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

Restoring a full backup and then a hundred log backups works only if the logs form an unbroken sequence. One missing backup in the middle and the restore stops there, with the database still in RESTORING state and no way forward.

Finding that out during a recovery is expensive. The sequence can be checked in advance from the headers, and it comes down to comparing two numbers per backup.

A woman engineer examines one junction of a long horizontal chain through a jeweller loupe, and that link glows green to show it has been confirmed sound.

FirstLSN and LastLSN

Every backup set records two log sequence numbers. FirstLSN is the log record the backup starts at. LastLSN is documented as the log sequence number of the next log record after the backup set, rather than the last one inside it.^[1]

That definition is what makes the check simple. Because LastLSN points at the next record rather than the final one, consecutive log backups meet exactly: the LastLSN of one equals the FirstLSN of the next. There’s no off-by-one adjustment to remember.

If those two values don’t match, log records exist between the two backups that neither one contains. That’s a gap, and the restore sequence stops at it.

Checking the whole sequence at once

LAG gives you the previous row’s value, so one pass over the ordered set finds every break:^[2]

WITH [log_backups] AS
(
    SELECT
          [h].[Position]
        , [h].[FirstLSN]
        , [h].[LastLSN]
        , [h].[BackupFinishDate]
        , [h].[BeginsLogChain]
        , [prev_last_lsn] = LAG([h].[LastLSN]) OVER (ORDER BY [h].[FirstLSN])
    FROM
        #header_results AS [h]
    WHERE
        [h].[BackupType] = 2                /* transaction log */
)
SELECT
      [l].[Position]
    , [l].[BackupFinishDate]
    , [l].[prev_last_lsn]
    , [l].[FirstLSN]
    , [gap_size] = [l].[FirstLSN] - [l].[prev_last_lsn]
FROM
    [log_backups] AS [l]
WHERE
    [l].[prev_last_lsn] IS NOT NULL
    AND [l].[FirstLSN] <> [l].[prev_last_lsn]
ORDER BY
    [l].[FirstLSN];

WITH [log_backups] AS

(

SELECT

[h].[Position]

, [h].[FirstLSN]

, [h].[LastLSN]

, [h].[BackupFinishDate]

, [h].[BeginsLogChain]

, [prev_last_lsn] = LAG([h].[LastLSN]) OVER (ORDER BY [h].[FirstLSN])

FROM

#header_results AS [h]

WHERE

[h].[BackupType] = 2 /* transaction log */

)

SELECT

[l].[Position]

, [l].[BackupFinishDate]

, [l].[prev_last_lsn]

, [l].[FirstLSN]

, [gap_size] = [l].[FirstLSN] - [l].[prev_last_lsn]

FROM

[log_backups] AS [l]

WHERE

[l].[prev_last_lsn] IS NOT NULL

AND [l].[FirstLSN] <> [l].[prev_last_lsn]

ORDER BY

[l].[FirstLSN];

No rows means no gaps across the set you gave it. Any row identifies the backup whose start doesn’t meet the previous backup’s end.

Order by FirstLSN rather than by date. The two normally agree, but LSN order is what the restore sequence depends on, and sorting by the value you’re validating avoids introducing a second assumption.

LAG needs SQL Server 2012 or later. On anything older, a self join on the ordered set gets the same answer with more typing.

What a gap actually means

A gap is not always damage. Working out which kind you have determines whether it’s a problem.

A file you didn’t include. The most common explanation is that the header set is incomplete rather than the chain. If backups live across several files, or across discrete files rather than one appended file, a gap appears wherever your enumeration missed one. Check what’s on disk before concluding anything.

A log backup taken by something else. A one-off BACKUP LOG from a person or another tool consumes log records and creates a backup you don’t have. Copy-only log backups exist to avoid this, since a copy-only log backup doesn’t affect the sequence of regular ones.^[3]

A broken log chain. Switching a database to SIMPLE recovery and back breaks the chain outright. So does any operation that truncates the log without backing it up. After that, log backups can’t span the break, and a new chain starts at the next full backup.

BeginsLogChain distinguishes the third case. It’s set to 1 on the first backup of a continuous chain, which happens after database creation or after a switch from SIMPLE to FULL or BULK_LOGGED.^[1] A gap immediately before a backup with BeginsLogChain = 1 means the chain restarted rather than that a file went missing.

Bridging from the data backup

The log sequence also has to connect to whatever data backup you’re restoring first.

For a full backup, the anchor is its LastLSN. Log backups whose LastLSN is at or below that value contain nothing you need, since the full already includes those changes. The first log backup you need is the one whose range covers the full’s LastLSN.

For a differential, the anchor is the differential’s LastLSN instead, for the same reason.

DECLARE @anchor_lsn numeric(25, 0);

SELECT @anchor_lsn = MAX([h].[LastLSN])
FROM   #header_results AS [h]
WHERE  [h].[BackupType] IN (1, 5);          /* full or differential */

SELECT
      [h].[Position]
    , [h].[FirstLSN]
    , [h].[LastLSN]
FROM
    #header_results AS [h]
WHERE
    [h].[BackupType] = 2
    AND [h].[LastLSN] > @anchor_lsn
ORDER BY
    [h].[FirstLSN];

DECLARE @anchor_lsn numeric(25, 0);

SELECT @anchor_lsn = MAX([h].[LastLSN])

FROM #header_results AS [h]

WHERE [h].[BackupType] IN (1, 5); /* full or differential */

SELECT

[h].[Position]

, [h].[FirstLSN]

, [h].[LastLSN]

FROM

#header_results AS [h]

WHERE

[h].[BackupType] = 2

AND [h].[LastLSN] > @anchor_lsn

ORDER BY

[h].[FirstLSN];

Using MAX over both types works because a differential’s LastLSN is later than that of the full it’s based on. If you’re deliberately skipping the differential, restrict the anchor to BackupType = 1.

Restoring a log backup that predates the anchor isn’t dangerous. SQL Server rejects it rather than doing something odd. Filtering them out just keeps the generated script to the statements that do something.

Damage the sequence check won’t find

Continuity says the backups form an unbroken range. It says nothing about whether each backup can be read.

IsDamaged is set when a backup was taken from a database that had detected corruption, and the backup proceeded anyway under CONTINUE_AFTER_ERROR.^[1] Those backups sit in the chain and satisfy every LSN check.

HasBackupChecksums tells you whether the backup carries checksums at all. Without them, RESTORE VERIFYONLY has much less to verify against.

SELECT
      [h].[Position]
    , [h].[BackupFinishDate]
    , [h].[IsDamaged]
    , [h].[HasBackupChecksums]
FROM
    #header_results AS [h]
WHERE
    [h].[IsDamaged] = 1
    OR [h].[HasBackupChecksums] = 0;

SELECT

[h].[Position]

, [h].[BackupFinishDate]

, [h].[IsDamaged]

, [h].[HasBackupChecksums]

FROM

#header_results AS [h]

WHERE

[h].[IsDamaged] = 1

OR [h].[HasBackupChecksums] = 0;

Treat IsDamaged = 1 as a break in the chain even though the LSNs line up. Missing checksums are worth reporting as a warning rather than a failure, since plenty of estates have older backups taken before CHECKSUM was made a default.

A continuous chain of readable backups can still belong to a different database than the one you think you’re restoring. The header records enough lineage to prove it does, which is part 9.

Have you had a stray one-off log backup break a chain? I’d like to hear how it was traced. Bluesky or LinkedIn.

References

RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. Defines LastLSN as the log sequence number of the next log record after the backup set, and documents BeginsLogChain, IsDamaged, and HasBackupChecksums. ↩
LAG (Transact-SQL) – Microsoft Learn. Available from SQL Server 2012, used here to compare each log backup against the one before it in a single pass. ↩
Copy-Only Backups – Microsoft Learn. A copy-only log backup preserves the sequence of regular log backups, which is why ad-hoc backups should use it. ↩

Tags: backup, Disaster Recovery, LSN, restore, transaction log

Proving the Restore, Part 8: Proving the Log Chain Has No Gaps

FirstLSN and LastLSN

Checking the whole sequence at once

What a gap actually means

Bridging from the data backup

Damage the sequence check won’t find

Next

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Proving the Restore, Part 8: Proving the Log Chain Has No Gaps

FirstLSN and LastLSN

Checking the whole sequence at once

What a gap actually means

Bridging from the data backup

Damage the sequence check won’t find

Next

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta