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.