When a database gets corrupted or failure occurs, before restoring the database from the backups you've created, it is recommended to create a tail-log backup of the records that haven't been backed up. This helps restore the database to the exact point at which it failed, preventing data loss.
Read on to learn
about the other reasons when you need to back up the tail of the transaction
log. Also, understand how to take a tail-log backup and restore it to get back
the data you fear losing in the event of a crisis.
Why and When Should
You Back Up The Tail of the Log?
Tail-log backup
helps capture the tail of the log records when the database is offline,
damaged, or data files are missing.
Reasons Why You Need To Back Up the Tail of
the Transaction Log
- Database is corrupted, or the
data file is corrupted or deleted.
- Database goes offline and doesn't
start; you may want to recover the database as quickly as possible. But,
before you begin recovery, first take the tail-log backup.
- The database is online, and you
plan on restoring the database, start by backing up the tail of the log.
- Migrating database from one
server to the other server.
Example Demonstrating the Need to Take
Tail-log Backup
Let's say, you
run DBCC CHECKDB to check for corruption in the database. It returns
consistency errors and you decide to restore the previously taken backups, such
as the Full backup. Then, you restore the Differential and all the transaction
log backups. But you don't want to lose the log records that haven't been
captured in the transaction log backup. So, to avoid losing those log records
(i.e., the tail of the backup) and maintain the log chain intact, you will need
to take Tail Log Backup.
Let’s consider a scenario.
Assume taking a Full database
backup and Log backups after every one hour.
Time |
Event |
8:00 AM |
Create a Full Database Backup |
9:00 AM |
Take Transaction Log Backup |
10:00 AM |
Take Transaction Log Backup |
11:00 AM |
Take Transaction Log Backup |
11:30 AM |
Failure occurs |
You can restore
the database starting from Full backup (taken at 8 AM), then restore all the three
transaction log backups (taken at 9 AM, 10 AM, 11 AM). But, there are no
backups from 11:00 AM till 11:30 AM, resulting in data loss.
So, how to recover the database without
data loss between 11-11:30 AM?
Take t-log
backup by executing the BACKUP LOG command ‘WITH NO_TRUNCATE’ option. It will
create a t-log backup file. Restore the file after the last transaction log
backup (11 AM) WITH NORECOVERY to recover the lost data.
USE MASTER
GO
BACKUP LOG [Database] TO DISK = ‘C:\ProgramFiles\MSSQLServer\Data\Tail_Log1.LOG’ WITH NO_TRUNCATE;
How to Back up and Restore Tail of the Log?
Before we discuss the process to back up the tail of the transaction log and restore it, it’s important to know the clauses you need for creating a t-log backup.
- NORECOVERY: Using this clause leave the database into the restoring state. This assures that the database won’t change after the t-log backup.
- NO_TRUNCATE: Use this clause only when the database is damaged.
- CONTINUE_AFTER_ERROR: If
a database is damaged and you cannot take t-log backup, back up the tail
of the log using CONTINUE_AFTER_ERROR.
Demo
- Create a new database
CREATE DATABASE Tail_LogDB;
GO
USE Tail_LogDB;
GO
- Create a new table and insert some data into it.
CREATE TABLE Employee (
EmployeeID int IDENTITY(1000,1)
PRIMARY KEY NOT NULL,
EmployeeAge int
);
GO
This T-SQL query will create a table named Employee with columns
‘EmployeeID’ and ‘EmployeeAge’.
- Create a stored procedure to add more records to the table.
CREATE PROCEDURE InsertEmployee
AS
DECLARE @i int = 100
WHILE @i > 0
BEGIN
INSERT Employee (EmployeeAge)
VALUES (@i)
Set @1 -=1
END
GO
EXECUTE InsertEmployee;
GO
SELECT * FROM Employee;
GO
Executing this
T-SQL query will create an ‘InsertEmployee’ stored procedure that runs through
a loop to add 100 more records into the Employee table. Then, select the
Employee table to verify that everything works.
- Create a full backup of the Tail_LogDB
BACKUP DATABASE Tail_LogDB
TO DISK = 'C:\TempDB\ Tail_LogDB_FULL.bak'
- Insert some more records into the table
EXECUTE InsertEmployee;
GO
SELECT * FROM Employee;
GO
After executing this T-SQL query, we will have 200 records
in the database table.
- Simulate a database failure
- Right-click on Tail_LogDB > Tasks > Take Offline.
- Select the ‘Drop all active connections’ checkbox and press OK.
- Now refresh the database, and you can see that the db is now OFFLINE.
- Next, go to the location where the data file is stored (i.e., TempDB folder), and you can see the db that we just saved.
- Now go to the location where the .mdf file and .ldf files for the Tail_LogDB database are saved. Delete the .mdf file.
Now let's head back to SSMS and understand how we can recover from this disaster.
Bring Database Back Online
·
Right-click on Tail_LogDB > Tasks > Bring
Online.
·
A dialog box with errors, click Close.
Refresh the database.
As you can see,
the database status has changed to Recovery Pending. Before attempting the
restore operation, ensure to back up the tail of the log to capture the second
instance of the 100 records we added into the database.
Now, let’s take the tail of the log.
Switch into the
master database and execute the BACKUP LOG statement with the
CONTINUE_AFTER_ERROR option. This option will ensure to perform tail log backup
even if any error occurs.
USE master;
GO
BACKUP LOG Tail_LogDB
TO DISK = 'C:\TempDB\Tail_LogDB.log'
WITH CONTINUE_AFTER_ERROR;
GO
Let's initiate
the restore process by restoring the full database backup 'WITH NORECOVERY'
option. Using this option specifies that the restore procedure would not
attempt to undo or roll back any uncommitted transactions. This is important
because if a modification to the data had begun but not finished when the
failure occurred, there would be a record in the transaction log. Typically,
SQL Server will attempt to roll back any of these partially completed changes
during a restore, and we don't want this to happen.
USE master
RESTORE DATABASE Tail_LogDB
FROM DISK = 'C:\TempDB\Tail_LogDB_FULL.bak'
WITH NORECOVERY;
GO
This restores the backup of the first 100 records.
To complete restoring the entire record set, let's restore
the log file as well.
RESTORE LOG Tail_LogDB
FROM DISK = 'C:\TempDB\Tail_LogDB.log';
GO
- A tail-log backup is useful to avoid losing data when a database is damaged or corrupted. However, you may fail to back up the tail of a damaged database log. So, when executing the BACKUP LOG statement, use WITH CONTINUE_AFTER_ERROR option to take t-log backup.
- You must also take a tail-log backup before restoring a database in an ONLINE state. If the database is in OFFLINE state and doesn't start, back up the tail of the transaction log WITH NORECOVERY before performing the restore procedure.
- It is also recommended to take t-log backup when migrating a large database from one source to another.
- But remember, you can take tail-log backups only if the transaction log file is accessible. Meaning, you cannot perform t-log backup on a database with a corrupted and inaccessible log file.