A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence.

The following are the Tail-log backup scenarios

• The tail-log backup process is a common scenario where we intend to migrate very large databases within a short outage window. For example, if you were planning to shut off a database at one location, and then restore it on a different server, the last thing you would want to do before you shut off the database is to initiate a tail log backup. The term, “tail” implies that it is the end of the log backup sequence. In this way, we ensure that there is zero data loss and that a chain for the restore operation is maintained. In case we don’t initiate a tail-log backup, as we would end up with data los, we would lose all the transactions that happened after the last log backup.
• If the database fails to start or goes offline, in such cases we may want to restore the database immediately. In the middle of such an incident, the database administrator’s job is to safeguard the database and to bring the database up and running as quickly as possible. It’s easy to start the recovery and start restoring the database backups. However, before starting the recovery, there is an intermediate step that one has to perform and the process is known as tail-log backup. It is initiated regardless of when the last t-log backup was made.
• When the database is damaged or corrupted, try to initiate a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

The NORECOVERY and CONTINUE_AFTER_ERROR are the backup log clauses that can be used with Tail-log backup scenarios

Getting started

Let’s now deep-dive into the log options and backup and recovery scenarios of tail-log backup.

To demonstrate the workings of the tail log backup, let’s create a database SQLShackTailLogDB and set the recovery model of the database to FULL.

Now, create a table SQLShackAuthor, and add some data to it. You can see that the table is populated with 5 rows.

Let’s select everything from the SQLShackAuthor table

In this section, we will discuss the creation of the backup of the SQLShackTailLogDB database. The following backup command is initiated and the backup file SQLShackTailLogDB_FULL.bak is written to the disk. Now, the backup file includes these 5 records that we just added.

Next, add few more rows into the SQLShackAuthor table.

Select everything from the table using the following SQL query.

Now let’s say that the backup, SQLShackTailLogDB has suffered a failure. In order to simulate this, perform the following tasks:

• Take the database offline

  1 2 3 4 5

  -- take TailLogDB offline -- delete .mdf data file from the hard drive USE master; GO ALTER DATABASE SQLShackTailLogDB SET offline

  
  

  

  1	-- delete .mdf data file from the hard drive

• Go ahead and take a look at the file system and delete the corresponding mdf file of SQLShackTailLogDB

  1	--Browse the folder and delete the file

  
  

  

• Bring the database online using the following T-SQL

  1 2 3

  USE master; GO ALTER DATABASE SQLShackTailLogDB SET ONLINE

  
  

  

Now, perform the important step: switch the session context to the Master Database. Then initiate a backup of the log file. You’ll notice an error message saying that the Backup Log is terminating abnormally.

The work-around for this error is to use WITH CONTINUE_AFTER_ERROR clause. This will force SQL Server to initiate the log backup.

This time, the log backup log is successfully processed.

Let’s start the restore process.

The WITH NORECOVERY option in the restore database command maintains the state so that further log files can be applied to bring the database online.

Now, restore the log file as well. We’ll restore the log from the disk file that we exported a few moments ago; it contains the remaining 5 rows of the SQLShackAuthor table.

Validate the data by querying SQLShackAuthor.

Migration Scenario:

In this scenario, the database SQLShackTailLogDB is restored with a different name and the current state of the database is left as “restoring” so that the database does not accept new connections or new transactions.

The following example can be further simplified to have a different restore location as well, with very little modification. However, that is not in the scope of the following example.

Continue with the following tasks:

1. Open the SQLCMD mode in SSMS
2. Initiate a full backup of SQLShackTailLogDB
3. Add few more records into the SQLShackAuthor table.
4. Perform a tail-log backup with NO_RECOVERY, NO_TRUNCATE options; this would leave the database in the “restoring” state.

The following output shows that the full backup, the insertion of few rows, and the tail-log backup were executed successfully.

Let’s discuss the database restore part of the script.

1. Connect to the instance.
2. Issue the restore database command with the NO_RECOVERY option.
3. Apply the residual changes in the tail-log backup to SQLShackTailLogDB
4. Validate the table

We can see the status of the source and the destination databases in the following output:

Summary

In this article, we understood the importance of the tail-log backup and the various scenarios that will help us understand database recovery in the context of a tail-log backup.

Table of contents

References

• Tail-Log Backups (SQL Server)
• Back Up the Transaction Log When the Database Is Damaged (SQL Server)
• Using SQL Server Management Studio

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021