TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

Rate This
  • Comments 11

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

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
  • 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:\Backup\SAMPLE_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.

  • 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:\Backup\SAMPLE_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

  • 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?

  • Hello Ink

    Thanks for reading.

    No these are 2 diffirent things.

    NOFORMAT is default backup behavior. This specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. In simple words. If you use default NOFORMAT you can write multiple backups to the same file.

    FORMAT in contrast, if you override the default by specifying FORMAT, this may re-write media header which in turn can make any existing content (say backup sets)  INVALID. Caution: using FORMAT may renders the entire media set unusable

    Varun Dhawan | Database Architect

  • Really cool and simple in explanation.

    Thanks

    Vasanth

    Loving SQL forever.

  • 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

  • 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

  • Thanks for posting.

    It is really Very very very nice article

  • nice details has been provided regarding SQL backup

    soft-engineering.blogspot.com

  • hello,

    i have problem to restore, error message

    "Msg 4305, Level 16, State 1, Line 1

    The log in this backup set begins at LSN 3098000001642200001, which is too recent to apply to the database. An earlier log backup that includes LSN 3098000001640000001 can be restored.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE LOG is terminating abnormally."

  • After completing last restoring (SAMPLE_DB-TLog3), my database "SAMPLE_RESTORED" is still showing SAMPLE_RESTORED(Restoring...), is there any prob with my execution or else ?

Page 1 of 1 (11 items)