Backup to multiple files for faster and smaller SQL Server files

Problem

Have you ever wished you could get your backups to run faster? Well there is a way by writing to multiple backup files.

Creating SQL Server backups is pretty simple to do; you can use the SQL Server Management Studio tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy very large files across the network or to the cloud.

Solution

In this article, we look at how to write your SQL Server database backup to multiple files.

In addition to writing your database backup to one file you have the ability to write to multiple files at the same time and therefore split up the workload. The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as have much smaller files that can be moved across the network.

Another advantage to writing to multiple files is if you have multiple storage devices you can write your backup files to different devices and therefore get better I/O throughput. One thing to note is that to get the best I/O throughput you should keep the writing of your backup files and the reading of the database files on different storage.

Below, we cover two ways of writing your backup to multiple files.

Backup to multiple files using SQL Server Management Studio

In SQL Server Management Studio you have the ability to add additional destination files. Just click “Add” and you can add an additional output file. In the example below everything is going to the C:\ drive, but for faster I/O throughput you would want to write to different drives.

Backup to multiple files using T-SQL

Using T-SQL you would write your backup command similar to the following:

BACKUP DATABASE [Northwind] TO 
DISK = 'C:\Northwind_file1.bak', 
DISK = 'D:\Northwind_file2.bak', 
DISK = 'E:\Northwind_file3.bak', 
DISK = 'F:\Northwind_file4.bak' 
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

Next Steps

• Test writing your backups to multiple files to see how much faster you can get your backups to run.
• See if this makes sense to implement in your environment and then plan to make the change.
• In addition to doing this using native SQL Server backups, there are several products in the market that allow you to backup to multiple files and compress the files at the same time. This allows you to have even smaller files and therefore further decrease the time to move the files around your network.
• Also, look at backup compression which can greatly reduce the amount of data that has to be written to the database backup files.
• In addition to writing to a fixed drive letter you can also write the backup to a network share as long as SQL Server has the correct permissions to create files.