#31. Understanding SQL LSN & Backups-Restore (Part-2)

 Understanding SQL LSN & Backups-Restore (Part-2)

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 backup & restore of any database from backup files. As this is a continuation from last post, I would request you to view my last post , which will help you to understand those backup files I refer below.

How?

            We will create a new database Restore_Explanation from those backups we already have for Backup_Explanation database.

Scenario1:  Importance of Transactional Logbackup LSN.

NOTE:

            I had used “MOVE… TO…” option while restoring to move the data files to different location. It may throw an error if I haven’t mentioned it while restoring “Restore_Explanation” DB from backups of “Backup_Explanation” ,though the logical names of the files are different the Physical Names of these files will be same as “ Backup_Explanation.mdf “ & “Backup_Explanation_log.ldf”. So, SQL will not allow you to restore our new database successfully.

            Also you should restore all backups with “NO RECOVERY” option except last Transactional Log backup to keep your database in recovery. Once all these are restored we will restore last needy Transactional log backup with recovery to get the database up to that point.

Let’s begin,

            When we try to restore a backup from backup_Explanation_Full.bak & Backup_Explantion_log2.trn by leaving log1.trn, It throws an error message stating mismatch of LSN in log backups which you are trying to restore.      

           

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

           

The error message in red clearly shows that SQL server is unable to restore log2 on top of full backup we have. It will suggest you to go for earlier log backup. This is because the First_lsn & Last_lsn of transactional log backup is not met as expected.

I will uncomment my query before restore command of backup_Explanation_log1.trn & I will rerun it. You may see the restore goes fine & a new database called Restore_Explanation got created in my SQL Instance with all the required 3 tables on it. 

            Thus we need to ensure that we restore correct Log backups in Sequence of their LSN to achieve successful point in time restore of any database.

Scenario2:  Importance of Differential Backup :

                Now I wish to restore the same database Restore_Explanation till ‘Table 8’ from the backups of Backup_Explanation database. To do so, I may follow 2 type of restores like

Note:

            I had used option “REPLACE” in this scenarioto restore Replace_Explanation database from backups of Backup_Explanation, as I am going to overwrite an existing database with same logical names & physical names for files & keep them on same location where it is currently located.

Type1:  Restoring First Full backup & followed by all transactional log backups till log5 whose database_backup_LSN  is equal to Checkpoint_LSN of the Full backup. Thus to accomplish this task we may need following backups to be restored.

Full1

Log1

Log2

Log3

Log4

Log5

Type2: Restoring First Full backup, followed by Diff2 Differential backup with NO Recovery option, followed by log5 with recovery option. As Differential backups are equal to cumulative backups of all Log backups before Checkpoint_LSN of that particular Differential backup since Last Full backup made. So we need the following files to complete our restore.

Full1

Diff2

Log5

            On analysing Type 1 option will become tedious & tough if we have more number of Log backups to be restored. Also we should keep track of Transactional log backup LSNs also. But   on Type2 We just want to restore only 3 files& it will be easier for our future analysis & it is easy for remembering & to follow as well. SO I prefer to go with Type 2 restore for this scenario.

            On the message you could see the restore got completed & there are 8 tables now presented on Restore_Explanation database.

NOTE:

Notice that first highlight shows the full backup got restored, which will restore both MDF & LDF files & leave the database in no recovery. The second highlighted portion shows the differential backup got restored, which again will add cumulative pages since last full backup for the database on its respective MDF & LDF files & will leave the database in no recovery as expected.

            Finally Notice on restoring log will restore its updates to the database LDF but not to MDF. This difference is because the checkpoint would have run only during FULL Backups & Differential Backups. Thus while restoring any FULL or Differential backups the Restore command will check for the CHECKPOINT_LSN created for that backup file.

Scenario 3:  Restoring a Copy-only Full backup:   

            Restore of a database from a copy only backup is very similar to restore a database from a full backup. The advantage it has is that all those differential backups & log backups happen after this copy only full backup will also have their database_backup_LSN equal to their last orginal Full backup’s checkpoint_LSN. Thus copy_only backup helps with a full backup which doesn’t reset the database_Backup_LSN of following differential & Tlog backups.

Scenario 4 : A Missing FUL Backup – Help of LSNs:

            Now let’s play with a Missing backup situation, which would have been a surprise to you J . Now If I need to restore the copy of Backup_Explanation database till table 15 to Restore_Explanation datbase, the easiest option will be like to restore full backup (Backup_Explanation_FULL2.bak) & to restore backup_explanation_log9.trn on top of it.

But due to some issue I had deleted my full backup Backup_Explanation_FULL2.bak unknowingly . & we need to recovery our database !!!

            I am going to retrieve my database from the last copy_only full backup as it has most of the data I need, followed by all transactional log backups. Skipping the full backup in between them.

Type	first_lsn	Last_lsn	checkpoint_lsn	database_backup_lsn
Copy_only_FULL	59000000001600000	59000000005600000	59000000001600000	57000000042400100
Log7	58000000045600000	59000000006400000	59000000001600000	57000000042400100
Log8	59000000006400000	59000000008800000	59000000001600000	57000000042400100
Full2	59000000012000100	59000000020000000	59000000012000100	57000000042400100
Log9	59000000008800000	59000000023200000	59000000012000100	59000000012000100
Log10	59000000023200000	59000000024800000	59000000012000100	59000000012000100
Log11	59000000024800000	59000000033600000	59000000025600100	59000000012000100

            With help of following query we will restore the database backups as mentioned.

            The result of the query will be,

Thus we can see all 15 tables got restored to Restore_Explanation database. Even though we don’t have a last successful full backup or differential backup we still able to restore a database to point in time only with help a full backup or Copy_only full backup  before LAST FULL BACKUP (FULL2) & followed by all Transactional Log backups.

             This was possible only because the FULL backup (FULL2) will only change the Database_Backup_LSN of following transactional log backups, which will help only to refer a recent full backup that will help easy restore of those following logs.

In our case ( Log9, Log10, Log 11) Database_backup_LSNs [59000000012000100]  will be the checkpoint_LSN of Full2 full backup[59000000012000100].

 But this full backup will not break the Transactional Log LSN by any chance. Which means Last_lsn of log8 [59000000008800000] will be equal to First_lsn of log9 [59000000008800000], though they have a full backup (FULL2) between them & their respective Database_backup_LSNs may differJ J.

Over all scenarios:

   I had attached whole script I had used for explaining scenarios fyr.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE [master]

--Senario1--

RESTORE DATABASE [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH  FILE = 1,  

MOVE N'Backup_Explanation' TO N'c:\MSSQL2012\SQL_TFS\MSSQL11.SQL_TFS\MSSQL\DATA\RESTORE_EXPLANATION\Backup_Explanation.mdf',  

MOVE N'Backup_Explanation_log' TO N'c:\MSSQL2012\SQL_TFS\MSSQL11.SQL_TFS\MSSQL\DATA\RESTORE_EXPLANATION\Backup_Explanation_log.ldf', 

 NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log1.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 -- comment & uncomment

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log2.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

--Senario2--

--Type1--

USE [master]

RESTORE DATABASE [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log1.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log2.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log3.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log4.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log5.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log6.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log7.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log8.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

--Senario2--

--Type2--

USE [master]

RESTORE DATABASE [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE DATABASE [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_diff2.diff' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Restore_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log5.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

--Scenario3 --

RESTORE DATABASE [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_copy_only.bak' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5 

--Scenario4 --

USE [master]

RESTORE DATABASE [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explantion_copy_only.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5 

RESTORE LOG [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log7.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log8.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log9.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log10.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [Backup_Explanation] FROM  DISK = N'D:\Backups\Backup_Explanation_Log11.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

Thanks for coming till this point!!
VSG
SQL DBA

#30.Understanding SQL LSN & Backups (Part 1)

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 …

               

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

Thanks for coming till this point!!
VSG
SQL DBA