Tail-Log Backup and Restore in SQL Server May 31, 2018 by Prashanth Jayaram 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. 12345678910111213141516171819202122 -- create a new database SQLShackDSDemo USE MASTERGODROP DATABASE IF EXISTS SQLShackTailLogDB GOCREATE DATABASE SQLShackTailLogDBON ( NAME = SQLShackTailLogDB_dat, FILENAME = 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SQLShackTailLogDB_dat.mdf', SIZE = 50, MAXSIZE = 150, FILEGROWTH = 10 ) LOG ON ( NAME = SQLShackTailLogDB_log, FILENAME = 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SQLShackTailLogDB_log.ldf', SIZE = 15MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) ; GO ALTER DATABASE SQLShackTailLogDB SET RECOVERY FULLGO Now, create a table SQLShackAuthor, and add some data to it. You can see that the table is populated with 5 rows. 1234567891011 USE SQLShackTailLogDB;GO -- create a table SQLShackAuthorCREATE TABLE SQLShackAuthor ( AuthorID INT IDENTITY(1,1) PRIMARY KEY, authorName varchar(100));GOINSERT SQLShackAuthor (authorName) VALUES ('Brain Lockwood'),('Ahmad Yaseen'),('Samir Behara'),('Luna Cvetovic'),('Prashanth Jayaram')GO Let’s select everything from the SQLShackAuthor table 12 SELECT * FROM SQLShackAuthor;GO 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. 123 -- create a full backupBACKUP DATABASE SQLShackTailLogDBTO DISK = 'f:\PowerSQL\SQLShackTailLogDB_FULL.bak' with FORMAT, INIT, COMPRESSION Next, add few more rows into the SQLShackAuthor table. 123 -- insert additional recordsINSERT SQLShackAuthor (authorName) VALUES ('Esat Erkec'),('Craig Porteous'),('Ben Richardson'),('Thomas LeBlanc'),('SQLShack')GO Select everything from the table using the following SQL query. 12 SELECT * FROM SQLShackAuthor;GO 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 12345 -- take TailLogDB offline-- delete .mdf data file from the hard driveUSE master;GOALTER 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 123 USE master;GOALTER 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. 12345 -- create a tail-log backup SQLShackTailLogDBUSE MASTERGOBACKUP LOG SQLShackTailLogDBTO DISK = 'f:\PowerSQL\SQLShackTailLogDB_taillog.log' 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. 12345 -- create a tail-log backup SQLShackTailLogDB WITH CONTINUE_AFTER_ERROR;BACKUP LOG SQLShackTailLogDBTO DISK = 'f:\PowerSQL\SQLShackTailLogDB_taillog.log'WITH CONTINUE_AFTER_ERROR;GO 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. 12345678910 -- restore the databaseUSE masterRESTORE DATABASE SQLShackTailLogDBFROM DISK = 'f:\PowerSQL\SQLShackTailLogDB_FULL.bak'WITH NORECOVERY;GO RESTORE LOG SQLShackTailLogDBFROM DISK = 'f:\PowerSQL\SQLShackTailLogDB_taillog.log';GO Validate the data by querying SQLShackAuthor. 1234 USE SQLShackTailLogDBGOSELECT * FROM SQLShackAuthor;GO 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: Open the SQLCMD mode in SSMS Initiate a full backup of SQLShackTailLogDB Add few more records into the SQLShackAuthor table. Perform a tail-log backup with NO_RECOVERY, NO_TRUNCATE options; this would leave the database in the “restoring” state. 123456789101112131415161718192021 :connect HQDBt01\sql2017-- create a full backupuse SQLShackTailLogDBGOSELECT * FROM SQLShackAuthor;GO BACKUP DATABASE SQLShackTailLogDBTO DISK = 'f:\PowerSQL\SQLShackTailLogDB_FULL.bak' with FORMAT, INIT, COMPRESSION -- insert additional records to SQLShackAuthorINSERT SQLShackAuthor (authorName) VALUES ('Sifiso W. Ndlovu'),('Ayman Elnory'),(' Jefferson Elias'),('Ed Pollack'),('Kandi Humpf')GOSELECT * FROM SQLShackAuthor;GO--backup the taillog backup and leave the source database in restoring stateuse masterGOBACKUP LOG [SQLShackTailLogDB] TO DISK = N'f:\PowerSQL\SQLShackTailLogDB_taillog.log' WITH NO_TRUNCATE , FORMAT, INIT, NAME = N'SQLShackTailLogDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10GO 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. Connect to the instance. Issue the restore database command with the NO_RECOVERY option. Apply the residual changes in the tail-log backup to SQLShackTailLogDB Validate the table 12345678910111213141516171819 :connect HQDBT01\SQL2017--restore the database with a different name [SQLShackTailLogDB_Test]USE [master]GORESTORE DATABASE [SQLShackTailLogDB_Test] FROM DISK = N'F:\PowerSQL\SQLShackTailLogDB_FULL.bak' WITH FILE = 1, MOVE N'SQLShackTailLogDB_dat' TO N'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SQLShackTailLogDB_Test_dat.mdf', MOVE N'SQLShackTailLogDB_log' TO N'g:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\SQLShackTailLogDB_Test_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 5 --apply the tail log backup RESTORE LOG [SQLShackTailLogDB_Test] FROM DISK = N'F:\PowerSQL\SQLShackTailLogDB_taillog.log' WITH FILE = 1, NOUNLOAD, STATS = 5GO--valdiate the outputuse [SQLShackTailLogDB_Test]GOSELECT * FROM SQLShackAuthor;GO 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 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 References Tail-Log Backups (SQL Server) Back Up the Transaction Log When the Database Is Damaged (SQL Server) Using SQL Server Management Studio About Latest Posts Prashanth JayaramI’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) SQL Server In-Memory database internal memory structure monitoring - January 30, 2019 Using SQL Power Doc to Discover, Diagnose and Document SQL Server - January 21, 2019 Inventory and document your SQL Server estate using PowerShell - January 14, 2019 Related posts: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent Understanding Backup and Restore operations in SQL Server Docker Containers SQL Server Database Backup and Restore reports An overview of the process of SQL Server backup-and-restore What is backup and restore in SQL Server disaster recovery?