Tail-Log Backups

  • Article

Icon showing a blue database disk This topic is relevant only for databases that are using the full or bulk-logged recovery models.

In most cases, under the full or bulk-logged recovery models, SQL Server 2005 requires that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

SQL Server 2005 usually requires that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost. For more information, see "Restoring Without Using a Tail-Log Backup" later in this topic.

Backing Up the Tail of the Log

Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement. We recommend that you take a tail-log backup in the following situations:

  • If the database is online, before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:
    BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

    Note

    To avoid an error, the NORECOVERY option is necessary.

  • If the database is offline and does not start.
    Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.
    BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }

    Important

    We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.

    If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

The following table summarizes these options.

BACKUP LOG option

Comments

NORECOVERY

Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup.

The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified,

{ CONTINUE_AFTER_ERROR | NO_TRUNCATE }

Use NO_TRUNCATE or CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.

ms179314.note(en-US,SQL.90).gifNote:

When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see "Tail-Log Backups with Incomplete Backup Metadata," later in this topic.

To create a transaction log backup when the database is damaged

Tail-Log Backups That Have Incomplete Backup Metadata

Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.

If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1.

If the metadata in a tail-log backup is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. Most of the backupfilegroup table columns are NULL; the only meaningful columns are as follows:

  • backup_set_id
  • filegroup_id
  • type
  • type_desc
  • is_readonly

Restoring Without Using a Tail-Log Backup

Restore scenarios in which a tail-log backup is unnecessary include the following:

See Also

Concepts

Copy-Only Backups
Database States
Applying Transaction Log Backups
Creating Transaction Log Backups

Other Resources

BACKUP (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Expanded the discussion of bypassing the tail-log backup into a separate section, "Restoring Without Using a Tail-Log Backup."
Changed content:
  • Updated the "Backing Up the Tail of the Log" section.