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