Striped Backups: Writing One Backup to Several Files

One backup can write to several destination files at the same time. Striped backups divide a single backup set across media families. Restoring that set needs every stripe, so the file-handling process matters as much as throughput.

Treat Striped Backups as One Backup Set

Striped backups distribute one backup set across several devices. They don't create several independent full backups. Each media family contributes part of the set.

Copy, retain, and verify the files together. If one stripe is missing, the remaining files don't become a smaller usable backup. That dependency belongs in every cleanup script and recovery runbook handling the output.

I explain that dependency before discussing performance. A folder full of files can look reassuring while one required member sits elsewhere. Use a disposable database and placeholder paths for the example.

The directories must exist, and the SQL Server service account needs write access. Choose new filenames for the rehearsal. Don't overwrite an existing backup collection merely to test a different device count.

Write to Two Destinations Explicitly

List each DISK target in the BACKUP statement. The targets below use Windows paths with preserved backslashes. They represent one full backup set.

CHECKSUM asks the engine to perform backup checksum checking. Compression is another choice you should evaluate against CPU and storage capacity. Keep the same options when comparing one device with several so the experiment isolates the device layout.

The two paths can reside on separate real storage routes or on the same underlying pool. Different drive letters don't prove independent throughput. Inspect the storage architecture before expecting parallel destinations to help.

A shared bottleneck can cap the whole operation. The filenames are only the addresses of the devices. The storage behind those addresses decides how much useful parallel work can happen.

BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SqlBackups\YourDatabase_full_1.bak',
   DISK = N'E:\SqlBackups\YourDatabase_full_2.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Test More Devices Without Promising Speed

A four-device statement extends the same pattern. Use another set of fresh filenames and keep the backup source comparable. Measure duration, CPU, and storage throughput on your own server.

More destinations can improve throughput when the path has capacity. They also create more files to manage. Don't choose a device count from another server's results or from the number of available drive letters.

I include the copy and restore stages in the test. A faster backup that complicates transfer and slows recovery isn't automatically a win. The operational goal is a usable recovery point within the required window.

Record all filenames as one manifest. That makes later copying and retention deterministic. A stripe should never depend on someone remembering which fourth file belonged with the other three.

BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SqlBackups\YourDatabase_test_1.bak',
   DISK = N'D:\SqlBackups\YourDatabase_test_2.bak',
   DISK = N'E:\SqlBackups\YourDatabase_test_3.bak',
   DISK = N'E:\SqlBackups\YourDatabase_test_4.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Find Striped Backups in the Media Family History

The msdb database stores backup-set metadata and the related media families. Join by media_set_id and keep family_sequence_number in the output. The sequence makes it clear which files belong to the same striped set.

The physical_device_name column records the path used for the backup. It doesn't prove that the file still exists at that path after a transfer or cleanup operation.

Use the query as an inventory and compare it with the actual file manifest. Include the backup finish time so two similarly named runs remain distinguishable. Retention cleanup should work at the set level.

Don't delete one family because its filename appears older than another under a different naming pattern. The restore dependency follows the backup set, not a guess based on the directory listing.

SELECT b.backup_set_id,b.database_name,b.backup_finish_date,
       m.family_sequence_number,m.physical_device_name
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_id
WHERE b.database_name = N'YourDatabase' AND b.type = 'D'
ORDER BY b.backup_finish_date DESC,m.family_sequence_number;

Verify and Restore With Every Stripe

Supply all devices when verifying or restoring the backup. VERIFYONLY checks whether the backup set is complete and readable under its checks. It doesn't establish that a full restore and database validation will succeed.

Perform an actual restore to an isolated name and target paths. Inspect FILELISTONLY first, then use the exact logical file names in the MOVE clauses.

Don't restore over the production database for this test. Avoid REPLACE as a shortcut. The sample below illustrates the required stripe list for inspection and verification.

Your actual restore needs a separate target, every logical file mapped, and enough capacity. Which operator could rebuild this device list during an outage? The saved manifest should answer that without a scavenger hunt across backup folders.

RESTORE FILELISTONLY
FROM DISK = N'D:\SqlBackups\YourDatabase_full_1.bak',
     DISK = N'E:\SqlBackups\YourDatabase_full_2.bak';
RESTORE VERIFYONLY
FROM DISK = N'D:\SqlBackups\YourDatabase_full_1.bak',
     DISK = N'E:\SqlBackups\YourDatabase_full_2.bak'
WITH CHECKSUM;

Tune Buffers With a Capacity Budget

BUFFERCOUNT controls the number of backup buffers. MAXTRANSFERSIZE controls the maximum transfer size. Their combination affects memory used by the operation, alongside other backup work.

Defaults already reflect engine decisions. Don't increase both without checking concurrent backup jobs and the instance's available memory. A setting that helps one isolated backup can create pressure when several databases run together.

Keep supported values and deployment-specific limits in view when testing. Change one factor at a time and save the resulting throughput and CPU. Restore behavior deserves the same review.

The backup command is only half of the recovery path. Keep a measured reason and an owner for the buffer setting. Record its reversal instead of repeating it by habit.

Keep Striped Backups Whole Through Retention

Transfer and validate every file before considering the backup offsite or otherwise protected. Retain the manifest with the backup metadata. Test a missing-stripe failure only in an isolated rehearsal, leaving the source files intact.

A file inventory should fail the release when any required family is absent. Treat a partial copy as incomplete, even if most of its bytes reached the destination.

Use striped backups when your own tests support the throughput trade and the file process can preserve every member. Keep restore tests in the acceptance gate. The number of files doesn't measure protection.

A complete, readable set with a demonstrated restore is the useful result. Parallel writing helps only when the recovery team can reliably bring those pieces together again.

Related reading on this blog: Multiple Backup Copies Stripped: SQL in Sixty Seconds #156 and Full, Differential and Log Backups: A Practical Guide.

A striped backup is not several backup copies, it is one recovery set stored in several files.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.