Improving Backups

3 4

You can improve both the performance and the execution of backups by using a few simple techniques. In this section, you'll find tips for enhancing performance and for improving your backups in other ways.

Enhancing Backup Performance

Enhancing backup performance is an important topic because the faster the backup runs, the shorter the time SQL Server's performance is degraded by the backup. Using the following techniques will help improve backup performance and in some cases can help improve restore performance as well. (Database restoration is explained in Chapter 33.)

• Use multiple backup devices. Having multiple backup devices enables SQL Server to perform some of the backup operations in parallel. SQL Server accomplishes this by striping the backup among several devices. To do this, SQL Server creates a number of threads based on the number of data files and the number of backup devices. Backup performance is also improved by additional threads that are used in writing to these devices. Performing operations in parallel reduces the amount of time that these operations take, especially on a multiple-processor system. This technique will improve both backup and recovery performance.
• Use multiple data files in the database. By using several smaller data files in the database rather than one large one, you will enable SQL Server to perform more of the backup in parallel. This technique will improve both backup and recovery performance.
• Use multiple LAN segments to perform the backup. By splitting the backup over multiple LAN segments, you can increase the network bandwidth available for the backup. Two LAN segments provide twice the bandwidth of one segment, three segments provide three times the bandwidth, and so on.
• Stage the backup. To improve backup performance, you can perform the backup as a disk backup and then copy the disk backup files to tape. This method improves performance because a disk is faster than tape, and it allows you to keep the last few backups available on disk. This technique will improve restore performance for only the backup files left on disk.
• Use differential backups. Differential backups will improve the performance of each backup, but if you use them, a restoration of the entire database will take much longer, as you'll see in Chapter 33. If backup times are unacceptable, this method might provide the best solution for your system. If you need to restore data only rarely, the risk might be acceptable.

Miscellaneous Tips

The following tips for performing backups might or might not apply in your environment:

• Store backups off-site. If you store your backups off-site, the backups might survive a disaster such as a fire or flood. The backup data is much more important than the computer system itself.
• Verify the backup. A backup might not always be good. Tapes can go bad, especially if you use the same tapes over and over again. By verifying the backup (at least occasionally), you will at least know that the tape is good.
• Don't reuse the same backup medium every day. If you reuse the same backup medium every day, you might not be able to recover data that is deleted several days before you try to recover it. Rotate the backup tapes so that you can restore at least several days' worth of information.
• Keep records. You should document how the backup works and how to rebuild the system if necessary. Remember, you might not always be there to rebuild the system yourself.
• Back up system tables. Remember to periodically back up system databases such as master and msdb.

These tips are designed to help you develop your own backup strategy. Every system is different and every company's needs are different. Again, you must develop the strategy that is right for you.