TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

In a recent issue with a customer, I came across a strange SQL belief (or say a DBA Myth) regarding SQL Server Transaction Logs Backup/Restore. Let me explain you what was the scenario and then let’s try attempting to break the myth with quick testing.

SCENERIO:

- Customer is taking FULL and TRANSACTION LOG (T-Log) backup of a critical database

- Backup files are maintained on Disk in sequential order

- Due to a hardware failure, the database got crashed and customer decided to recover from the backups

- Due to an another issue (let’s assume) the most recent FULL backup file is lost and he now only has 1st FULL backup and successive T-Log backups

- Customer is not sure if he will be able to perform point-in-time recovery ß “THE MYTH”

EXPLANATION:

Simple answer to above scenario is: POINT-IN-TIME-RECOVERY can still be performed.

Here’s Why:

A continuous sequence of T-Log backups is tied by a ‘Log Chain’, which starts with a FULL backup. Now, unless we run anything explicitly that breaks the log-chain (Ex., running BACKUP log TRUNCATE_ONLY* or by switching to SIMPLE recovery model), the existing chain remains intact. With the log chain intact, you can restore your database from any FULL database backup in the media set, followed by all subsequent T-Log backups to the point of failure.

TESTING: THE WATERS

To understand the scenario, let’s do some hands-on with BACKUP/RECOVERY using FULL and T-LOG backups

=====================================================================

--CREATE A NEW DATABASE “SAMPLLE_DB” IN FULL RECOVERY MODEL

=====================================================================

Use Master

GO

CREATE DATABASE [SAMPLE_DB] ON PRIMARY

( NAME = N'SAMPLE_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_DB.mdf' ,

SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'SAMPLE_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_DB_log.ldf' ,

SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'SAMPLE_DB', @new_cmptlevel=90

GO

ALTER DATABASE [SAMPLE_DB] SET RECOVERY FULL

GO

=====================================================================

=====================================================================

--PERFORM MULTIPLE BACKUPS (FULL and T-LOG)

=====================================================================

--Take a FULL database backup (This is a starting point of the backup set and is mandatory to have before you can initiate T-Log backup)

BACKUP DATABASE [SAMPLE_DB] TO DISK = N'D:\Backup\DB_FULL.bak'

WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Create a TEST table

Use [SAMPLE_DB]

GO

CREATE TABLE dbo.Table_1

       (

       Name varchar(50) NULL

       ) ON [PRIMARY]

GO

--Insert a 1st Row in TEST table

Insert into dbo.Table_1 values ('Rob')

GO

--Take 1st T-LOG backup.

BACKUP LOG [SAMPLE_DB] TO DISK = N'D:\Backup\SAMPLE_DB-TLog1.trn'

WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Insert a 2nd Row in TEST table

Insert into dbo.Table_1 values ('Bob')

GO

--Take 2nd T-LOG backup.

BACKUP LOG [SAMPLE_DB] TO DISK = N'D:\Backup\SAMPLE_DB-TLog2.trn'

WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Insert a 3rd Row in TEST table

Insert into dbo.Table_1 values ('Marry')

GO

--Take 2nd FULL database backup

BACKUP DATABASE [SAMPLE_DB] TO DISK = N'D:\Backup\DB_FULL_2.bak'

WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Insert a 4th Row in TEST table

Insert into dbo.Table_1 values ('Suzanne')

GO

--Take 3rd T-LOG backup (Note: This we are taking after 2nd FULL BACKUP)

BACKUP LOG [SAMPLE_DB] TO DISK = N'D:\Backup\SAMPLE_DB-TLog3.trn'

WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Just a quick check on number of rows in table dbo.Table_1

Use SAMPLE_DB

GO

Select * from dbo.Table_1

GO

Name

--------------------------------------------------

Rob

Bob

Marry

Suzanne

(4 row(s) affected)

=====================================================================

--RESTORE MULTIPLE BACKUPS (HEADER ONLY) TO VERIFY THE Log Chain

=====================================================================

--RESTORE 1ST FULL database backup with HEADERONLY

RESTORE HEADERONLY FROM DISK = 'D:\Backup\DB_FULL.bak'

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BeginsLogChain

--------------------------------------- --------------------------------------- --------------------------------------- ----------------- --------------

20000000006300037 20000000008100001 20000000006300037 0 0

For 1st FULL Backup, FirstLSN is 20000000006300037 and LastLSN is 20000000008100001. Also BeginsLogChain is 0. This is staring Log Chain

--RESTORE 1st T-Log backup with HEADERONLY

RESTORE HEADERONLY FROM DISK = 'D:\Backup\SAMPLE_DB-TLog1.trn'

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BeginsLogChain

--------------------------------------- --------------------------------------- --------------------------------------- ----------------- --------------

20000000006300037 20000000009900001 20000000006300037 20000000006300037 1

For 2nd T-LOG Backup, FirstLSN is 20000000006300037, LastLSN is 20000000009900001. BeginsLogChain bit is 1, which indicates that this is first in log chain

--RESTORE 2st T-Log backup with HEADERONLY

RESTORE HEADERONLY FROM DISK = 'D:\Backup\SAMPLE_DB-TLog2.trn'

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BeginsLogChain

--------------------------------------- --------------------------------------- --------------------------------------- ----------------- --------------

20000000009900001 20000000010000001 20000000006300037 20000000006300037 0

For 2nd T-LOG Backup, FirstLSN is 20000000009900001, which is LastLSN for 1st T-Log backup and can be applied over 1nd T-lOG backup

-- RESTORE 2nd FULL Backup with HEADERONLY

RESTORE HEADERONLY FROM DISK = 'D:\Backup\DB_FULL_2.bak'

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BeginsLogChain

--------------------------------------- --------------------------------------- --------------------------------------- ----------------- --------------

20000000010300158 20000000016800001 20000000010300158 20000000006300037 0

For 2nd FULL Backup, FirstLSN is 20000000010300158. This FULL backup is not breaking LSN chain

--RESTORE 3rd T-Log backup with HEADERONLY

RESTORE HEADERONLY FROM DISK = 'D:\Backup\SAMPLE_DB-TLog3.trn'

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BeginsLogChain

--------------------------------------- --------------------------------------- --------------------------------------- ----------------- --------------

20000000010000001 20000000017500001 20000000010300158 20000000010300158 0

For 3rd T-LOG Backup, FirstLSN is 20000000010000001 which is LastLSN for 2nd T-LOG backup and can be applied over 2nd T-lOG backup.

=====================================================================

--RESTORE MULTIPLE BACKUPS (THIS TIME DOING IT ACTUALLY!!)

=====================================================================

-- Restore 1st FULL Backup with NORECOVERY

RESTORE DATABASE [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\DB_FULL.bak'

WITH  FILE = 1,

MOVE N'SAMPLE_DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_RESTORED.mdf',

MOVE N'SAMPLE_DB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_RESTORED_1.ldf',

NORECOVERY,  NOUNLOAD, STATS = 10

GO

-- Restore 1st T-Log Backup with NORECOVERY

RESTORE LOG [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\SAMPLE_DB-TLog1.trn'

WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

-- Restore 2nd T-Log Backup with NORECOVERY

RESTORE LOG [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\SAMPLE_DB-TLog2.trn'

WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

-- NOTE: Here, I'M SKIPPING THE 2nd FULL BACKUP HERE

-- Restore 3rd T-Log Backup with RECOVERY (as this is the last T-Log backup in the chain)

RESTORE LOG [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\SAMPLE_DB-TLog3.trn'

WITH  FILE = 1, RECOVERY, NOUNLOAD, STATS = 10

GO

--- Moment of Truth, Lets check the number of rows in TEST table

Use SAMPLE_RESTORED

GO

Select * from dbo.Table_1

GO

Name

--------------------------------------------------

Rob

Bob

Marry

Suzanne

(4 row(s) affected)

Conclusion: Myth Busted!!

You can always restore a database by applying T-LOG backups in sequence, If there are NO gaps in the Log-Chain, irrespective of any intermediate FULL or DIFFRENTIAL backups.

Next Step:

- Go try this yourself

- Recommended read @ SQL BOL: Working with Transaction Log Backups and http://www.sqlskills.com/blogs/paul/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx

* This option is removed from SQL Server 2008 onwards

Hope you'll enjoying demystifying this one along with me.

Varun Dhawan

Support Engineer, Microsoft SQL Server PSS

Reviewed by

Nickson Dicson, Shamik Ghosh

TL, Microsoft SQL Server PSS