Tail-Log Backups

sql-server-logoIn this article we will offer information about tail-log backups, a feature which is available for SQL Server versions 2005 and newer. This is a topic which you should be aware of if your backup and restore process  for your SQL Server databases  is using either the FULL or Bulk-Logged recovery model.
A tail-log backup contains the log records  that were not yet backed up (thus the name, tail of the log).

This type of backup is done in order to prevent any work loss and helps to keep the log sequence intact. Before you will be able to recover your database to its most recent point, you must have a backup of its transaction log tail. In the recovery plan for the database, this backup will be the last backup of interest.

Please take note that not all restore scenarios will require you to make a tail-log backup. You are not forced to make a tail-log backup if the recovery point is already present in an earlier log backup. Moreover, a tail-log backup is not necessary if you are either replacing, this includes overwriting, or moving a database and you do not need to restore it to a state from a time which is after its most recent backup.

Situations where a Tail-Log Backup is required

The most common situations where a tail-log backup is recommeded to be done are the following:

      • If the database is online and your intention is to do a restore operation of the database, your first step should be to back up the tail of the log. If you do not want to risk having errors for an online database, you must use the WITH NORECOVERY option of the BACKUP T-SQL statement.
      • BACKUP LOG myDatabse TO DISK = '\\local\mydatabase.bak'
        WITH NORECOVERY, NO_TRUNCATE
      • We recommend using NO_TRUNCATE only when your database is damaged!
      • In case the database is offline and unable to start but you need to restore the database, first you will have to back up the tail of the log. Due to the fact that no transactions can occur during this time, using the WITH NORECOVERY option is not mandatory.
      • If a database is damaged, you should try to make a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP statement. CHECKSUM is also an option which works well in this situation.
      • BACKUP DATABASE myDatabase 
        TO DISK = 'Z:\myDatabase.bak'
        WITH CHECKSUM, CONTINUE_AFTER_ERROR
        • On a damaged database, backing up the tail of the log can be successful only if the log files are not damaged, the database is in a state which supports tail-log backups and the database does not contain any bulk-logged changes.
        • In the case when a tail-log cannot be created, any transactions commited after the latest log backup will be lost.

Incomplete Backup Metadata Tail-Log Backups

The tail-log backups capture the tail of the transaction log even in the case in which the database is offline, missing data files or damaged. This might be the cause for incomplete metadata from the restore information commands and msdb. However, even in this situation, only the metadata is incomplete; the captured log is complete and fully usable.

In case a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata column’s value is set to 1.

If case in which in a tail-log backup you have metadata that  is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. In this case, most of the backupfilegroup table columns will be populated with NULL values.

Helpful tutorials

    1. How To : Backup the Tail of the Transaction Log (SSMS step by step tutorial)
    2. How To : Backup the entire Transaction Log when SQL Server database is damaged

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

Leave a Reply

Your email address will not be published. Required fields are marked *