Write for Us
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 at 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. [6.5, 7.0, 2000, 2005] Updated 6-27-2006
*****
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. Incremental (transaction log) backups are the fastest, but are generally the slowest to restore. [7.0, 2000, 2005] Updated 11-15-2005
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 following information should give you an idea on the major performance differences among different RAID levels when backups are made:
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. [6.5, 7.0, 2000, 2005] Updated 11-15-2005
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. [6.5, 7.0, 2000, 2005] Updated 6-27-2006
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.
For example, a 10MBs connection can only backup to a maximum of 4.4GB per hour, a 100MBs connection can backup up to a maximum of 44GB her hour, and a Gigabit connection can backup up to 440GB an hour. If your databases are huge, you seriously need to consider using the fastest network connections you can get. [6.5, 7.0, 2000, 2005] Updated 3-20-2006
You can speed all backups, including disk backups, by backing up to multiple backup devices at the same time. SQL Server creates a separate backup thread for each backup device, allowing backups to be done in parallel to multiple backup devices. This feature is very important for very large databases (VLDB), although this technique can be used with databases of any size. To take full advantage of threaded backups, the disk subsystem must be able to keep up, otherwise the slow performance of a disk subsystem can negate the benefits of using multiple backup threads. [6.5, 7.0, 2000, 2005] Updated 3-20-2006
Perform backups during times of the day with slower activity. This will help prevent I/O bottlenecks and help prevent your backups from affecting user's response times. [6.5, 7.0, 2000, 2005] Updated 3-20-2006
For very large databases that need regular full backups, consider a fiber-based SAN (storage area network) storage and backup solution for the ultimate in performance. [6.5, 7.0, 2000, 2005] Updated 3-20-2006
Next Page>>
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset