If your environment is anything like mine, backups are usually not allocated the speediest disk. Generally speaking, those disks are reserved for data, logs, and tempdb. So it’s up to us squeeze every last bit of performance out of our backup jobs.  If you’re using maintenance plans to do your backups, chances are you’re not making the most of what resources you have. The database I’m testing with today is roughly 240GB. Backing it up to a single backup file takes 3169.466 seconds (52 minutes 51 seconds) at a rate of 74.628 MB/sec. Let’s see how fast I can make it using the tools SQL Server has built into it.

Backup Compression

In SQL Server 2008, we were given backup compression. This feature offers us a huge savings, both in the amount of disk space that backups consume, and in the amount of time it takes to perform the backup. After all, the less physical IO we have to do, the better.

BACKUP DATABASE [MyDatabase] TO
DISK = N'F:\myServer_backup\MyDatabase_backup.bak'
WITH INIT
, COMPRESSION
GO

If I run the backup using compression, the backup size drops to a mere 47GB and completes in 1597.213 seconds (148.090 MB/sec). So I’ve cut my time in half just by enabling backup compression. That’s outstanding, but I think I can do better.

Multiple files

One common method for improving backup performance is to use multiple backup files. Writing data to a backup device is done by a backup thread, one thread per backup device. So the more backup files/devices you have, the more threads you have working for you. My next test was to backup the database to 2 files on different mount points.

BACKUP DATABASE [MyDatabase] TO
DISK = N'F:\myServer_backup\MyDatabase_backup_1of2.bak' ,
DISK = N'F:\myServer_backup2\MyDatabase_backup_2of2.bak'
WITH INIT
, COMPRESSION
GO

Using 2 backup devices and compression brought this backup time down to 1272.981 seconds (185.809 MB/sec), knocking 30 minutes off my initial run time.

MAXTRANSFERSIZE and BUFFERCOUNT

Two lesser-known options in the BACKUP command are MAXTRANSFERSIZE and BUFFERCOUNT. The MAXTRANSFERSIZE specifies, in bytes, the largest unit of transfer between SQL Server and your backup device. The default is 1 MB. I tested the effect of setting this option to 2MB, still using compression and 2 backup devices.

BACKUP DATABASE [MyDatabase] TO
DISK = N'F:\myServer_backup\MyDatabase_backup_1of2.bak' ,
DISK = N'F:\myServer_backup2\MyDatabase_backup_2of2.bak'
WITH INIT
, COMPRESSION
, MAXTRANSFERSIZE=2097152
GO

The resulting backup took 1299.216 seconds (182.057 MB/sec). I also tested using only one backup device. This test ran in 1744.748 seconds (136.185 MB/sec). So doubling the MAXTRANSFERSIZE didn’t seem to offer any benefit in my tests.

BUFFERCOUNT specifies the number of IO buffers that SQL Server allocates to the backup process. The default value is determined by the number of data volumes and the number of backup devices.  In my environment, the default is a measly 12.  For my next test, I set BUFFERCOUNT = 1024.

BACKUP DATABASE [MyDatabase] TO
DISK = N'F:\myServer_backup\MyDatabase_backup_1of2.bak' ,
DISK = N'F:\myServer_backup2\MyDatabase_backup_2of2.bak'
WITH INIT
, COMPRESSION
, BUFFERCOUNT = 1024
GO

The result? My backup completed in 613.190 seconds (385.739 MB/sec)! You can combine the MAXTRANSFERSIZE and BUFFERCOUNT options to further optimize your throughput. You want to be careful though: MAXTRANSFERSIZE * BUFFERCOUNT = the amount of memory required for the backup operation.  Setting these too high may result in an ugly memory error, “There is insufficient system memory in resource pool ‘internal’ to run this query.”  I tested my backup using both options and squeezed out a teeny bit more throughput, but at this point I believe I’m hitting the limit of what my hardware will allow (based on SQLIO tests).

So overall, I took my backup time from 53 minutes all the way down to 10 minutes. And bonus: no third party tools were needed. Not too shabby!