TSQL: Backup a database to multiple files

I wanted to see how much I could reduce backup times by specifying multiple files in the BACKUP 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

tsql_multiple_backup_files_sql_server

2 Comments

  1. Steve says:

    That’s an interesting lab experiment and I can see the possible value. But why do we backup? So we can restore. I’d be more interested in the load and restore times of a mutli-file backup.

    What kind of performance do you get on a smaller transfer’s vs one big transfer between disks? How much faster does the DB restore? Come online?

    I’d also like to point out, that unless you are writing to separate disks, these numbers don’t mean much. The answers to these questions would justify or negate the ramifications of maintaining so many disks just for log backups and the redundancy and backup of those drives to ensure you don’t lose one of those files.

  2. Rhys says:

    Hi Steve,

    Funny you should say but I’m doing a RESTORE version as we speak. This will be published soon but the results are far from exciting.

    “I’d also like to point out, that unless you are writing to separate disks, these numbers don’t mean much” <- I wouldn't agree here. Disk is far from being the only bottleneck. Cheers, Rhys

Leave a Reply