SQL Server Differential Backup – Issues with CheckpointLSN

sql server

I’m trying to get my head around how LSNs and Differential Backups work. I’ve read a few articles on here that explain some things that have helped but there is still a missing gap for me.

I get that a differential backup is slightly different to the logs in that all it holds are the extents that have changed since the last full backup and a tiny bit of log to ensure the data is consistent. No problems, I get that. I’m trying to work out how I identify the subsequent logs that need to be applied after that differential.

Below is a cut-down table of some backup entries. The checkpoint_lsn of the diff and transaction log do not match so that doesn’t seem to be the same, neither do the first and last lsn values. My current thinking is the next log to apply after a diff is one where the last_lsn of the diff is contained with the first_lsn and last_lsn value of the log. Is this correct or have I totally pulled that out of my bottom and got the wrong end of the stick here?
I totally understand lots of people use time stamps on their backup files to do this, yup that can work, but… I just need to understand what is going on and why to help me get a good nights sleep.

DiffLSNquery

Best Answer

The checkpoint_lsn of the diff and transaction log do not match so that doesn’t seem to be the same

Checkpoint_lsn is the LSN of most recect checkpoint respect to given backup (the closest in time checkpoint that occured prior to given backup), so checkpoint_lsns of different backups are not related to each other.

neither do the first and last lsn values

This depends on the type of backup.

While for all the backup types these are the first LSN and the (last LSN - 1)* contained in given backup and seem to be independent of other backups, Last_lsn and First_lsn of log backups are always "in relation":

enter image description here

I.e. every next log backup starts exactly where previous log backup stopped.

* That LSN is not contained in 2 log backups, instead, given current log backup, its last_lsn is not the one contained in this backups, but it's the next lsn respect to the last lsn, contained in this log backup, that's why I wrote that the Last_LSN is Last_lsn - 1.

In other words, it is LSN that will be the starting point of the next log backup and it will be contained in that next log backup.

My current thinking is the next log to apply after a diff is one where the last_lsn of the diff is contained with the first_lsn and last_lsn value of the log. Is this correct

Yes it's correct.

Related Question