Tail Log Backups in SQL Server

What is Tail-Log Backup?

A tail-log backup captures the log records that have not been backed up since the recent last log backup to current point in time before restore. Generally we generate a tail log to make database into NoRecovery mode (Inaccessible). The tail log backup can be generated from FULL / BULK-LOGGED Recovery model databases.

Unlike other log backups, this tail log backup can be generated even if database is damaged. The tail log backup is also generated using backup log command only with two options a) NoRecovery and b) continue_after_error

NoRecovery – We need to use this option when database is online, After issuing this command it ensures Database is not changed after tail-log backup, it makes database in restoring state, if database is in restoring state, users requests will not processed.

Backup log database_name to disk =’path of tlog file’ with norecovery

No_Truncate / Continue_after_error – Using one of this options we can generate a tail-log backup on a damaged database.

a) Create a database and table in it
b) Insert few records into the table
c) Offline the database and rename data file
d) Try to bring the database online, it wont allow because, it wont find the data file

At that time we can use the below command to generate tail log file from a damaged database.

Backup log damaged_dbname to disk=’path of tlog file’ with no_truncate

Using No_Truncate option, the current active transaction log can be backedup when the data files are inaccessible or damaged or missing and log file is undamaged.

Backup log damaged_dbname to disk=’path of tlog file’ with continue_after_error