Restore Database with Tail-log Backup

Restore & Recover Database with Tail Backup (SQL Server)

Table of Contents

0. What is a Tail-Log Backup?
1. Create Test Database
2. Create Table & Insert Data
3. Take FULL Backup
4. Insert More Data (Simulate Activity)
5. Take LOG Backup
6. More Transactions
7. Simulate Failure
8. Take Tail-Log Backup (Final Log Backup)
9. Listdown All Backup files
10. Restore FULL Backup
11. Restore LOG Backup
12. Restore TAIL Backup (Final Restore – Last step)
13. Verify Data


0. What is a Tail-Log Backup?

Definition: A tail-log backup captures the “tail” of the transaction log — the portion of log records that have not yet been backed up.

Purpose: It ensures no data loss and maintains the log chain for point-in-time recovery.

Usage: Taken before restoring a database, especially after failure or corruption, to secure the most recent changes.
BACKUP LOG [DatabaseName] TO DISK = 'Path\TailLog.trn' WITH NO_TRUNCATE; (If DB corrupted/Offline/Recovery Pending)

Migration: When moving a database to another server, the tail-log backup ensures no data is lost during the cutover, if planned then use BACKUP LOG [DatabaseName] TO DISK = 'Path\TailLog.trn' WITH NORECOVERY; 

Requirement: The log file (.ldf) must be available, if it’s missing or corrupted, a tail-log backup isn’t possible.


1. Create Test Database

CREATE DATABASE TEST;
GO

ALTER DATABASE TEST SET RECOVERY FULL;
GO


2. Create Table & Insert Data

USE TEST;
GO

CREATE TABLE emp (
    id INT,
    name VARCHAR(50)
);

INSERT INTO emp VALUES (1, 'SUGI');
GO


3. Take FULL Backup

BACKUP DATABASE TEST TO DISK = 'K:\BACKUP\TEST\Test_full.bak';


4. Insert More Data (Simulate Activity)

INSERT INTO emp VALUES (2, 'TEJA');
GO


5. Take LOG Backup

BACKUP LOG TEST TO DISK = 'K:\BACKUP\TEST\Test_log1.trn';


6. More Transactions

INSERT INTO emp VALUES (3, 'JANA');
GO


7. Simulate Failure

DISCLAIMER: The commands given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by these commands.

Now assume: Database issue / corruption / accidental restore requirement

Before starting a restore, you SHOULD take a tail-log backup (final log backup)

1. Insert Data
2. Kill SQL Server
3. Rename or Move Data File (MDF)
4. Start SQL Server
5. Database won't be accesable.
6. Take Tail-log backup (Final backup)
7. Restore Full Backup with NORECOVERY
8. Restore Log backups (All Log backups) with NORECOVERY;
9. Restore (Final restore) Tail-log backup with RECOVERY;
10. Database will come Online.
-- Generate activity

INSERT INTO emp VALUES (4, 'SOMU');
GO

-- Remove MDF (OS level)
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('TEST');

Get-Service -Name 'MSSQLSERVER'

Stop-Service -Name 'MSSQLSERVER' -Force

Move-Item -Path "D:\MSSQL16.MSSQLSERVER\MSSQL\Data\TEST.mdf" -Destination "T:\SQLDATA\TEST.mdf"

Test-Path "T:\SQLDATA\TEST.mdf"
Test-Path "D:\MSSQL16.MSSQLSERVER\MSSQL\Data\TEST.mdf"

Start-Service -Name 'MSSQLSERVER'
Get-Service -Name 'MSSQLSERVER'


8. Take Tail-Log Backup

-- IF Database accesable  
BACKUP LOG TEST TO DISK = 'K:\BACKUP\TEST\Test_tail.trn' WITH NORECOVERY;

-- If DB is damaged (DB in SUSPECT Mode/Offline/Recovery Pending)
BACKUP LOG TEST TO DISK = 'K:\BACKUP\TEST\Test_tail.trn' WITH NO_TRUNCATE;
NORECOVERYNO_TRUNCATE
Difference
  • Puts the database into restoring state.
  • Prevents further transactions.
  • Ensures no more changes happen after backup starts, until restore completes.
  • Typically used when DB is still accessible/online.
  • Use NORECOVERY when you are planning a restore and want to freeze the database.
  • Allows log backup even if database is damaged/offline.
  • Does NOT require database to be online.
  • Skips normal checks and forces log capture.
  • Log file must be accesable, else log backup will fail.


9. Listdown All Backup files

Full Backup file: K:\BACKUP\TEST\Test_full.bak
Log Backup file 1: K:\BACKUP\TEST\Test_log1.trn


10. Restore FULL Backup

RESTORE DATABASE TEST FROM DISK = 'K:\BACKUP\TEST\Test_full.bak' WITH NORECOVERY;


11. Restore LOG Backup

RESTORE LOG TEST FROM DISK = 'K:\BACKUP\TEST\Test_log1.trn' WITH NORECOVERY;


12. Restore TAIL Backup (Final Step)

RESTORE LOG TEST FROM DISK = 'K:\BACKUP\TEST\Test_tail.trn' WITH RECOVERY;


13. Verify Data

USE TEST;
GO
SELECT * FROM emp;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/