Tail-log backups (SQL Server)
Applies to: SQL Server
This article is relevant only for backup and restore of SQL Server databases that are using the full or bulk-logged recovery models.
A tail-log backup captures any log records that haven't yet been backed up (the tail of the log), to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup is the last backup of interest in the recovery plan for the database.
Not all restore scenarios require a tail-log backup. You don't need a tail-log backup if the recovery point is contained in an earlier log backup. A tail-log backup is unnecessary if you're moving or replacing (overwriting) a database, and don't need to restore it to a point of time after its most recent backup.
Scenarios that require a tail-log backup
We recommend that you take a tail-log backup in the following scenarios:
If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the
WITH NORECOVERYoption of the BACKUP Transact-SQL statement.
If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, use the
NO_TRUNCATEis effectively the same as a copy-only transaction log backup. Using
WITH NORECOVERYis optional, because no transactions can occur at this time.
If a database is damaged, try to take a tail-log backup by using the
WITH CONTINUE_AFTER_ERRORoption of the
On a damaged database, backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database doesn't contain any bulk-logged changes. If a tail-log backup can't be created, any transactions committed after the latest log backup are lost.
The following table summarizes the
CONTINUE_AFTER_ERROR options for
|BACKUP LOG option||Comments|
Important: Avoid using
When you back up the tail of the log of a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see the next section.
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 the metadata in a tail-log backup is incomplete, the backupfilegroup table is 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:
To create a tail-log backup, see Back Up the Transaction Log When the Database Is Damaged (SQL Server).
To restore a transaction log backup, see Restore a Transaction Log Backup (SQL Server).