Creating and Applying Transaction Log Backups

Backing up the transaction log periodically to create a sequence of transaction log backups offers users the most flexibility in restoring databases. By creating transaction log backups, a database can be restored to any point in time contained within the sequence of transaction logs, right up to the point of failure.

When creating a transaction log backup, the starting point of the backup is where:

• The previous transaction log backup ended (if a transaction log backup has been created).
• The transaction log backed up as part of the most recent database or differential database backup ended, if no previous transaction log backup was created. (A database or differential database backup contains a copy of the active space of the transaction log.)

Note Creating a backup of all the files and filegroups in a database together with a spanning set of transaction log backups is equivalent to creating a database backup. Therefore, whenever a database backup is required when creating or applying a transaction log backup, a complete set of file or filegroup backups can be used instead. For more information, see Using File or Filegroup Backups.

Truncating the Transaction Log

When Microsoft® SQL Server™ finishes backing up the transaction log, it truncates the inactive portion of the transaction log. SQL Server then reuses this truncated, unused space in the transaction log rather than the transaction log continuing to grow and use more space. The inactive portion of the transaction log is that part of the transaction log that is no longer used during the recovery process that the database goes through when SQL Server starts, because all transactions in that part of the log are complete. Conversely, the active portion of the transaction log contains transactions that are still running and have not completed yet.

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

• The most recent checkpoint.

  This represents the earliest point at which SQL Server would have to roll forward transactions during a restore operation.

• The start of the oldest active transaction; 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.

• The start of the oldest CREATE INDEX operation whose completion log record has not been backed up yet.

Conditions for Backing Up the Transaction Log

The transaction log should not be backed up:

• If the trunc. log on chkpt database option is set to TRUE; create a database or differential database backup instead.
• If any nonlogged operations have occurred in the database since the last database backup was created. Create a database or differential database backup instead.
• Until a database backup has been created because the transaction log contains the changes made to the database after the last backup was created.
• If the transaction log has been truncated, unless a database or differential database backup is created after the transaction log truncation occurs.
• If any files are added or deleted from the database; a database backup should be created immediately instead.

Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, without backing up the truncated part of the transaction log, every time a checkpoint occurs in the database, preventing more transaction log backups from being created.

Using SQL Server, backup operations can occur while the database is online and in use. However, some operations are not allowed when the transaction log is being backed up:

• Creating or deleting database files
• Performing nonlogged operations
• Shrinking either the database (automatically or manually) or the database files. For more information, see Shrinking the Database.

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is in progress and one of these operations is attempted, the operation fails and the backup operation continues.

Backing Up the Active Transaction Log

It is possible to back up the currently active transaction log even if the database is inaccessible, for example due to media failure, providing both the primary data file and the transaction log files are accessible.

Note By placing the primary data file and transaction log files on different physical disks than the database files containing the user objects, any media failure on the disks containing the user database files affects only those files. Additionally, placing the primary data file and transaction log files on mirrored or RAID 5 disks can further protect the files from isolated media failure. For more information on primary data files, see Using Files and Filegroups.

This transaction log backup can then be used with the database backup and previous transaction log backups to restore the database to the exact point of failure, although any uncommitted transactions will be rolled back.

Creating a Sequence of Transaction Log Backups

To create a sequence of transaction log backups, you typically make a database backup at periodic intervals, such as daily, and transaction log backups at shorter intervals, such as hourly. The interval between backups varies with the criticality of the data and the workload of the server. Some sites may only need to back up the database weekly and back up the transaction log daily.

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. Two 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 since 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 last 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.

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.

Applying 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.

Recovery and Transaction Logs

When restoring a database backup or applying a transaction log backup, the default is to roll back all uncompleted transactions at the end of the restore operation. This is required to restore the integrity of the database.

Note The operation of rolling back all uncompleted transactions is also called recovering 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 where 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. Because there is no record of a commit or rollback operation in the first transaction log backup, if recovery runs when the first transaction log backup is applied, the long-running transaction would be treated as incomplete, and data modifications recorded in the first transaction log backup for the transaction would be rolled back. SQL Server then does not allow the second transaction log backup to be applied after recovery has run.

Therefore, when applying 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.

To create a database backup

To create a transaction log backup

To create a backup of the currently active transaction log

To restore a database backup

To apply a transaction log backup

See Also