Proving the Restore, Part 13: Two Vocabularies for the Same Backup
This series has used two sources for the same information. msdb records what the instance did. RESTORE HEADERONLY reads what’s in the files.
They describe the same backups and disagree on almost every name. Code that reads from both, which is most code that does anything useful here, ends up translating between them, and the translation is where mistakes get made.
Backup type: letters against numbers
The difference that causes the most trouble is the backup type.
msdb.dbo.backupset uses a char(1) code.[1] RESTORE HEADERONLY returns a smallint.[2] The values don’t correspond in any pattern you could guess.
| Backup kind | msdb type |
Header BackupType |
|---|---|---|
| Full database | D |
1 |
| Transaction log | L |
2 |
| File or filegroup | F |
4 |
| Differential database | I |
5 |
| Differential file | G |
6 |
| Partial | P |
7 |
| Differential partial | Q |
8 |
Two details make this worth a table rather than memory. The differential database backup is I in msdb, for “incremental”, while the header calls it 5. And the letter F means file backup in msdb, while in RESTORE FILELISTONLY the letter F in the Type column means Full Text Catalog.[3] Same letter, different meaning, different result set.
BackupTypeDescription in the header gives the type as text, which sidesteps the numeric codes when readability matters more than compactness:[2]
|
1 2 3 4 5 6 7 8 9 |
SELECT [h].[Position] , [h].[BackupType] , [h].[BackupTypeDescription] , [h].[BackupFinishDate] FROM #header_results AS [h] ORDER BY [h].[Position]; |
That returns values like DATABASE, TRANSACTION LOG, and DATABASE DIFFERENTIAL.
Column names for the same values
The LSN columns hold identical values under different names and different conventions.
| Concept | msdb.dbo.backupset |
RESTORE HEADERONLY |
|---|---|---|
| First LSN in the backup | first_lsn |
FirstLSN |
| LSN after the backup | last_lsn |
LastLSN |
| Most recent checkpoint | checkpoint_lsn |
CheckpointLSN |
| Most recent full backup | database_backup_lsn |
DatabaseBackupLSN |
| Differential base | differential_base_lsn |
DifferentialBaseLSN |
| Original database identity | family_guid |
FamilyGUID |
| Starting recovery fork | first_recovery_fork_guid |
FirstRecoveryForkID |
| Ending recovery fork | last_recovery_fork_guid |
RecoveryForkID |
| Position on the device | position |
Position |
| Backup finished | backup_finish_date |
BackupFinishDate |
msdb uses lower case with underscores throughout. The header uses PascalCase. Two of the fork columns differ by more than casing: first_recovery_fork_guid against FirstRecoveryForkID, and last_recovery_fork_guid against RecoveryForkID, where the header drops “last” entirely.
Which source to trust
They answer different questions, and the right one depends on what you’re asking.
msdb is authoritative about what this instance did. It records the backup operation, the path written to, the position, and the timing. It’s fast to query and needs no file access.
The header is authoritative about what the file contains. It survives instance rebuilds, migrations, and msdb restores. It’s the only source that works for a file someone handed you.
The cases where they disagree are worth enumerating, because each disagreement means something specific:
| Disagreement | Likely explanation |
|---|---|
msdb has a row, the file doesn’t exist |
the file was deleted, moved, or archived |
The file exists, msdb has no row |
taken by a different instance, or msdb was restored or rebuilt |
msdb lists positions the file lacks |
the file was recreated with INIT |
Different FamilyGUID under the same name |
a different database, covered in part 9 |
A validation routine that reads both and reports the differences finds problems that either source alone would miss.
Translating in one place
The practical advice is to normalise at the boundary rather than carrying both vocabularies through the logic. A small mapping applied when data enters your working table keeps the comparisons readable:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT [source] = N'msdb' , [backup_kind] = CASE [b].[type] WHEN 'D' THEN N'FULL' WHEN 'I' THEN N'DIFF' WHEN 'L' THEN N'LOG' ELSE N'OTHER' END , [first_lsn] = [b].[first_lsn] , [last_lsn] = [b].[last_lsn] , [finished] = [b].[backup_finish_date] FROM [msdb].[dbo].[backupset] AS [b] WHERE [b].[database_name] = N'Sales' UNION ALL SELECT [source] = N'header' , [backup_kind] = CASE [h].[BackupType] WHEN 1 THEN N'FULL' WHEN 5 THEN N'DIFF' WHEN 2 THEN N'LOG' ELSE N'OTHER' END , [first_lsn] = [h].[FirstLSN] , [last_lsn] = [h].[LastLSN] , [finished] = [h].[BackupFinishDate] FROM #header_results AS [h]; |
With both sides speaking one vocabulary, a full outer join on first_lsn shows what each source has that the other doesn’t. That comparison is the check msdb alone can’t do and the files alone can’t do either.
Next
One post left. The generated restore script from part 11 can run to hundreds of lines, and getting it out of a stored procedure so someone can read it runs into a limit that truncates without complaint. That’s part 14.
Do you normalise these at the boundary, or carry both vocabularies through? Bluesky or LinkedIn.
References
- backupset – Microsoft Learn. Documents the
typecolumn codes D, I, L, F, G, P, and Q, and the lower-case underscore naming used throughoutmsdb. ↩ - RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. Documents
BackupTypeas a smallint with values 1, 2, 4, 5, 6, 7, and 8, andBackupTypeDescriptionas the text equivalent. ↩ - RESTORE FILELISTONLY (Transact-SQL) – Microsoft Learn. Where the letter F means Full Text Catalog in the
Typecolumn, unrelated to the F used for file backups inmsdb. ↩