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.

This is Part 16 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

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

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

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

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server Database Backup Tutorial with Examples
• Different SQL Server Recovery Models Step by Step Tutorial with Examples
• SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
• SQL Server FULL Recovery Model Step by Step Tutorial with Examples
• SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
• Different Types of SQL Server Backups
• Permissions Required to Take Database Backup in SQL Server
• FULL Database Backups in SQL Server Step by Step Tutorial with Examples
• DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
• TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
• COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
• MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
• FILE Backup in SQL Server Step by Step Tutorial with Examples
• FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples