Lesson 2: Backup Strategy

[Previous] [Next]

In Lesson 1, you learned about the SQL Server backup process. In this lesson, you will learn how to apply this process to develop a reliable backup strategy that is appropriate for the requirements of your organization. This lesson presents samples of various backup strategies that will help you to develop your own strategy.

After this lesson, you will be able to

• Design an appropriate backup strategy

Estimated lesson time: 30 minutes

Planning a Backup Strategy

There are two overall backup and restore strategies, each with its own strengths and weaknesses:

• Backing up only the database. With this strategy, the entire database is backed up regularly. In case of failure, you will lose all committed transactions that occurred after your most recent database backup.

The primary advantage of using only database backups is simplicity. Backing up is a single operation, normally scheduled at regular intervals. Should a restore be necessary, it can be accomplished easily in one step.

• Backing up the database and the transaction log. With this strategy, the entire database is backed up less frequently; the transaction log is backed up frequently between database backups. In case of failure, you will be able to recover all backed-up transactions and possibly even committed (complete) transactions that occurred since the last transaction log backup. Only uncommitted (incomplete) transactions will be lost.

Transaction log backups provide the information necessary to redo changes made after a database backup was performed. They make it possible to back up large production databases at short intervals, as well as to restore a database up to a specific point in time.

CAUTION

---

Recovery of the active transaction log will be possible only if the transaction log and primary data files are undamaged. For this reason, consider placing the transaction log and primary data files on fault-tolerant disks.

Both of these strategies can be augmented by the use of differential database backups to increase the speed of the backup and restore processes.

Finally, it is possible to perform database backups of individual files or filegroups, segmenting a database backup into smaller backup procedures that can be completed in less time. This may be necessary for very large, busy databases.

The Database Backup Strategy

When using the database backup strategy, you back up the entire database every time a backup is performed. Figure 8.1 illustrates this backup strategy. Database size and frequency of data modification determine the time and resources involved in implementing a database backup strategy.

Figure 8.1 The database backup strategy

Use database backups if

• The database is small. The amount of time required to back up a small database is reasonable.
• The database has few data modifications or is read-only. If the database fails between backups, data modified since the last backup will be lost, so up-to-the-minute recovery and point-in-time recovery will not be possible.
• You are willing to accept the loss of changed data if the database fails between backups and must be restored.

Consider the following example of a backup plan and the steps you would take to restore your database. Assume the following:

• The database contains 10 megabytes (MB) of data.
• The database backup process takes a few minutes to complete.
• The database is used mostly for decision support and is modified very little each day.
• The possibility of losing a day's worth of changes to the database is acceptable. These changes can be re-created easily.
• The system administrator does not want to monitor the log size or perform any maintenance on the transaction log.
• The trunc. log on chkpt. database option is set to true, to ensure that the transaction log does not become full. The transaction log does not hold a record of changes to the database over time and cannot be used to restore the database in case of a system failure.
• A database backup is done each day at 6:00 p.m.
• The database becomes corrupted at 10:00 a.m.

To recover the database, you would restore the database backup from the previous night at 6:00 p.m., overwriting the corrupted version of the database.

The limitation of this approach is that all data modifications that were made since the last database backup at 6:00 p.m. are lost.

NOTE

---

You may be able to recover changes since the backup of the previous night if the transaction log and the primary data file are not damaged (using the BACKUP LOG statement with the NO_TRUNCATE option). However, if the potential data loss is too great, you should consider implementing a backup strategy that includes periodic transaction log backups.

The Database and Transaction Log Backup Strategy

When using a strategy that combines database backups and transaction log backups, you make complete database backups at less frequent regular intervals. Between database backups, the transaction log is backed up, so that you have a record of all database activities that occurred between database backups. This common backup strategy is illustrated in Figure 8.2.

Figure 8.2 The database and transaction log backup strategy

Restoring a database that has been backed up using a database and transaction log strategy involves two steps. First you must restore the most recent complete database backup. Then you apply all of the transaction log backups that were created since the most recent complete database backup.

Use this backup strategy when you cannot afford to lose changes since the most recent database backup or when you need to be able to restore data to a specific point in time.

Consider the following example of a backup plan and the steps you would take to restore your database. Assume the following:

• The database and transaction logs are stored in separate files on separate physical media.
• A database backup is done each night at 6:00 p.m.
• Transaction log backups are performed each day at 9:00 a.m., 12:00 noon, and 3:00 p.m.
• The physical medium that contains the secondary data file(s) is damaged at 1:30 p.m.

You would go through the following steps to recover the database:

1. Back up the transaction log, using the WITH NO_TRUNCATE option. (This is possible only if the transaction log and the primary data files are available.)
2. Restore the database backup that was created the previous night at 6:00 p.m.
3. Apply all transaction logs that were created that day (9:00 a.m. and 12:00 noon).
4. Apply the transaction log backup that was created at the beginning of the restore process (if one was created).

Applying the transaction log created at the beginning of the restore brings the database back to the state it was in when it was damaged. If you are not able to make a backup of the transaction log before starting the restore, you will be able to restore the database to the state it was in at 12:00 noon.

The Differential Backup Strategy

Figure 8.3 illustrates the differential backup strategy. Use this strategy to augment either a database backup strategy or a database and transaction log backup strategy. Differential backups consist only of the portions of the database that have changed since the last database backup.

Figure 8.3 The differential backup strategy

Recovery using a differential backup requires that you restore the most recent complete database backup and the most recent differential backup. If transaction log backups are also made, only those created since the most recent differential backup need to be applied to fully recover the database. Use this strategy to reduce recovery time if the database becomes damaged.

For example, rather than applying many transaction logs, you would use the most recent differential backup to restore data that has changed since the last complete database backup and then apply only the transaction log backups taken since that differential backup.

Consider the following example of a backup plan and the steps that you would take to restore your database. Assume the following:

• A database backup is performed once a week. The last database backup was made on Sunday at 1:00 a.m.
• A differential backup is performed at the end of each business day. A differential backup was performed on both Monday and Tuesday at 6:00 p.m.
• Transaction log backups are performed every hour during the business day (8:00 a.m. to 5:00 p.m.). A transaction log backup was performed on Wednesday at 8:00 a.m. and again at 9:00 a.m.
• The database becomes corrupted on Wednesday at 9:30 a.m.

You would go through the following steps to recover the database:

1. Back up the transaction log, using the WITH NO_TRUNCATE option. (This is possible only if the transaction log and the primary data file are available.)
2. Restore the database backup that was created on Sunday at 1:00 a.m.
3. Restore the differential backup that was created on Tuesday at 6:00 p.m. This backup is the latest differential backup and contains all of the data that has changed since the complete database backup on Sunday at 1:00 a.m.
4. Apply the transaction log backups that were created on Wednesday at 8:00 a.m. and 9:00 a.m.
5. Apply the transaction log backup that was created at the beginning of the restore process (if one was created).

The application of the last transaction log backup brings the database back to where it was at the time it was damaged. If you are not able to make a backup of the transaction log before starting the restore then you will be able to restore the database to the state it was in at 9:00 A.M on Wednesday.

The Database File or Filegroup Backup Strategy

Figure 8.4 illustrates the database file backup strategy. The database filegroup strategy works similarly, except that it works with filegroups rather than individual files. When you implement a database file or filegroup backup strategy, you must back up the transaction log as part of the strategy.

Figure 8.4 The database file backup strategy

Use this strategy for very large databases that are partitioned among multiple files. When combined with regular transaction log backups, this technique makes it possible to perform backups when time is limited.

For example, if you have only one hour to perform a database backup that would normally take four hours, you could create the database using four data files, back up only one file each night, and still ensure data consistency. Transaction log backups could be performed at short intervals during the day.

Consider the following example of a backup plan and the steps you would take to restore your database. Assume the following:

• The data in a database is divided among data files File1, File2, and File3.
• A database backup is performed every week. A complete database backup is performed on Monday at 1:00 a.m.
• Selected files are backed up on a rotating basis each day at 1:00 a.m.

• File 1 is backed up on Tuesday and Friday at 1:00 a.m.
• File 2 is backed up on Wednesday and Saturday at 1:00 a.m.
• File 3 is backed up on Thursday and Sunday at 1:00 a.m.

• Transaction log backups are performed hourly between database backups.
• On Thursday at 8:00 a.m., the physical medium of File2 becomes damaged.

You would go through the following steps to recover the database:

1. Back up the transaction log, using the WITH NO_TRUNCATE option. (This is possible only if the transaction log and the primary data file are available.)
2. Restore the backup of File2 that was created on Wednesday at 1:00 a.m.
3. Apply the transaction log backups made since Wednesday at 1:00 a.m. (2:00 a.m. Wednesday through 7:00 a.m. Thursday).
4. Apply the transaction log backup that was created at the beginning of the restore process (if one was created).

The performance that is gained by using this strategy results from the fact that only transaction log events that affect data stored in the failed file need to be applied. In this example, only transactions for File2 made after 1:00 a.m. on Wednesday are applied. If File1 had failed, transaction log backups made after 1:00 a.m. on Tuesday would have been applied. If File3 had failed, transaction log backups made after 1:00 a.m. on Thursday would have been applied.

Performance Considerations

Consider some of the issues that affect the performance of SQL Server when you back up databases:

• Backing up to multiple physical devices is generally faster than using a single physical device. SQL Server takes advantage of multiple backup devices by writing the data to each backup device in parallel.
• The time needed to back up a database depends on the speed of the physical device. Tape drives are generally slower than disk devices.
• You should minimize concurrent activity when you back up a database. Concurrent activity on SQL Server may affect the time it takes to back up your database.

Lesson Summary

There are two approaches administrators can take when planning a backup strategy. They can back up only the database at frequent intervals, or they can back up the database and the transaction logs. The first strategy allows a simple restore of the database in case of data loss but can result in data being lost during the interval between backups. The second strategy may involve more work when restoring data but often can bring your database back to the state it was in before the loss.