Proving the Restore, Part 7: One File, Hundreds of Backup Sets
Point RESTORE HEADERONLY at a full backup and you get one row. Point it at the file a fifteen-minute log backup job has been appending to since Sunday and you get several hundred.
Each row is a separate backup set inside one physical file. Restoring from that file means naming which set you want, and the column that identifies it is Position.
Appending versus discrete files
BACKUP writes a new file or appends to an existing one depending on whether you specify INIT or NOINIT. NOINIT is the default, so a repeated backup to the same path appends rather than overwrites.[1]
Two strategies follow from that. Discrete files give you one backup per file, with the timestamp usually in the name. Appending gives you one file per database that grows through the week.
The appended approach reduces file count considerably. A database with fifteen-minute log backups produces 96 files a day, or 672 a week, as discrete files. As one appended file it produces one.
The failure modes are equivalent, which is the part worth being clear about. Losing one discrete log backup breaks the chain from that point forward. Losing the single appended file breaks the same chain, just all at once. Neither strategy protects the chain by itself; that comes from copying the backups somewhere else.
Where the two differ is in operational surface. Fewer files means less to enumerate, less to copy, and fewer chances for a retention script to remove one file out of a set. It also means a single corrupt file takes out more, and that copying a partially written file is harder to reason about.
Reading the sets
RESTORE HEADERONLY against an appended file returns every set on the device, oldest first:
|
1 2 |
RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_LOG.trn'; |
The columns that matter for navigation are Position, BackupType, BackupStartDate, BackupFinishDate, FirstLSN, and LastLSN. Position is the ordinal of the backup set on the volume, and it’s the value you pass to FILE when restoring.[2]
Captured into a table as in part 2, the sets you want become a straightforward filter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @after_lsn numeric(25, 0) = 194000000023700001 /* diff's LastLSN */ , @stopat datetime2(3) = '2026-08-17T14:30:00'; SELECT [h].[Position] , [h].[BackupFinishDate] , [h].[FirstLSN] , [h].[LastLSN] FROM #header_results AS [h] WHERE [h].[BackupType] = 2 /* transaction log */ AND [h].[LastLSN] > @after_lsn AND [h].[BackupStartDate] <= @stopat ORDER BY [h].[Position]; |
Filtering on LastLSN rather than on date is what keeps this correct. Log backups are written in LSN order, and while that usually matches the date order, the LSN is the value the restore sequence actually depends on. Part 8 goes through the continuity checks in full.
Restoring one set out of many
FILE = n selects the backup set:
|
1 2 3 |
RESTORE LOG [Sales_test] FROM DISK = N'D:\Backups\Sales_LOG.trn' WITH FILE = 188, NORECOVERY, CHECKSUM; |
Without FILE, the restore uses the first backup set on the device, which for an appended file is the oldest one present rather than the one you probably want.[3]
Replaying a week of logs therefore means one RESTORE LOG statement per position, in ascending order. A hundred and twenty positions is a hundred and twenty statements, which is a large part of why generating the script is worth automating. Part 11 covers that.
Position is per device, not per database
Position numbers the backup sets on one media set. It carries no meaning outside that file.
Two consequences. Position 188 in this week’s log file is a different backup from position 188 in last week’s, so a script that hardcodes positions is only valid against the exact file it was generated from. And if the file is ever recreated with INIT, positions restart at 1 while the filename stays the same.
Storing the generated restore script without the file it was generated against is therefore not much use. Regenerating from the current headers takes seconds and can’t drift.
Cross-checking against msdb
msdb.dbo.backupset records position for each backup it took, so you can compare what the instance thinks it wrote against what the file actually contains:[4]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT [b].[position] , [b].[backup_finish_date] , [b].[first_lsn] , [b].[last_lsn] , [m].[physical_device_name] FROM [msdb].[dbo].[backupset] AS [b] INNER JOIN [msdb].[dbo].[backupmediafamily] AS [m] ON [m].[media_set_id] = [b].[media_set_id] WHERE [b].[database_name] = N'Sales' AND [b].[type] = 'L' ORDER BY [b].[backup_finish_date] DESC; |
A mismatch between the two is informative. If msdb lists positions the file doesn’t contain, the file was recreated or replaced. If the file contains sets msdb has no record of, those backups were taken by a different instance, which happens with availability group backups taken on a secondary.
Note the column naming. msdb uses first_lsn and last_lsn in lower case with underscores, while the header returns FirstLSN and LastLSN. They hold the same values. Part 13 goes through the rest of the vocabulary differences.
Next
With the sets identified and ordered, the question is whether they form an unbroken sequence. Adjacent log backups have to meet exactly, and SQL Server gives you the two numbers needed to prove it. That’s part 8.
Do you append log backups to one file per database, or keep them discrete? I’d be interested in what drove the choice. Bluesky or LinkedIn.
References
- BACKUP (Transact-SQL) – Microsoft Learn. Documents
INITandNOINIT, and thatNOINITis the default, which is why repeated backups to one path append rather than overwrite. ↩ - RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. Defines
Positionas the position of the backup set in the volume, for use with theFILEoption. ↩ - RESTORE Arguments (Transact-SQL) – Microsoft Learn. Covers
FILE = backup_set_file_numberand its default behaviour when omitted. ↩ - backupset – Microsoft Learn. Records
position,first_lsn, andlast_lsnfor each backup the instance performed. ↩