Microsoft SQL Server 2000 Administrators Companion

3 4

Scheduling backups is a highly subjective task. Numerous factors are involved in designing the backup schedule. Because system performance is degraded during backups, backups might be limited to off-hours. You also might have only a small window of time in which to complete the backup. In this section, we'll review a few tips that can help you in setting up a backup schedule. Keep in mind that even though backups affect the performance of the system, they are crucial operations that must be done in order to protect the system from data loss.

Backup Scheduling Tips

The following tips can help you determine the ideal backup schedule for your system:

• Plan full backups for off-hours. If your company does not run in a 24by-7 environment (24 hours a day, 7 days a week), off-hours are the best time to perform backups.
• Schedule a full backup over several days. If your database is large and you cannot perform a full backup in the allotted time, divide the backup operation into sections. You can perform a file or filegroup backup on one piece of the database at a time. Over a period of several days, you can back up all of the data.
• Use differential backups. If you cannot afford the time to perform a full backup every night, you can perform differential backups during the week and a full backup over the weekend.
• Customize your backup plan. Every system is different, and every company is different. Design the backup schedule that best fits your needs.

REAL WORLD  Planning Backups

Here are a few sample backup plans that might help you develop your own backup schedule:

• Small system in an 8-by-5 environment This type of system will usually allow for full backups every evening. The transaction log might need to be backed up only once per day, depending on the size of the transaction log and the number of transactions performed.
• Medium-sized system in a 24-by-7 environment A medium-sized system that is running in a 24-by-7 environment does not allow a lot of downtime for backups. However, if you have a system of this size, you might be able to perform full backups on the weekends. The following table shows what a backup schedule for a medium-sized company might look like:

• Large system in a 24-by-7 environment Extremely large systems might not allow a full backup to be done in just one day. A compromise would be to split the full backup over several days, as shown in the following table. (In this sample schedule, the full backup is performed over Saturday and Sunday.)

This information is intended to give you an idea of how to schedule backups. Because every system and its requirements are different, only you can decide how best to schedule your backups.