Wednesday, 10 October 2012

Behaviour of log chain with Full backup

Will the Log chain break when we take a full backup of a database?
As per Microsoft documents, A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. (
Let see some exercise to verify this,
Exercise 1:
--Create Database
--Now i create a Full Backup 1, This the intial backup after DB creation
BACKUP DATABASE PowerShellTest TO DISK = 'F:\PowerShellTest1.bak'
WITH NAME = 'Full Backup 1',STATS = 1
--Lets Query MSDB backupset to see the backup details
SELECT * from msdb..backupset where database_name = 'PowerShellTest'
--create logged operation
( col1 int)
--Now start Log chain , Log Backup 1
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log1.trn'
WITH NAME = 'Log Backup 1',STATS = 1
--Query MSDB backupset again to see the backup details
SELECT * from msdb..backupset where database_name = 'PowerShellTest'
--continue logged operation
( col2 int)
--continue Log chain,Log Backup 2
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log2.trn'
WITH NAME = 'Log Backup 2',STATS = 1
--Now I am taking a Full backup Full Backup 2, This is the 2nd Full backup after DB creation and if full backup breaks the log chain it should happen here.
BACKUP DATABASE PowerShellTest TO DISK = 'F:\PowerShellTest2.bak'
WITH NAME = 'Full Backup 2', STATS = 1
--continue logged operation
( col3 int)
--continue Log chain, as per MSDN records this should still point to Full Backup 1:Log Backup 3
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log3.trn'
WITH NAME = 'Log Backup 3',STATS = 1
--Query DB
SELECT * FROM sys.objects where type = 'U' -- 3 Rows showing Tbls TEST,TEST1 and TEST2
--drop Database to recover
--Now I am Restoring the DB using Backup 1 in NORECOVERY mode
RESTORE DATABASE PowerShellTest FROM DISK = 'F:\PowerShellTest1.bak'
--If the Log backup chanin is not broken by the FULL Backup 2 then we should be able to Restore Log 1,2,3 to prove Backup 2 did not break Log chain, Let's try
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log1.trn' WITH NORECOVERY,STATS = 1
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log2.trn' WITH NORECOVERY,STATS = 1
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log3.trn' WITH RECOVERY,STATS = 1
And Yes I was able to recover the database successfully from Full backup 1 and Log 1,2 and 3 proving the Log Chain was not broken by the subsequent FUll backups!!!!
--Query DB
SELECT * FROM sys.objects where type = 'U' 3 Rows showing Tbls TEST,TEST1 and TEST2 proving the DB was successfully restored.
As per MSDN: To identify the backup that starts the log chain in a set of backups, query the begins_log_chain column of the backupset table, or run RESTORE HEADERONLY on the backup device to see the BeginsLogChain column in the results set. So I ran the below TSQL,
SELECT name,database_name,begins_log_chain,type from msdb..backupset where database_name = 'PowerShellTest'

Full Backup 1PowerShellTest0D
Log Backup 1PowerShellTest1L
Log Backup 2PowerShellTest0L
Full Backup 2PowerShellTest0D
Log Backup 3PowerShellTest0L

As expected Log backup chain starts from Log Backup 1.
Exercise 2:
Lets see how log chains are broken,
I follow the same steps as in Exercise 1 till Log backup 1 is taken,
--Create Database
--Full Backup 1
BACKUP DATABASE PowerShellTest1 TO DISK = 'F:\PowerShellTest1_FULL1.bak'
WITH NAME = 'Full Backup X1',STATS = 1
--backup set1
SELECT * from msdb..backupset where database_name = 'PowerShellTest1'
--create logged operation
( col1 int)
--Log chain start, Log Backup 1
BACKUP LOG PowerShellTest1 TO DISK = 'F:\PowerShellTest1_log1.trn'
WITH NAME = 'Log Backup X1',STATS = 1
At this point i change the recovery model of the database,
--Change Recovery Model
--Now Again I take a Full Backup 2
BACKUP DATABASE PowerShellTest1 TO DISK = 'F:\PowerShellTest1_FULL2.bak'
WITH NAME = 'Full Backup X2',STATS = 1
--continue logged operation
( col2 int)
--Now I take a Log backup 2, As per MSDN the log chain should have been broken with Recovery model change and this log backup should be start of new log chain
BACKUP LOG PowerShellTest1 TO DISK = 'F:\PowerShellTest1_log2.trn'
WITH NAME = 'Log Backup X2',STATS = 1
--Now Lets verify the backupset to see in which backups Log chain begins,
SELECT name,database_name,begins_log_chain,type from msdb..backupset where database_name = 'PowerShellTest1'

Full Backup X1PowerShellTest10D
Log Backup X1PowerShellTest11L
Full Backup X2PowerShellTest10D
Log Backup X2PowerShellTest11L

There You GO ! The Log backup taken after recovery model change shows the Log chain is reinitialized again, hope the blog was useful.

No comments:

Post a Comment