Transaction Log Backups

By creating both database and transaction log backups, it is possible to restore a database to the exact point of failure, and minimize, or completely eliminate, loss of data due to the failure.

Some of the characteristics of a system for which you would consider using transaction log backups include:

• Any loss of changes after the last database backup is unacceptable, such as an OLTP financial trading database system.

  It is not acceptable to lose any transactions.

• The resources required to perform database backups only are limited, such as lack of backup storage space or time available to perform the backup operation.

  For example, a 10 terabyte database would require a lot of disk space and time to back up regularly in its entirety.

• Returning the database to a point before a failure occurred is desirable.

  For example, you want to return the database to the point in time 10 minutes before a failure occurred, rather than recovering all or nothing.

• Database changes are frequent.

  A large number of changes to the database occur over a relatively short period of time resulting in the last database backup becoming out of date quickly. Backing up the entire database frequently because of these rapid changes may not be possible due to reasons listed earlier.

Because transaction log backups generally use fewer resources than database backups, they can be created more frequently than database backups. This reduces the window of time in which a failure could occur, such as the entire server failing, and the last backup operation being performed. This decreases the amount of data that could be potentially lost. Additionally, by applying transaction log backups, you can recover the database to a specific point in time prior to when a problem occurred, if necessary.

Note There can be rare situations where the transaction log backup is larger than a database backup. For example, a database has a high transaction rate or transactions that affect a large proportion of the database causing the transaction log to grow quickly, or the transaction log is backed up infrequently. In these situations, create transaction log backups more frequently.

A recommended process for backing up a database and any transaction log is:

1. Back up the database regularly, such as every night.
2. Back up the transaction log frequently, such as hourly or more for highly active systems.

The process for restoring the database and applying the transaction log backups is:

1. Back up the current transaction log if a failure occurs (unless, for example, the disk containing the transaction log files has failed).
2. Restore the most recent database backup.
3. Apply all of the transaction log backups in the same sequence in which they were created after the database backup.
4. Apply the final transaction log backup created in Step 1 to restore the database to the point of failure.

Therefore, although the use of transaction log backups increases recoverability, creating and applying them is also more complex than simply using database backups. Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If anything happens to break the sequence of transaction log backups, such as nonlogged operations, you should create a database or differential database backup and start backing up the transaction logs again. Any previous transaction log backups are not necessary and can be overwritten unless you want to return the database to a point prior in time included in these transaction log backups. For more information about the conditions required to create a sequence of transaction log backups, see Creating and Applying Transaction Log Backups.

See Also