Share via


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 https://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

Comments

  • Anonymous
    September 04, 2010
    Thank you for giving a good explanation but we will get confused by this following statement. -- Restore 3rd T-Log Backup with NORECOVERY RESTORE LOG [SAMPLE_RESTORED] FROM  DISK = N'D:BackupSAMPLE_DB-TLog3.trn' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 10 GO Which you said NORECOVERY in the top but used RECOVERY. Just FYI. Thank you.

  • Anonymous
    September 09, 2010
    Hello Sree, Thanks for pointing this out. Yes, In the this step, I trying to restore T-log with RECOVERY. I have made the change accordingly.                -- 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:BackupSAMPLE_DB-TLog3.trn' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 10 GO Appreciate your suggestion. Thanks and regards, Varun Dhawan Support Engineer, Microsoft SQL Server PSS

  • Anonymous
    June 27, 2011
    Hi, thanks for the post.. I see you're using 'WITH NOFORMAT' option. Would it be the same if I use 'WITH FORMAT' option for all backups?

  • Anonymous
    June 30, 2011
    The comment has been removed

  • Anonymous
    June 30, 2011
    Really cool and simple in explanation. Thanks Vasanth Loving SQL forever.

  • Anonymous
    February 10, 2012
    thx for sharing... i confused when your step 3 : Restore 3rd T-Log Backup with RECOVERY i can used my data but how to restore 4rd T-Log Backup Thank you

  • Anonymous
    August 09, 2012
    Arkan, As you've noticed, that my LAST T-Log backup is 'SAMPLE_DB-TLog3.trn' and that I haven't taken another T-log backup beyond that. So, during restore operation, If i need to have my data recovered till 'that-specific-point-in-time of last T-log', I need to restore that last in the log chain with RECOVERY option. Does this answers your query. Varun

  • Anonymous
    November 15, 2012
    Thanks for posting. It is really Very very very nice article

  • Anonymous
    January 17, 2013
    nice details has been provided regarding SQL backup soft-engineering.blogspot.com

  • Anonymous
    February 19, 2014
    The comment has been removed

  • Anonymous
    March 07, 2014
    The comment has been removed

  • Anonymous
    June 29, 2015
    Hi Priya Gautam, I Guess you haven't restored your last T-log file with recovery.