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:
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:
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?
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!