When your databases grow, operations, like a database backup and restore, are going to take a longer time to complete. Maybe at some point you are running out of your time window to complete a database backup or a restore. Instead of expending your time window (if that is even an option) you can use some parameters to speed up your backup and restore times!

This article shows some options and parameters to speed up your SQL Server backup and restore. In some cases I’ve seen backup and restores go 3 times faster with some simple tweaking!

For this article I’m running backup’s and restores on a database that’s around 10GB in size on a SQL Server 2008R2. The database and backup disks are separated on different disks on a SAN.

Optimizing SQL Server Backup time

The first thing we are addressing is the time it takes to complete a database backup.
There are 2 options we can use to speed up the time it takes to complete a full backup: Compression and splitting your backup into multiple files.

Before we start looking at these 2 options, let’s take a look how long our backup takes without any options or compression:

It took SQL Server 69 seconds to create a backup of our database with a throughput of 143 MB/sec.

Backup compression

Let’s turn on compression and see if we can speed up the process, here’s the SQL script I used:

BACKUP DATABASE [RestoreTest]

TO  DISK = N'N:\Backup\RestoreTest_one_wc.bak'
WITH NOFORMAT,
INIT, 
NAME = N'RestoreTest-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
COMPRESSION, 
STATS = 5

GO

Let’s find out if turning on compression has any affect:

Turning on backup compression brought the time to complete the backup down to 51 seconds and increased the throughput to 191 MB/sec! So just by turning on compression we won 18 seconds, that’s almost 25%!

Backup to multiple files

Another option to speed up backup’s is using multiple files to backup your database. When performing a backup to one file SQL Server will only use one CPU to perform that backup operation. If you create multiple files SQL Server will use multiple CPU’s, one for every backup file. This can have a positive impact on your backup operation.

This is how our backup did when we split the backup into 4 files:

As you can see the results are a little disappointing…Instead of completing the backup operation faster, it actually took longer to complete; 57 seconds with a throughput of 170 MB/sec.

Adding more files (I went up to 6) did speed up to process a little but never as fast as the one file with compression backup.

The reason for this is possibly the size of the database, since it is small (10GB) the overhead for writing to multiple files is larger then the benefits of splitting the backup file or we might be limited by storage limits. I’ve seen backup operations for some large databases (2TB) go from 3 hours to complete to 45 minutes!

Optimizing SQL Server Restore time

Now that we’ve seen how we can improve the speed of backup operations, let’s take a look at restores!

There are 2 options we are interested in to optimize our restores: MaxTransferSize and BufferCount.

MaxTransferSize specifies the unit of transfer to be used by SQL Server from the backup media. It can range from 65536 (65 KB) to 4194304 (4MB).
BufferCount specifies the total number of I/O buffers available for the restore operation. This can be a tricky option to use, specifying to many buffers can lead to “Out of memory” errors! Always use this option with care! The total memory used by the recovery process is MaxTransferSize x BufferCount = Memory needed by restore if you do not have the needed amount of memory on your server you will get errors!

You can think of the SQL Server restore process as using buckets to put out a fire.
The BufferCount sets the number of buckets to use to put out the fire while the MaxTransferSize sets how full these buckets should be. As you can imagine using many buckets and only filling them half can impact the time it takes you to put out the fire, just like using only a few buckets but filling them all the way to the top can have a negative impact if the buckets are too heavy to lift.

Setting the MaxTransferSize and BufferCount options can be different for every environment since you need to optimize the values to match your storage and memory configuration. So play around with different values until you get your optimized restore time!

Let’s see how fast our restore completes if we don’t supply the MaxTransferSize and BufferCount parameters:

As you can see it took 171 seconds to restore the database with a throughput of 57 MB/sec.

Before we go and change the MaxTransferSize and BufferCount options let’s see what SQL Server set these parameters to when restore our backup.

To see more information about the backup and restore operations when can set 2 traceflags:

Traceflag 3213, traces SQL Server activity during backup/restore
Traceflag 3605, output trace information to SQL Server Log

Run the following SQL commands before running your restore to get more SQL Server logging:

DBCC TRACEON (3213, -1)
DBCC TRACEON (3605, -1)

Our initial restore above generated the following information in the SQL Server log file:

You'll notice SQL Server decided to use a BufferCount of 6 and a MaxTransferSize of 1024KB to perform the restore operation.

Restore with MaxTransferSize

Let’s perform the same restore again but this time we will set the MaxTransferSize to the maximum allowed value of 4194304 bytes (or 4 MB).

RESTORE DATABASE [RestoreTest]

FROM  DISK = N'N:\Backup\RestoreTest_one_nc.bak'
WITH  FILE = 1, 
NOUNLOAD, 
REPLACE,
MAXTRANSFERSIZE = 4194304, 
STATS = 5

GO

Wow! That really makes a difference! We went from 171 second to 81 seconds and the throughput more then doubled to 121 MB/sec!

Restore with MaxTransferSize and Buffercount

Let’s squeeze a little bit more out of our restore by setting the BufferCount to 100.

RESTORE DATABASE [RestoreTest]

FROM  DISK = N'N:\Backup\RestoreTest_one_nc.bak'
WITH  FILE = 1, 
NOUNLOAD, 
REPLACE,
MAXTRANSFERSIZE = 4194304,
BUFFERCOUNT = 100, 
STATS = 5

GO

We made it even faster this time! 19 seconds faster! We went from 81 seconds when we only used the MaxTransferSize to 62 seconds when we also set the BufferCount!

After this I have tested some more with different BufferCount values but couldn’t get it faster then when I set them to 100.

Keep in mind that on another system other values might work better! Also remember then when you increase the BufferCount to a value that’s too high you can run into “out of memory” errors so be carefull!

Conclusion

Playing around with backup and restore options will help you speed up your backup and restore operations! Play around with the MaxTransferSize and BufferCount options until you get a result your happy with but be careful not to specify the BufferCount value to high!

Here are the graphs from every setting I tested:

References

TechNet SQL Server Backup: http://technet.microsoft.com/en-us/library/ms186865(v=sql.110).aspx