Performance Tuning Tips for SQL Server Backup and Restore

SQL Server backup and restore performance is often a greater issue than many DBAs realize. This is because the time it takes to backup and restore a database plays a very large part in how large a single SQL Server database can be. For example, if it takes more than 24 hours to backup or restore a database, obviously this presents some problems.

What can sometimes happen to the DBA is that a database may be of reasonable size now, given their current server hardware, and backups and restores may take a reasonable amount of time. But as time goes by, the database grows, and at some point the amount of time to backup and restore the database becomes too long given the backup and restore window available. The moral to this story, keep a close eye on backup and restore times, and factor this in when projecting future hardware needs for your SQL Server databases. [2000, 2005, 2008] Updated 2-3-2009

*****

Depending on the size of your database, select the backup method that is the best tradeoff between backup and restore time. For example, full backups take the longest to perform, but are the fastest to restore. Differential backups are overall faster than full backups, but take longer to restore. [2000, 2005, 2008] Updated 2-3-2009

*****

For fastest backups, perform a disk backup to a local drive array (ideally, to an array dedicated to backups only), then move the backup file(s) over the network to another server where the file can be stored, or to a tape device. Backing up a database directly to tape on a local device, or to a tape device over the network, or to directly to a hard disk over the network, is generally slower. As a rule of thumb, I keep one copy of my database backups on the local server (even though they have been moved off the server to tape) as a convenience should I need to restore the backup quickly.

For maximum backup speed, the local array you back up to should be RAID 1 or RAID 10 due to the high percentage of writes going on. A RAID 5 array is not recommended because they don’t handle a high percentage of disk writes (which occurs during disk backups) efficiently.

The actual throughput you get on your system will be different from the above figures, but the above figures should help convince you of the need to use the fastest disk arrays you can to write your backups. [2000, 2005, 2008] Updated 2-3-2009

*****

If you are backing up your databases directly over a network (not really recommended as it hurts performance), one way to boost throughput is to perform the backup over a dedicated network which is devoted to backups and restores only. All devices should be on the same high-speed switch. Avoid going over a router when backing up over a network, as they can greatly slow down backup speed. [2000, 2005, 2008] Updated 2-3-2009

*****

If you are backing up over a network, or if you are backing up directly to disk, and then are moving the disk backups over the network to another server, performance can be enhanced if you use the fastest network cards and switches possible. The same is true for restores. [2000, 2005, 2008] Updated 2-3-2009

*****

Continues…

Leave a comment

Your email address will not be published.