Backing up the tail log

Recovery Point Objective (RPO) is one of the main DBA concerns in creating a Backup policy or disaster recovery plan. The RPO is the acceptable last point in time  for recovery. Once established , the DBA creates backup procedures to protect the RPO.

Read Last known good backup – Assumption is the mother of all xxxx ups  for DBA paranoia motivation

In a perfect world the scheduled backups would cover all situations. But there are situations where the last point in time is not sufficient. Unexpected failures occur such as:

1)      File corruption

2)      Media failure

3)      Drives missing

4)      Data corruption

5)      Msg  5120 Unable to open the physical file

An extra transaction log backup  is required to ensure any log records which haven’t  yet  been backed up are retained . This type of backup is called a tail log backup.

Note: Any transaction log back up is a tail log backup , as you’re always backing up the tail.

A typical scenario could be : 1) Daily full backup 2) hourly log backup. The DBA is supporting a service level agreement (SLA) to recover to the last hour.

Before restoring over the current  database, the DBA takes a tail log backup. Restoring the tail log backup becomes the last backup in the database recovery plan.

BACKUP LOG [sql_tools] TO DISK = N'E:\sqlserver_dba\sql_tools_Log_Tail.bak' WITH INIT

The only exception to the above scenario is where BULK LOGGED is used and minimally logged operations were performed. In that case, you cannot recover those transactions.

What happens if the data files are  deleted or corrupted ? Use the NO_TRUNCATE option if the logs are available

BACKUP LOG [sql_tools] TO DISK = N'E:\sqlserver_dba\sql_tools_Log_Tail.bak' WITH NO_TRUNCATE

Steps to recover a SQL database using the tail log backup

a)      Take tail log backup . Use NO_TRUNCATE if the data files are unavailable

b)      Restore the last valid FULL BACKUP

c)       Restore the hourly log backups

d)      Restore the tail log backup

To understand more on SQL Server Restores read  SQL Server – Difference between restore RECOVERY and NORECOVERY

Read More on Disaster Recovery

Full Recovery without Log Backups - SQL Server DBA

Creating a Disaster Recovery Plan - SQL Server DBA

Redundancy for SQL Server