Working with Transaction Log Backups

 The topic is relevant only for databases that are using the full or bulk-logged recovery models.

This topic presents concepts about how to back up and restore (apply) transaction logs. Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. With log backups, you can recover the database to the point of failure or to a specific point in time. We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log drive. The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. Taking a log backup every 15 to 30 minutes might be enough. If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log file or files.

Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. Restoring a database by using only file backups can become complex. Therefore, we recommend that you start with a full database backup when you can. Thereafter, backing up the transaction log regularly is necessary. This not only minimizes work-loss exposure but also enables truncation of the transaction log. Typically, the transaction log is truncated after every conventional log backup. However, log truncation can be delayed. For more information, see Factors That Can Delay Log Truncation.

In SQL Server 2005, you can back up the log while any full backup is running.

The Log Chain

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time, or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

To restore a database up to the point of failure, the log chain must be intact. That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups.

If you are using only file backups, you must back up the log from the beginning of the first full file backup. You can start taking log backups immediately after the first full file backup. We recommend starting then because the first log backup can take a long time. While the log is being backed up, you back up the other files. To restore the database from only file backups, the set of full file backups must be augmented with one or more log backups that cover the interval between the first and last file backup.

Note

To identify the backup that starts the log chain in a set of backups, query the begins_log_chain column of the backupset table, or run RESTORE HEADERONLY on the backup device to see the BeginsLogChain column in the results set.

Taking regular transaction log backups is necessary. In addition to letting you restore the backed-up transactions, a log backup truncates the log to remove the backed up log records from the log file. If you do not back up the log frequently enough, the log files can fill up. For information about how to handle a full transaction log, see Troubleshooting a Full Transaction Log (Error 9002).

Important

If a log backup becomes missing or damaged, start a new log chain by creating a full or differential database backup and then backing up the transaction log to start a new log chain. We recommend that you retain transaction logs backups that come before a missing log backup, in case you ever want to restore the database to a point in time within those backups. For information about how to help protect your backups, see Security Considerations for Backup and Restore.

For information about how to create log backups, see Creating Transaction Log Backups.

How Are Log Backups Used?

Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Then, you recover the database. This rolls back all transactions that were incomplete when the recovery started and brings the database online. After the database has been recovered, you cannot restore any more backups.

Important

To help prevent work loss before an offline restore or after a failure, we recommend that you back up the tail of the log to capture any log records that are not yet backed up. For more information, see Tail-Log Backups.

To restore transaction log backups

• Applying Transaction Log Backups

See Also

Concepts

Considerations for Switching from the Simple Recovery Model
Considerations for Switching from the Full or Bulk-Logged Recovery Model
Backup Under the Full Recovery Model
Introduction to Transaction Logs
Overview of Restore and Recovery in SQL Server

Other Resources

Understanding and Managing Transaction Logs

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Moved information about how to help secure log backups to Security Considerations for Backup and Restore.