Prashanth Jayaram

Tail-Log Backup and Restore in SQL Server

May 31, 2018 by

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

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

  • Bring the database online using the following T-SQL

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:


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

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV


Prashanth Jayaram
Backup and restore

About 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