Working with Transaction Log Backups
The topic is relevant only for databases that are using the full or bulk-logged recovery models.
Applies to: SQL Server 2008 R2 and higher versions.
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. In SQL Server 2005 and later versions, you can back up the log while any full backup is running.
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.
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 files.
To limit the number of log backups that you need to restore, it is essential to routinely back up your data. For example, you might schedule a weekly full database backup and daily differential database backups.
Note
By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases, you can suppress these log entries by using trace flag 3226, if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).
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.
Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups.
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 and Tail-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.