In this article, we will offer information about tail-log backups, a feature that 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 recommended 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.
- NORECOVERY is an option that backs up the tail of the log and leaves the database in RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail log of the log before a RESTORE operation.
- In order to perform a log backup that will skip log truncation and then take the database into the RESTORING state in an atomic state, use the NO_TRUNCATE and NORECOVERY options together.
-
If during the process of restoring the database, the status is stuck in RESTORING state, we have an article that will guide you through fixing this.
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 that 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 that 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 committed 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 a 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.