Backup & Recovery from First Principles, Part 2: Anatomy of a Backup
A backup file is not a copy of your MDF. It is a container with a precise contract: enough pages and enough log to reconstruct a transactionally consistent database as of the moment the backup finished. Understanding what goes into that container, and how SQL Server chains containers together, is the difference between a restore strategy and a folder full of hopeful .bak files.
This is part 2 of the backup and recovery series. Part 1 covered recovery models; parts 3 and 4 build on this one to explain the log chain and copy-only backups. Demos ran on SQL Server 2019 (CU32) and were verified identical on SQL Server 2025 (RTM) – though 2025’s RESTORE HEADERONLY output adds new columns, including CompressionAlgorithm and LastValidRestoreTime.
The Three Backup Types
- Full (type D): every allocated page in the database, plus enough transaction log to make the restored copy consistent. A full backup taken on a busy system is not a snapshot of the start time; data pages are read while transactions keep committing, and the included log range reconciles it all at restore time.[1]
- Differential (type I): every extent modified since the last non-copy-only full backup, tracked by the differential bitmap pages. Differentials are cumulative, not incremental: each one contains everything since the base full, so you only ever restore the newest one.[2]
- Log (type L): the log records generated since the previous log backup. Log backups are the only incremental backup SQL Server has, and the only path to point-in-time restore.
Building a Chain and Reading It Back
Take a full, a differential, and two log backups with some work in between:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\anat_full.bak' WITH INIT, COMPRESSION; UPDATE TOP (50000) [dbo].[stage] SET [payload] = REPLICATE('a', 400); BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\anat_diff1.bak' WITH DIFFERENTIAL, INIT, COMPRESSION; UPDATE TOP (50000) [dbo].[stage] SET [payload] = REPLICATE('b', 400); BACKUP LOG [bkrec_demo] TO DISK = N'C:\temp\bkrec\anat_log1.trn' WITH INIT, COMPRESSION; UPDATE TOP (50000) [dbo].[stage] SET [payload] = REPLICATE('c', 400); BACKUP LOG [bkrec_demo] TO DISK = N'C:\temp\bkrec\anat_log2.trn' WITH INIT, COMPRESSION; |
Every backup lands a row in msdb.dbo.backupset,[3] and the LSN columns are where the chain lives:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT [bs].[backup_set_id] , [bs].[type] , [bs].[first_lsn] , [bs].[last_lsn] , [bs].[database_backup_lsn] , [bs].[differential_base_lsn] FROM [msdb].[dbo].[backupset] AS [bs] WHERE [bs].[database_name] = N'bkrec_demo' ORDER BY [bs].[backup_set_id]; |
|
1 2 3 4 5 6 |
type | first_lsn | last_lsn | database_backup_lsn | differential_base_lsn -----|--------------------|--------------------|---------------------|---------------------- D | 722000014728800001 | 722000014731200001 | 714000017579200001 | NULL I | 722000023585600001 | 722000023588000001 | 722000014728800001 | 722000014728800001 L | 719000002063200001 | 723000006320000001 | 722000014728800001 | NULL L | 723000006320000001 | 723000015162400001 | 722000014728800001 | NULL |
Three things worth staring at:
- The differential’s
differential_base_lsnequals the full backup’sfirst_lsn. That is the pointer that says “I am based on that full.” Restore the differential onto any other full and SQL Server refuses. - Each log backup’s
first_lsnequals the previous log backup’slast_lsn. The chain is literally a linked list of LSN ranges. A gap between one backup’slast_lsnand the next one’sfirst_lsnis a broken chain, which is part 3’s whole subject. - The first log backup’s range starts before the full backup was taken. Log backups are independent of full backups; the full backup did not truncate the log or restart the log chain. This surprises almost everyone the first time.
LSNs render as intimidating 25-digit numbers, but they are just three fixed-width fields concatenated: VLF sequence number, log block offset, and record number. I published a converter between the hex and decimal LSN formats if you need to line msdb values up against fn_dblog or error messages.
Asking the File Itself
msdb history only exists on the server that took the backup. Hand someone a bare file and the file still tells its own story:
|
1 |
RESTORE HEADERONLY FROM DISK = N'C:\temp\bkrec\anat_log1.trn'; |
RESTORE HEADERONLY[4] returns the same LSN columns plus flags that matter during a recovery scramble: BackupType, RecoveryModel, HasBulkLoggedData (the point-in-time asterisk from part 1), IsCopyOnly, BeginsLogChain, and HasBackupChecksums (which part 8 shows is more important than most people realize). Its siblings RESTORE FILELISTONLY (what files are inside, for building MOVE clauses) and RESTORE LABELONLY (media set info) complete the toolkit. If you have a folder of backup files and no msdb, you can reconstruct the restore sequence from the files alone – tedious by hand, scriptable in practice.
What This Buys You
The mental model to carry forward: a restore is a walk along an LSN timeline. The full backup drops you onto the timeline at its checkpoint_lsn. A differential jumps you forward to its last_lsn. Log backups walk you forward continuously, and only log backups let you stop between backup boundaries. Every restore error you have ever seen about LSNs (“too recent to apply”, “no files ready to rollforward”) is SQL Server telling you where on the timeline you are versus where the file you offered begins.
Part 3 breaks the chain on purpose, in all the ways production systems break it by accident, and shows how to detect and repair each one.
Ever inherited a backup folder with no msdb to explain it? Tell me about it in the comments, or find me on Bluesky or LinkedIn.
References
- Backup Overview (SQL Server) – Microsoft Learn. Backup types and what each contains. ↩
- Differential Backups (SQL Server) – Microsoft Learn. Differential base, bitmap, and cumulative behavior. ↩
- backupset (Transact-SQL) – Microsoft Learn. Column reference for msdb backup history, including the LSN columns. ↩
- RESTORE Statements (Transact-SQL) – Microsoft Learn. RESTORE HEADERONLY, FILELISTONLY, and LABELONLY output reference. ↩