Proving the Restore, Part 2: HEADERONLY Changes Shape Between Versions
In part 1 I used msdb to find a database whose newest full backup had aged past a week. msdb tells you a backup happened. It doesn’t tell you the file is still readable, or which database it really came from.
For that you have to open the file, and RESTORE HEADERONLY is how. It reads the header of every backup set on a device and returns one row each, without restoring anything.
It also returns a different number of columns depending on which version of SQL Server runs it. If you capture that output into a table, that difference can break your code when you upgrade across the boundary.
Reading a header
At its simplest:
|
1 2 |
RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_FULL_20260817.bak'; |
One row per backup set on that device. For a file holding a single full backup that’s one row. For an appended log backup file it can be hundreds, one per log backup written since the file was created.
The columns you’ll actually reach for are BackupType, FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN, FamilyGUID, Position, and BackupFinishDate. Later parts of this series use every one of them. Reading a single result set by eye is fine. Comparing sixty log backups against each other is not, so sooner or later you want the output in a table.
Capturing the output
RESTORE HEADERONLY isn’t a table source, so you can’t SELECT from it. Within T-SQL on the same instance, INSERT ... EXEC is how you capture it:
|
1 2 |
INSERT INTO #header_results EXEC (N'RESTORE HEADERONLY FROM DISK = N''D:\Backups\Sales_FULL_20260817.bak'''); |
INSERT ... EXEC requires the destination table to have the same number of columns as the result set, in the same order, with compatible types.[3] There’s no partial mapping and no column list you can supply to pick out the ones you want. You take all of them or none.
So you have to declare a temp table matching the result set column for column. That’s tedious but workable, until the column set changes.
56 columns, then 59
Microsoft documents the full result set, and three columns carry an “Applies to: SQL Server 2022 (16.x) and later versions” note:[1]
| Column | Data type | Added in |
|---|---|---|
LastValidRestoreTime |
datetime | SQL Server 2022 |
TimeZone |
nvarchar(32) | SQL Server 2022 |
CompressionAlgorithm |
nvarchar(32) | SQL Server 2022 |
Counting the documented result set gives 56 columns through SQL Server 2019 and 59 from SQL Server 2022 onward. The three land at the end, after EncryptorType, which is the last of the older columns.
Those three encryption columns are themselves a version gate, added in SQL Server 2014 CU1. containment arrived in SQL Server 2012. If you still support anything older than 2014 you need to gate those too.
TimeZone is nvarchar(32), holding the name of the time zone the backup was taken in, despite a name that suggests a numeric offset. Declaring it as smallint produces a conversion error rather than a column count error, which points you somewhere unhelpful.
Gating the temp table
The fix is to build the temp table conditionally on the version of the engine executing the statement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @major_version int = CAST(SERVERPROPERTY(N'ProductMajorVersion') AS int); CREATE TABLE #header_results ( [BackupName] nvarchar(128) NULL , [BackupDescription] nvarchar(255) NULL , [BackupType] smallint NULL , [ExpirationDate] datetime NULL , [Compressed] bit NULL , [Position] smallint NULL , [DeviceType] tinyint NULL /* ... the remaining common columns ... */ , [KeyAlgorithm] nvarchar(32) NULL , [EncryptorThumbprint] varbinary(20) NULL , [EncryptorType] nvarchar(32) NULL ); IF @major_version >= 16 BEGIN ALTER TABLE #header_results ADD [LastValidRestoreTime] datetime NULL; ALTER TABLE #header_results ADD [TimeZone] nvarchar(32) NULL; ALTER TABLE #header_results ADD [CompressionAlgorithm] nvarchar(32) NULL; END; |
ProductMajorVersion returns 13 for 2016, 14 for 2017, 15 for 2019, 16 for 2022, and 17 for 2025.[2] Testing >= 16 rather than naming versions individually keeps the code working on later releases, assuming the columns aren’t changed again.
Building the base table then extending it with ALTER TABLE keeps the column order correct without duplicating a 56-line definition in two branches. The added columns land at the end, which is where the result set puts them.
Gate on the engine, not the backup
The column set is a property of the engine running RESTORE HEADERONLY. Microsoft’s “Applies to” notes attach the three columns to SQL Server 2022 and later, meaning the version of the instance executing the statement, not the version recorded inside the backup file.
So the gate has to read SERVERPROPERTY on the instance doing the reading. SoftwareVersionMajor from the header describes where the backup came from, and it isn’t available until after the capture you’re trying to size, so it can’t inform the table definition anyway.
I haven’t tested what an older instance does when pointed at a backup written by a newer one, and the cross-version rules for reading headers are worth confirming on your own estate before you rely on either behaviour.
What this buys
With the header in a table you can compare files against each other: which belong to the same database, whether the log backups form a continuous sequence, whether a differential is based on the full you’re about to restore, and whether your target point in time is reachable.
Those are the next several posts. First, though, part 3 covers what happens when the version gate is wrong, because the error SQL Server raises names the restore rather than the table that caused it.
Have you got version-gated code that broke on an upgrade in a way the error message didn’t explain? Tell me about it on Bluesky or LinkedIn.
References
- RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. The full result set definition.
LastValidRestoreTime,TimeZone, andCompressionAlgorithmeach carry an “Applies to: SQL Server 2022 (16.x) and later versions” note, andTimeZoneis documented as nvarchar(32). ↩ - SERVERPROPERTY (Transact-SQL) – Microsoft Learn. Documents
ProductMajorVersion, used here to decide which column set to expect. ↩ - INSERT (Transact-SQL) – Microsoft Learn. Covers the
INSERT ... EXECform and its requirement that the destination match the result set. ↩
That “insert into” trick with the backup header is slick.