TSQL: Backup a database to multiple files

I wanted to see how much I could reduce backup times by specifying multiple files in theBACKUP TSQL command. Here’s a script I wrote to do this and I present a summary of the results below. The times are based on a database that produced a backup file(s) of approximately 51GB. You mileages will vary here based on a whole bunch of factors. Therefore consider these results illustrative and do your own testing.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Single file
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\single_file_backup.bak';
GO

-- Two files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\two_files_file1.bak',
DISK = '\\path\to\backup\location\two_files_file2.bak'
GO

-- Five files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\five_files_file1.bak',
DISK = '\\path\to\backup\location\five_files_file2.bak',
DISK = '\\path\to\backup\location\five_files_file3.bak',
DISK = '\\path\to\backup\location\five_files_file4.bak',
DISK = '\\path\to\backup\location\five_files_file5.bak'
GO

-- Ten files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\ten_files_file1.bak',
DISK = '\\path\to\backup\location\ten_files_file2.bak',
DISK = '\\path\to\backup\location\ten_files_file3.bak',
DISK = '\\path\to\backup\location\ten_files_file4.bak',
DISK = '\\path\to\backup\location\ten_files_file5.bak',
DISK = '\\path\to\backup\location\ten_files_file6.bak',
DISK = '\\path\to\backup\location\ten_files_file7.bak',
DISK = '\\path\to\backup\location\ten_files_file8.bak',
DISK = '\\path\to\backup\location\ten_files_file9.bak',
DISK = '\\path\to\backup\location\ten_files_file10.bak'
GO

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Results are as follows…

Files	1	2	5	10
CPU time (ms)	16878	13527	16159	21745
Elapsed time (ms)	1983023	1545460	1372302	1237149