14 March,2021 by Tom Collins
I was talking to an Auditor recently - who specialises in large Corporate Audits - and they asked me how would I prove a certain database which is backed up is actually restored to another server. One of the methods I described was using the Log Sequence Numbers (LSN).
When a database is backed up , the First and Last LSN numbers are recorded, and when a database is restores First and Last LSN mumbers are recorded.
The Auditor wanted to understand what does the LSN mean? When a record is created in the SQL Server transaction log a new LSN is generated. Importantly , LSNs are ordered , meaning it is possible to compare one LSN with another i.e was it before or after.
This sequencing aspect of LSN are used in the database restore sequence. The sequencing of the log chain is critical - and it is up to the DBA to understand what events can break the Log Chain. Read more on The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth for consequencecs if log chain is unavailable
For example - when the DBA changes the Recovery Model from FULL to SIMPLE - the log chain is broken - and a new FULL backup is required to set up a new chain
LSNs are exposed through various system tables and functions.
Read more on Transaction Logs
Read sql transaction Logs with ::fn_dblog
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |