Understanding SQL
LSN & Backups (Part 1)
Why?
LSN (LOG SEQUENCE NUMBER) is
very simple sequence that exists in SQL database which helps to identify &
maintain the logs of any database. This is most easily misunderstood while
relating this with backup types & their conditions. So let’s see how this
LSN plays a major role in restore of any database from backup files.
How?
I am going to make use of
Backup_explanation database & I am going to take Full, Differential,
Copy-only Full backup & transactional log backups of the same by creating
some sample tables between each backups with following script.
-- Create a database
Create database Backup_Explanation
--Changing the recovery model to FUll
Alter database Backup_Explanation
set Recovery='FULL'
--Query to create table and create Backups
Use Backup_Explanation
Create Table Table1(i int)
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_Full.bak'
with init-- Till Table1
Create Table Table2(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log1.trn' with init -- Till Table2
Create Table Table3(i int)
-- Running a Checkpoint, this will reset Checkpoint_LSN
Checkpoint
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log2.trn' with init -- Till Table3
Create Table Table4(i int)
-- Running a differential backup, this will also reset
Checkpoint_LSN
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_diff.diff'
with init,differential -- Till Table4
Create Table Table5(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log3.trn' with init -- Till Table5
Create Table Table6(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log4.trn' with init -- Till Table6
Create Table Table7(i int)
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_diff2.diff'
with init,differential -- Till Table7
Create Table Table8(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log5.trn' with init -- Till Table8
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_diff3.diff'
with init,differential -- Till Table8
Create Table Table9(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log6.trn' with init -- Till Table9
Create Table Table10(i int)
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_copy_only.bak'
with init,copy_only-- Till Table10
Create Table Table11(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log7.trn' with init -- Till Table11
Create Table Table12(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log8.trn' with init -- Till Table12
Create Table Table13(i int)
Backup database Backup_explanation
to disk='D:\Backups\Backup_Explantion_Full2.bak'
with init-- Till Table13
Create Table Table14(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log9.trn' with init -- Till Table14
Create Table Table15(i int)
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log10.trn' with init -- Till Table15
Checkpoint
Backup log Backup_explanation to
disk='D:\Backups\Backup_Explantion_log11.trn' with init -- Till Table15
Now I
had created required backups for our Backup_Explanation database. We will see
how LSNs helps in point in time recovery. I will use following script to pull
required data for our analysis from join of 2 system tables namely
è
Msdb..backupset
è
Msdb..backupmediafamily.
Rules Set:
It will be easy for you to analyse the
result of this query if you keep following things as your rule set. Let’s
begin,
·
Transactional
log LSN will never get affected by any type of backup, which means neither full
backups nor differential backups will affect LSN flow.
·
Whenever
a full backup, differential backup runs a checkpoint will run by default.
·
Always the Checkpoint LSN & First LSN
of Full Backup will be same.
·
Always the Checkpoint LSN & First LSN
of a Differential Backup will be same.
·
The Very first Log backup taken on any
FULL /BULK Logged recovery model database will have its First, Last, Checkpoint
LSNs similar to its Full backup.
Points to be known:
First_LSN :
The starting point LSN of any backup.
Last_LSN :
The Ending point LSN of any backup, note that this will be starting
of following First_LSN of next Transactional Log backup.
Checkpoint_LSN:
A
Checkpoint LSN will get reset whenever a Checkpoint runs on the database, in
other words, this denotes the LSN when the last checkpoint had run.
Database_backup_lsn:
This always denotes the Last full
backup’s Checkpoint LSN. This helps the particular log/differential backup to
get bifurcated beneath nearest successful full backup in hierarchy. This
applies for all types of backups except the very FIRST FULL BACKUP taken on the
database.
The
very FIRST Full backup taken on the database always have its
database_backup_lsn as 0. Since it don’t have any backup to be placed higher
than it on the hierarchy. This will serve as root for all other backups which
follow it.
Differential_base_lsn:
This always denotes the Last full backup’s
Checkpoint LSN. This helps the particular backup to get bifurcated beneath that
particular full backup in hierarchy. The Differential_base_lsn will be
generated only for Differential backups of any database.
Analysing
result of the Query:
I had attached the complete XL file which we are
analysing at the end of this blog. We will analyze portion by portion of the
same now.
You may see that
green rows mention full backups, all blue rows for log backups & a grey row
for copy_only full backup.
See
the very FIRST FULL backup (FULL1) & following very first Log backup (LOG1)
have their
First_LSN =Checkpoint_LSN=
57000000042400100 & its
database_backup_lsn =0.
As
we had created Table1 & Table2 before taking Full1 full backup & Log1
log backup respectively you may see their Last LSN’s aren’t same.
A Deep dive into
Checkpoint_LSNs :
è
We have created another table now & ran a
checkpoint before running a log backup Log2.This in turn reset the Value of
Checkpoint_LSN of Log2.
Thus there is a change in Check point
LSN of Log2.
Similarly
when we take a Differential backup or Full
the Checkpoint_LSN will get reset & it will be remain same until
next run of checkpoint.
You
may see the Checkpoint_LSN got reset at Diff2, Diff3, Copy_only_Full &
Full2.
Thus,
Checkpoint_LSN of Diff1= Checkpoint_LSN
of Log3 = Checkpoint_LSN of Log4.
Checkpoint_LSN of Diff2= Checkpoint_LSN
of Log5
Checkpoint_LSN of Diff3= Checkpoint_LSN
of Log6
Checkpoint_LSN of Copy_Only_Full=
Checkpoint_LSN of Log7 = Checkpoint_LSN of Log8.
Checkpoint_LSN of Full2= Checkpoint_LSN
of Log9 = Checkpoint_LSN of Log10.
Since we ran a checkpoint again
the Checkpoint_LSN of Log10 differs from Log11.
A Deep dive into database_backup_LSNs
:
As per the explanation discussed above the
database_backup LSN shows the checkpoint_LSN value of its last FULL backup,
this helps us to identify the nearest root backup of it’s hierarchy.
Thus,
Database_backup_LSN all differential
(diff1, diff2 & diff3) & Log backups
(Log1,Log2,Log3,Log4,Log5,Log6,Log7,Log8) will be equal to Checkpoint_LSN of
Full1 (57000000042400100)
Once the next FULL backup (FULL 2)
happened, the following backups will have different Database_backup_LSN as
(59000000012000100) abide the rule.
A deep dive into
copy_only backup:
A copy only full backup is
used to take a full backup of any database without affecting database_backup_LSN chain. You may see
that Database_backup_lsn of Copy_only_full
= Checkpoint_LSN of FULL1 , which
means this backup doesn’t resets or breaks
the database_Backup_LSN which is most important for a Differential
backup to be restored on top of its respective LAST successful Full backup.
A deep dive into Log
backups’ LSNs:
Last_LSN
of Log 1= First_LSN of Log2
Last_LSN
of Log2= First_LSN of Log3
Last_LSN
of Log3= First_LSN of Log4
.
.
.
.
Last_LSN
of Log10=First_LSN of Log11.
This
clearly shows neither Full backup nor a Differential backup that had broken the
LSN chain of log backups, which means log backup LSN is always continuous with
its log once it is initiated from its FULL Backup of a FULL/ BULK Logged
recovery model database until its recovery model is changed.
NOTE:
This clearly shows that invariably of
any latest full backup we have we can restore consecutive LOG backups to get
any database point in time recovery. We will see this through SQL Query window
in my next blog. J
CheersJ.
To be continued …
VSG
SQL DBA
No comments:
Post a Comment