Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Backing up the tail log

28 April,2015 by Tom Collins

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


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Backing up the tail log | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer