Errors 4305 and 4326: Log Backups Out of Sequence in a Restore Chain

Fix it now

A log backup does not fit where you are trying to apply it. 4305 means the file begins at an LSN too recent to apply, so something between the two is missing. 4326 means it ends at an LSN too early, so it has already been applied. Neither indicates damage: what you have is an ordering problem in the sequence you are feeding the engine.

SELECT bs.type, bs.backup_start_date, bs.backup_finish_date,
       bs.first_lsn, bs.last_lsn, bs.database_backup_lsn,
       bmf.physical_device_name
FROM   msdb.dbo.backupset bs
JOIN   msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE  bs.database_name = N'MyDb'
ORDER BY bs.backup_finish_date;

1. Confirm the database is still mid-restore: SELECT name, state_desc FROM sys.databases WHERE name = N'MyDb'; should read RESTORING.
2. Read the type column. D is a full backup, I a differential, L a log, F a file or filegroup backup, G a differential file backup, P a partial and Q a differential partial.
3. Restore the full backup WITH NORECOVERY, then the most recent differential based on it, also WITH NORECOVERY.
4. Apply every log backup taken after that differential, in order, each WITH NORECOVERY.
5. Use WITH RECOVERY only on the last statement, or issue RESTORE DATABASE [MyDb] WITH RECOVERY; on its own at the end.

Both messages name the LSN they want. 4305 tells you an earlier log backup including a specific LSN can be restored; 4326 tells you a more recent one can. Read the number rather than guessing which file is next.

If the sequence completes and the database comes online, you are done. If a file is genuinely missing, the next section explains how far you can still get.

Why it happens

Every change written to the transaction log gets a log sequence number, and those numbers only ever increase. A log backup copies a contiguous range of them and records its first and last LSN in its header, which is what you see in the first_lsn and last_lsn columns of msdb.dbo.backupset. When you restore, the engine knows exactly which LSN the database has reached and will only accept a log backup whose range begins there.

That single rule produces both errors, and both messages are more helpful than they look. 4305 says the log in this backup set begins at an LSN which is too recent to apply, and then names an earlier LSN that can be restored. 4326 says the log terminates at an LSN too early to apply, and names a more recent one. Neither is a complaint about the file: they are directions to the right one.

Differentials work differently and this is where sequences get tangled. Each differential is tied to a specific full backup through its database_backup_lsn, which records the LSN of the most recent full backup it is based on. Restoring a differential onto a different base fails for the same family of reasons, and the column that tells you which base it needs is in the same query.

The chain is broken by things done at the source, not at the restore. Switching to simple recovery ends it. So does a second job, a third-party backup product or an ad-hoc log backup taken to somewhere you do not know about, because each of those truncates the log and carries the next range away with it. A copy-only log backup is the exception: it reads the log without truncating it and leaves the sequence intact, which is why any ad-hoc log backup should carry COPY_ONLY.

Full reference

Which message you have and what it wants

Reading msdb.dbo.backupset without guessing

Reading the type column is what stops you trying to apply a differential as though it were a log backup, which is the most common way a chain gets misread. Reading database_backup_lsn is what stops you applying a differential to the wrong full backup.

Finding the last restorable point

1. Build the ordered list from the source instance with the query in the fix section.
2. Walk forward from your full backup, checking that each file’s first_lsn continues from the previous file’s last_lsn.
3. The first place that does not continue is the gap.
4. The backup_finish_date of the last file before the gap is as far as you can reach in time.
5. Restore up to that file, then issue RESTORE DATABASE WITH RECOVERY, and take a fresh full backup immediately.

Why the chain gets broken, and how to keep it

• Two schedulers taking log backups will each hold half the chain and neither will be complete. Standardise on one.
• Any ad-hoc log backup should carry COPY_ONLY, which reads the log without truncating it.
• A switch to simple recovery ends the chain immediately, and switching back does not resume it. Take a full backup afterwards.
• Differentials do not truncate the log and do not affect the chain. Only log backups and a recovery model change do.
• A third-party backup product taking log backups counts as a second scheduler. Find out before you need the chain, not during a restore.

Point in time, and the STOPAT trap

4335 is the one that catches people out during a point-in-time restore. It means the STOPAT value you gave is behind where the restore has already rolled forward, so there is nothing left to stop at. The fix is not to change the STOPAT value repeatedly; it is to start the sequence again from the full backup and apply the log backups with the STOPAT clause on each of them, so the roll-forward stops where you meant it to the first time.

Every code this article covers

Confirm the fix worked

1. Confirm the database finished the sequence: SELECT name, state_desc FROM sys.databases WHERE name = N'MyDb'; should read ONLINE.
2. Check what was applied and in which order: SELECT restore_date, restore_type, backup_set_id FROM msdb.dbo.restorehistory WHERE destination_database_name = N'MyDb' ORDER BY restore_date;
3. Query a table with recent activity and confirm the data reaches the point in time you expected.
4. Take a fresh full backup immediately, so the recovered database has its own clean chain.
5. Confirm only one schedule is taking log backups from now on.

Questions people ask about this