Transaction Log Backups

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

When restoring a transaction log backup, Microsoft® SQL Server™ rolls forward all changes recorded in the transaction log. When SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time the backup operation started. If the database is recovered, SQL Server then rolls back all transactions that were incomplete when the backup operation started.

Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease your risk of losing data.

Note  Sometimes a transaction log backup is larger than a database backup. For example, a database has a high transaction rate causing the transaction log to grow quickly. In this situation, create transaction log backups more frequently.

Transaction log backups are used only with the Full and Bulk-Logged Recovery models. For more information, see Using Recovery Models.

Using Transaction Log Backups with 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 a log backup is missing or damaged, you must create a database or differential database backup and start backing up the transaction logs again. Retain the previous transaction logs backups if you want to restore the database to a point in time within those backups.

The only time database or differential database backups must be synchronized with transaction log backups is when starting a sequence of transaction log backups. Every sequence of transaction log backups must be started by a database or differential database backup.

Usually, the only time that a new sequence of backups is started is when the database is backed up for the first time or a change in recovery model from Simple to Full or Bulk-Logged has occurred. For more information, see Switching Recovery Models.

Truncating the Transaction Log

When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:

• The most recent checkpoint.
  
  
• The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.

  This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

• The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.

  This represents the earliest point that SQL Server still has to replicate.

Conditions for Backing Up the Transaction Log

The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running.

Do not back up the transaction log:

• Until a database or file backup has been created because the transaction log contains the changes made to the database after the last backup was created. For more information, see Using File Backups.
  
  
• If the transaction log has been explicitly truncated, unless a database or differential database backup is created after the transaction log truncation occurs.

Restoring Transaction Log Backups

It is not possible to apply a transaction log backup:

• Unless the database or differential database backup preceding the transaction log backup is restored first.
  
  
• Unless all preceding transaction logs created since the database or differential database was backed up are applied first.

  If a previous transaction log backup is lost or damaged, you can restore only transaction logs up to the last backup before the missing transaction log.

• If the database has already recovered and all outstanding transactions have been either rolled back or rolled forward.

  When applying transaction log backups, the database must not be recovered until the final transaction log has been applied. If you allow recovery to take place when applying one of the intermediate transaction log backups, you cannot restore past that point without restarting the entire restore operation, starting with the database backup.

Creating a Sequence of Transaction Log Backups

To create a set of backups, you typically make a database backup at periodic intervals, such as daily, and transaction log backups at shorter intervals, such as every 10 minutes. You must have at least one database backup, or a covering set of file backups, to make log backups useful. The interval between backups varies with the criticality of the data and the workload of the server. If your transaction log is damaged, you will lose work performed since the most recent log backup. This suggests frequent log backups for critical data, and highlights the importance of placing the log files on fault tolerant storage.

The sequence of transaction log backups is independent of the database backups. You make one sequence of transaction log backups, and then make periodic database backups that are used to start a restore operation. For example, assume the following sequence of events.

The transaction log backup created at 8:00 P.M. contains transaction log records from 4:00 P.M. through 8:00 P.M., spanning the time when the database backup was created at 6:00 P.M. The sequence of transaction log backups is continuous from the initial database backup created at 8:00 A.M. to the last transaction log backup created at 8:00 P.M. The following procedures can be used to restore the database to its state at 10:00 P.M. (point of failure).

Restore the database using the last database backup created.

1. Create a backup of the currently active transaction log.
   
   
2. Restore the 6:00 P.M. database backup, and then apply the 8:00 P.M. and active transaction log backups.

   The restore process detects that the 8:00 P.M. transaction log backup contains transactions that have occurred prior to the last restored backup. Therefore, the restore operation scans down the transaction log to the point corresponding to when the 6:00 P.M. database backup completed and rolls forward only the completed transactions from that point on within the transaction log backup. This occurs again for the 10:00 P.M. transaction log backup.

Restore the database using an earlier database backup (earlier than the most recent database backup created).

1. Create a backup of the currently active transaction log.
   
   
2. Restore the 8:00 A.M. database backup, and then restore all four transaction log backups in sequence. Do not restore the 6:00 P.M. database backup. This rolls forward all completed transactions up to 10:00 P.M.

   This process will take longer than restoring the 6:00 P.M. database backup.

The second option points out the redundant security offered by a chain of transaction log backups that can be used to restore a database even if a database backup is lost. You can restore an earlier database backup, and then restore all of the transaction log backups created after the database backup was created.

Note  It is important not to lose a transaction log backup. Consider making multiple copies of log backup sets. This can be accomplished by backing the log up to disk, then copying the disk file to another device, such as a separate disk or tape.

Recovery and Transaction Logs

When you finish the restore operation and recover the database, all incomplete transactions are rolled back. This is required to restore the integrity of the database.

After this has been done, no more transaction log backups can be applied to the database. For example, a series of transaction log backups contain a long-running transaction. The start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. There is no record of a commit or rollback operation in the first transaction log backup. Therefore, if a recovery operation runs when the first transaction log backup is applied, the long-running transaction is treated as incomplete. Data modifications recorded in the first transaction log backup for the transaction are rolled back. SQL Server does not allow the second transaction log backup to be applied after the recovery operation has run.

Therefore, when restoring transaction log backups, the database must not be recovered until the final transaction log has been applied. This prevents any transactions from being partially rolled back. The only time outstanding transactions need to be rolled back is at the end of the last restore operation.