SQL Server -Tail Log Backups

Tail Log Backup: Tail Log Back up is Back up of Tail of Log Just Before Before Restore operation of Database.

  • As it is Log Backup so we can take Tail Log Backup when recovery model Database either Full or Bulk Logged.

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

  • If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:
    BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
    -- If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:
    BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR
  • Tail Lob Backup capture the tail of log even database is in offline mode,Database is damaged or missing data file.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.