Converting a SQL Server Log Sequence Number with PowerShell

As demonstrated in one of my previous blog articles “Determine who deleted SQL Server database records by querying the transaction log with PowerShell“, someone or something has deleted records from a SQL Server database. You’ve used my Find-MrSqlDatabaseChange function to determine when the delete operation occurred based on information contained in either transaction log backups or the active transaction log:


You’re ready to perform point in time recovery of the database to the LSN (Log Sequence Number) just before the delete occurred but the LSN provided from the transaction log is in a different format than what’s required to perform a restore of the database. How can the “Current LSN” as shown in the previous set of results be converted to what’s needed to perform point in time recovery of the database? You could use some cryptic T-SQL code, but a PowerShell function can also be written as a reusable tool and added to your SQL toolkit:

Take a moment to notice how the code in the previous example is formatted. It’s formatted for readability. Your co-workers and future self will thank you. Also notice that it contains a “Requires” statement which states the minimum version of PowerShell that’s required. It includes comment based help so help can be obtained just like it is for any other cmdlet. It uses parameter validation to require that a value is provided and that it’s in a specific format. All of these are standard items that should be included in any PowerShell function <period>.

The conversion returns both the original LSN and the converted LSN:


An even better option is to make PowerShell tools modular so the Convert-MrSqlLogSequenceNumber function can accept the output of my previously created Find-MrSqlDatabaseChange function via pipeline input so the necessary log sequence number can be determined with a single PowerShell one-liner instead of having to run one command and then copy and paste its output as input for the next command:


Maybe there are multiple log sequence numbers that need translating? That functionality is built in as well:


All of the PowerShell functions shown and/or referenced in this blog article can be downloaded from my SQL repository on GitHub.

If you’ve found this blog article interesting and you’re attending the PowerShell and DevOps Global Summit 2016 next week, then you should definitely consider attending my “Building Unconventional SQL Server Tools in PowerShell with Advanced Functions and Script Modules” session on Wednesday afternoon, April 6th.


Leave a Reply

%d bloggers like this: