TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples

A tail-log backup captures any log records which has not yet been backed up i.e., the tail of the transaction log to prevent any work loss and to keep the transaction log chain intact. Before you can recover a SQL Server database to its latest point in time, one must back up the tail of its transaction log without fail. Note that the tail log backup will be the last backup of which one can take to recovery the database.

Scenarios when you need to perform a TAIL LOG backup

Microsoft recommends you to Perform a TAIL LOG backup during the following scenario:-

• The database is currently ONLINE and you are planning to perform the restore operation on the database then begin by backing up the tail of the log. To avoid any error for an ONLINE database, you must use the … WITH NORECOVERY option 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 transaction log file. Because no transactions can occur during such time, it is optional to useWITH NORECOVERY during such times.
• If a database is DAMAGED, then try to take a tail-log backup by using WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

On a damaged database backing up the tail of the log can only succeed if the log files are undamaged, and the database is in a state that supports tail-log backups, and at the same time the database does not contain any bulk-logged changes. If you are unable to create a tail-log backup, any transactions committed after the latest log backup will be lost.

Difference between BACKUP NORECOVERY and CONTINUE_AFTER_ERROR options

NORECOVERY: When you specify NORECOVERY option before you take the log backup then you intend to continue with a database restore operation after the successful backup of transaction log file. NORECOVERY option takes the database into the restoring state. This will guarantee that the database does not change after the tail-log backup is completed. The transaction log is truncated unless you specify NO_TRUNCATE option or COPY_ONLY option while running transaction log backups.

Create a TAIL LOG backup WITH NORECOVERY Using TSQL command

BACKUP LOG [MyTechMantra] TO DISK = 'C:\DBBackups\MyTechMantra_NORECOVERY.TRN' WITH NORECOVERY, COMPRESSION, CHECKSUM, STATS = 25

Create a TAIL LOG backup WITH NO_TRUNCATE when Database is Damaged Using TSQL command

Microsoft recommends that you avoid using NO_TRUNCATE, except when the database is damaged.

BACKUP LOG [MyTechMantra]
TO DISK = 'C:\DBBackups\MyTechMantra_NO_TRUNCATE.TRN'
WITH NO_TRUNCATE, COMPRESSION, CHECKSUM, STATS = 25

CONTINUE_AFTER_ERROR : You must specify CONTINUE_AFTER_ERROR option while backing up transactional log file only if you are backing up the tail of aDAMAGED SQL Server Database.

Create a TAIL LOG backup WITH CONTINUE_AFTER_ERROR Using TSQL command

BACKUP LOG [MyTechMantra]
TO DISK = 'C:\DBBackups\MyTechMantra_NORECOVERY.TRN'
WITH NORECOVERY, CONTINUE_AFTER_ERROR, COMPRESSION, CHECKSUM, STATS = 25