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…
|CPU time (ms)||16878||13527||16159||21745|
|Elapsed time (ms)||1983023||1545460||1372302||1237149|