SQL SERVER – Creating Multiple Backup Files – Stripped

Multiple Backup Files can form one striped backup set. A customer wanted several files for a single database backup.

-- New, unique output filenames on the SQL Server host:
-- BACKUP DATABASE [CollationTest] TO
-- DISK=N'D:\Data\CollationTest_part1.bak',
-- DISK=N'E:\Backup\CollationTest_part2.bak'
-- WITH CHECKSUM, STATS=10;
-- Restore requires all media families from that striped backup:
-- RESTORE DATABASE [RestoreTest] FROM
-- DISK=N'D:\Data\CollationTest_part1.bak',
-- DISK=N'E:\Backup\CollationTest_part2.bak'
-- WITH MOVE N'YourDataLogicalName' TO N'D:\RestoreTest\RestoreTest.mdf',
-- MOVE N'YourLogLogicalName' TO N'E:\RestoreTest\RestoreTest.ldf';

In SSMS, open Tasks, Back Up and review destinations. Multiple striped destinations are not separate full copies. Keep all required media families together. Supply them all for restore.

I previously wrote stripped and named one file backupcopy.bak. That naming could imply redundancy. Striping is different from mirroring or manually splitting an existing file. Distinct destination names make the example clearer.

Independent resources can improve throughput for a suitable workload. Two files on the same underlying disk don’t guarantee improvement. Use new destinations and preserve earlier sets. Test a restore with explicit safe paths.

Reference: Striped backup media families.

Related reading

• Comprehensive Database Performance Health Check
• Pinal Dave on YouTube
• Forwarded Records and Performance – SQL in Sixty Seconds #155
• Hide Code in SSMS – SQL in Sixty Seconds #154
• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151

A striped media family is not an independent full backup, it is one required part of the set.

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