Could I retrieve the value of the First LSN and the Last LSN?

Copper Contributor

Hello everyone,

 

I'm new to databases and I'm having some minor problems with the SQL server.

 

Let me explain: I'd like to automate a script written in Go that would facilitate the restoration of a database. My script would search for transaction logs in a folder, verify their presence, and then execute them to restore the database.

 

What I don't understand is how SQL Server validates their coherence to apply it in my code. How does SQL Server check that they are in the correct order and that they are all present?

 

When using the graphical interface for a restoration:

Vaultnexus_1-1711638305273.png

 

I've noticed that for the log files, the Last LSN corresponds to the First LSN of the next log file. Therefore, I wonder if it's possible to retrieve and compare them to validate coherence in my code.

 

So, I'm wondering if this is possible.

 

To give a more concrete example, I've created a similar script for the MySQL database, where to check the coherence of the files, I simply looked to see if the next binlog was greater than the current one by +1. For example:

  • binlog.000001, binlog.000002, binlog.000003: coherence is good.
  • binlog.000001, binlog.000003, binlog.000004: coherence is not validated, binlog.000002 is missing.

Thank you for your assistance. :)

1 Reply

@Vaultnexus
To get the First/Last LSN, you use RESTORE HEADERONLY.  You can also get the redo_start_lsn from sys.master_files. You would use this to work what log file is needed for an existing DB.
I created a log shipping tool that can also be used as a database restore tool that might be worth a look.  It's open source so you can see exactly how it works.