TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

Article
08/26/2010

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

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%)

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

ALTER DATABASE [SAMPLE_DB] SET RECOVERY FULL

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

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

--Create a TEST table

Use [SAMPLE_DB]

CREATE TABLE dbo.Table_1

(

Name varchar(50) NULL

) ON [PRIMARY]

--Insert a 1st Row in TEST table

Insert into dbo.Table_1 values ('Rob')

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

--Insert a 2nd Row in TEST table

Insert into dbo.Table_1 values ('Bob')

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

--Insert a 3rd Row in TEST table

Insert into dbo.Table_1 values ('Marry')

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

--Insert a 4th Row in TEST table

Insert into dbo.Table_1 values ('Suzanne')

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

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

Use SAMPLE_DB

Select * from dbo.Table_1

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'