SQL Server – Understanding Differential/T-Log LSN

backuprecoverysql servertransaction-log

So, this is now my third question on this topic. I just keep finding more and more than i want to know and understand about how FULL recovery works.

We transitioned a database to FULL yesterday by taking a full backup at 7am. Since then we take T-Log backups every hour and differentials nightly at 12:01am. I'm generating some reports that can be automatically emailed to me (hooray automation!) and im noticing something funny with the LSN numbers. Maybe I'm just not understanding how they work, but here goes.

Picture of query result displaying files, dates, sizes, LSNs, etc since we initiated full recovery yesterday:

enter image description here

I've highlighted the LSNs that are confusing to me and hopefully it makes it easier for you all to read. Here are my questions:

  1. The high LSN for the full backup is '308000000583700001', shouldn't the differential backups [Row 13 in picture] low LSN be that also in order for the log to chain correctly? Am I misunderstanding how the full and differential log files are synced to each other?

  2. You'll notice that the differential, taken at 12:01am has a high LSN of '311000000469900001' but the T-Log backup taken at 1:00am has the exact same high LSN. Is this simply because there was no activity and the T-Log simply 'caught up' to match the LSN of the differential?

I'm racking my brain trying to make sure not only do i know how to implement a recovery strategy like this, but also that I completely understand exactly how the LSNs all sync up when generated.

Thanks again for all your help, as usual, on this one!

Here's the SQL used to pull this result, in case you would like to investigate using your system:

    SELECT TOP 48 CAST(s.database_name as char(10)) AS DB
    ,CAST(' ' as char(1)) + CAST(SUBSTRING(m.physical_device_name, 46, 40) as char(25)) AS FileName
    ,CAST(CAST(s.backup_size / 1000000 AS INT) AS CHAR(4)) AS MBSize
    ,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS CHAR(4)) AS SecsTaken
    ,s.backup_start_date
    ,CAST(s.first_lsn AS CHAR(20)) AS first_lsn
    ,CAST(s.last_lsn AS CHAR(20)) AS last_lsn
    ,CASE s.[type]
        WHEN 'D'
            THEN 'Full'
        WHEN 'I'
            THEN 'Diff'
        WHEN 'L'
            THEN 'TLog'
        END AS BackupType
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    AND backup_start_date > '2014-07-24 07:00:00'
ORDER BY backup_start_date DESC

From @Shankys response, here is what the result set looks like with database_backup_lsn and chockpoint_lsn added to the query. As you can see, the backup_lsn is the same for all, referencing that they are ties to the full backup created yesterday. I hope this information helps anyone else trying to understand how LSNs work!

enter image description here

Best Answer

I will answer you question point wise like you asked

  1. No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the checkpointLSN of full backup and this will show to which full backup this differential backup relates to. Differential backup is also kind of full backup just taking backup of extents which has changed so LSN number might not be same as that of Full backup.

  2. Neither Transaction Log backup nor LSN chain would is affected by Differential backup or Full database backup. Only log backup effects the chain.Proof you can see last LSN of 12 AM trn backup is same as first LSN of 1 AM transaction log backup.

Hope this helps

Related Question