Proving the Restore, Part 5: Striped Backups Are All or Nothing
Large databases are often backed up across several files at once. Instead of one 800 GB file, you get eight files of about 100 GB, written in parallel:
|
1 2 3 4 5 6 |
BACKUP DATABASE [Sales] TO DISK = N'D:\Backups\Sales_FULL_S00.bak' , DISK = N'D:\Backups\Sales_FULL_S01.bak' , DISK = N'D:\Backups\Sales_FULL_S02.bak' , DISK = N'D:\Backups\Sales_FULL_S03.bak' WITH CHECKSUM, COMPRESSION; |
The motivation is throughput. Several writers can saturate storage that one writer can’t, and on the restore side several readers help the same way.
The consequence is one backup written across four files, with a naming convention that makes them look like four separate backups.
Media sets and media families
The terminology matters here because the error messages use it.
A media set is the complete backup output. A media family is one file within it. Striping across four files creates one media set of four media families, and each file records how many families the set has and which one it is.[1]
Each stripe therefore records the size of the set it belongs to, so SQL Server can report what’s missing without you tracking it separately.
Restoring requires naming every family in a single statement:
|
1 2 3 4 5 6 |
RESTORE DATABASE [Sales_test] FROM DISK = N'D:\Backups\Sales_FULL_S00.bak' , DISK = N'D:\Backups\Sales_FULL_S01.bak' , DISK = N'D:\Backups\Sales_FULL_S02.bak' , DISK = N'D:\Backups\Sales_FULL_S03.bak' WITH NORECOVERY, CHECKSUM; |
There’s no incremental form. You can’t restore three now and add the fourth later, because the data is interleaved across all of them rather than partitioned by object.
What a missing stripe looks like
Leave one out and SQL Server reports it before writing anything. The message names the expected count, the supplied count, and the rule:
|
1 2 3 |
Msg 3132, Level 16, State 1, Line 1 The media set has 4 media families but only 3 are provided. All members must be provided. |
Because it’s raised up front, a restore script missing a stripe fails immediately rather than partway through. It’s also one way to discover the stripe count when you don’t know it.
Discovering the count from one file
RESTORE LABELONLY reads the media header from a single device. It returns a single row describing the media set and this file’s place in it:[2]
|
1 2 |
RESTORE LABELONLY FROM DISK = N'D:\Backups\Sales_FULL_S00.bak'; |
FamilyCount is the number of media families in the set. MediaSetId identifies the set, and every stripe belonging to it carries the same value. FamilySequenceNumber says which family this particular file is.
Read FamilyCount from any one stripe you can find, then confirm you have that many files whose MediaSetId matches:
|
1 2 3 4 5 6 7 8 9 10 11 |
/* One row per candidate stripe, populated by LABELONLY against each file. */ SELECT [media_set_id] = [l].[MediaSetId] , [expected] = MAX([l].[FamilyCount]) , [found] = COUNT(DISTINCT [l].[FamilySequenceNumber]) FROM #labelonly_results AS [l] GROUP BY [l].[MediaSetId] HAVING COUNT(DISTINCT [l].[FamilySequenceNumber]) < MAX([l].[FamilyCount]); |
Any row returned is an incomplete set. Counting distinct FamilySequenceNumber rather than counting files also catches the case where the same stripe was copied twice under different names. Grouping by MediaSetId protects against two backups whose files landed in the same folder with overlapping names: same-looking filenames, different media sets, and neither set complete.
Why the filename is not evidence
The _S00 through _S07 suffixes in these examples are a convention, not a feature. SQL Server neither creates nor interprets them, and it stripes across whatever four names you give it.
Two things follow. A folder containing Sales_FULL_S00.bak through Sales_FULL_S03.bak is not proof of a complete set, because the fourth file might belong to a different night’s backup that reused the name. And a gap in the numbering isn’t proof of a missing stripe either, if the convention changed.
Check MediaSetId and FamilyCount rather than the filenames.
The retention trap
The failure mode this creates in practice is a retention policy that operates on files rather than on backup sets.
A rule that keeps the newest N files, or deletes anything older than N days evaluated per file, can delete part of a media set. Stripes are written in parallel but finish at slightly different times, so a boundary that falls between them removes some and keeps others. The remaining files pass any check that only looks for file presence, and they won’t restore.
Two defences. Make retention operate on the whole set, deleting all families together or none. And make your validation check FamilyCount rather than counting files, so a partial set is reported as broken rather than as present.
msdb helps with the first one. backupmediafamily has one row per family joined to a single backupset row through media_set_id, so the grouping you need is already recorded:[3]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT [b].[database_name] , [b].[backup_finish_date] , [b].[media_set_id] , [stripe_count] = COUNT(*) , [files] = STRING_AGG([m].[physical_device_name], N', ') 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] = 'D' GROUP BY [b].[database_name] , [b].[backup_finish_date] , [b].[media_set_id] ORDER BY [b].[backup_finish_date] DESC; |
STRING_AGG needs SQL Server 2017 or later.[4] On 2016 use the FOR XML PATH form, or just return the rows unaggregated.
A note on partial restores
One case does let you restore from fewer files than were written, and it’s worth separating from striping so it doesn’t muddy the rule.
Backing up individual files or filegroups produces separate backup sets, and those can be restored individually as part of a piecemeal restore. That’s a different operation with different syntax, and the BackupType values reflect it: 4 for a file backup, 6 for a differential file backup, against 1 for a full database backup.[5]
A striped full database backup is a single backup set spread across media families. A filegroup backup set is a different backup of a different scope. The first is all or nothing; the second is designed to be assembled in pieces.
Next
A complete full backup is the foundation, and the next thing stacked on it is usually a differential. A differential only restores onto the exact full it was based on, and SQL Server records enough in both headers to prove the pairing before you try it. That’s part 6.
Have you had a retention policy eat part of a striped set? I’d like to hear how it was caught. Bluesky or LinkedIn.
References
- Media Sets, Media Families, and Backup Sets – Microsoft Learn. Defines the relationship between a media set and its media families, and establishes that every family of a set is required to restore it. ↩
- RESTORE LABELONLY (Transact-SQL) – Microsoft Learn. Returns the media header from a single device, including
FamilyCountandMediaSetIdused here to detect incomplete sets. ↩ - backupmediafamily – Microsoft Learn. One row per media family, joined to
backupsetthroughmedia_set_id, which is what makes set-aware retention possible. ↩ - STRING_AGG (Transact-SQL) – Microsoft Learn. Available from SQL Server 2017, which is the version floor for the aggregation used above. ↩
- RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. Documents the
BackupTypevalues, including 1 for database, 4 for file, and 6 for differential file. ↩