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;

	NORECOVERY	NO_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/

MS SQL SERVER – Restore & Recover Database without Tail Backup

Table of Contents
___________________________________________________________________________________________________

Pre-requisites

0. Verify Recovery Model
1. Take Full Database Backup
2. Create Table DBA and Insert some data
3. Take T-LOG Backup
4. Insert into table DBA
5. Take T-LOG Backup
6. Insert into table DBA
7. Take T-LOG Backup
8. Insert into table DBA
9. Take Differential Database Backup
10. Insert into table DBA
11. Capture Table DBA total rows — 10 Rows
12. Take T-Log Backup

Backup Validation

13. List Backup
14. Validate Backup – RESTORE VERIFYONLY
15. Check contents of backup – RESTORE HEADERONLY

Delete Database — Test Simulation – Don’t try in office machines

16. Using SSMS (GUI)
17. Using T-SQL

Restore & Recover Database

18. Restore & Recover Database using SSMS (GUI)
19. Restore Database using T-SQL (WITH NO RECOVERY)
20. Recover Database using T-SQL (WITH RECOVERY)
21. Verify Table DBA total rows – 10 Rows

___________________________________________________________________________________________________

Pre-requisites

0. Verify Recovery Model

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases where name = 'TEST'
GO

1. Take Full Database Backup

BACKUP DATABASE [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak' 
WITH NAME = N'FULL DATABASE BACKUP - TEST',STATS 
GO

2. Create Table DBA and Insert some data

-- create table DBA
CREATE TABLE DBA 
(
NAME	VARCHAR(10),
ROLE	VARCHAR(10)
);
GO
-- Insert data to table 
INSERT INTO DBA VALUES ('SUGI','DBA');
INSERT INTO DBA VALUES ('TEJA','DBA');
INSERT INTO DBA VALUES ('BALA','DBA');
INSERT INTO DBA VALUES ('SURENDAR','DBA');

3. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO

4. Insert into table DBA

INSERT INTO DBA VALUES ('SREERAM','INFRA');

5. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO

6. Insert into table DBA

INSERT INTO DBA VALUES ('PAVAN','HADOOP');

7. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO

8. Insert into table DBA

INSERT INTO DBA VALUES ('SRINIVAS','MW');

9. Take Differential Database Backup

BACKUP DATABASE [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak' 
WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP TEST DATABASE',STATS 
GO

10. Insert into table DBA

INSERT INTO DBA VALUES ('RAVI','DEV');
INSERT INTO DBA VALUES ('RAMESH','DBA');
INSERT INTO DBA VALUES ('SRINIVAS','DBA');

11. Capture Table DBA total rows — 10 Rows

12. Take T-Log Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO

Backup Validation

13. List Backup

-- List all database backups from last week.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date

14. Validate Backup – RESTORE VERIFYONLY

--Verify Full Backup
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'; 
-- Verify Diff Backup
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'; 
-- Verify T-LOG Backup 
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak';

15. Check contents of backup – RESTORE HEADERONLY


--Check Contents - Full Backup
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'; 
--Check Contents - Diff Backup
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'; 
--Check Contents - T-LOG Backup 
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak';

Delete Database — Test Simulation – Don’t try in office machines

16. Using SSMS (GUI)

17. Using T-SQL

Drop database TEST;

Restore & Recover Database

18. Restore & Recover Database using SSMS (GUI)

19. Restore Database using T-SQL (WITH NO RECOVERY)

-- drop database test
DROP DATABASE TEST;





-- Restore Database from FULL DB Backup
RESTORE DATABASE TEST 
FROM DISK = N'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 5
GO



-- Restore Database from LAST DATABASE DIFFERENTIAL BACKUP
RESTORE DATABASE TEST 
FROM DISK = N'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'
WITH
NORECOVERY,
NOUNLOAD,
STATS = 5
GO

20. Recovery Database using T-SQL (WITH RECOVERY)

-- Recover Database using T-LOG Which was taken after LAST DB DEFFERENTAIL Backup 


-- DB Differential Backups always Cumulative (It will take backup from Last Full backup only, Not from Last Cumulative Backup)

No need to use T-LOG FILES 1,2 and 3 for recovery, because we took DB Differential Backup before T-LOG (4th time backup). Hence we need T-LOG File 4 only required for recovery. T-LOG 1,2 and 3 No more required.



RESTORE LOG TEST 
FROM DISK=N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak'
WITH FILE = 4,
RECOVERY,
NOUNLOAD,
STATS = 5
GO

21. Verify Table DBA total rows – 10 Rows

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

BRIGHT DBA

ORACLE DBA – Tips and Techniques

Tag Archives: SQL SERVER RESTORE

Restore Database with Tail-log Backup

Restore & Recover Database with Tail Backup (SQL Server)