If you’ve spent much time playing with SQL Server backups and restores then you’ll have discovered Log Sequence Numbers (LSNs), I’ve discussed before How SQL Server LSNs are generated, and went through how to manually convert a hexadecimal LSN to a numeric value.
As part of some work I was doing on the dbatools module I needed something to convert the Log Sequence Numbers coming back from the transaction log into numeric LSNs so I could use them to query the backup history tables in msdb.
Thinking this might be useful to other people, I’ve chopped out the dbatools specific code, and present the function here:
| function Convert-LSN { | |
| <# | |
| .SYNOPSIS | |
| Converts Lsns betweent Hex and/or numeric formats | |
| .DESCRIPTION | |
| Function takes an LSN in either split Hexadecimal format () or numberic | |
| It then returns both formats in an object | |
| .PARAMETER LSN | |
| The LSN value to be converted | |
| .EXAMPLE | |
| PS C:\ $output = Convert-LSN -LSN 0000014:000000f3:0001 | |
| Will return object $Output with the following value | |
| $Output.HexLSN = 0000014:000000f3:0001 | |
| $Output.NumbericLSN = 20000000024300001 | |
| #> | |
| [CmdletBinding()] | |
| param( | |
| [string]$LSN | |
| ) | |
| if ($LSN -match '^[a-fA-F0-9]{8}:[a-fA-F0-9]{8}:[a-fA-F0-9]{4}$') { | |
| Write-Verbose -Message 'Hexadecimal LSN passed in, converting to numeric' | |
| $sections = $LSN.Split(':') | |
| $sect1 = [System.Convert]::ToInt64($sections[0], 16).ToString() | |
| $sect2 = [System.Convert]::ToInt64($sections[1], 16).ToString().PadLeft(10, '0') | |
| $sect3 = [System.Convert]::ToInt64($sections[2], 16).ToString().PadLeft(5, '0') | |
| $Hexadecimal = $LSN | |
| $Numeric = $sect1 + $sect2 + $sect3 | |
| } elseif ($LSN -match '^[0-9]{15}[0-9]+$') { | |
| Write-Verbose -Message 'Numeric LSN passed in, converting to Hexadecimal' | |
| $sect1 = '{0:x}' -f [System.Convert]::ToString($LSN.Substring(0, $LSN.length-15), 16).PadLeft(8,'0') | |
| $sect2 = '{0:x}' -f [System.Convert]::ToString($LSN.Substring($lsn.length-14, 9), 16).PadLeft(8, '0') | |
| $sect3 = '{0:x}' -f [System.Convert]::ToString($LSN.Substring($lsn.length-5, 5), 16).PadLeft(4, '0') | |
| $Numeric = $LSN | |
| $Hexadecimal = $sect1 + ':' + $sect2 + ':' + $sect3 | |
| } else { | |
| Write-Warning -Message 'LSN passed in is neither Numeric nor in the correct hexadecimal format' | |
| return | |
| } | |
| [PSCustomObject]@{ | |
| Hexadecimal = $Hexadecimal | |
| Numeric = $Numeric | |
| } | |
| } |
Standard PowerShell good practice to include a decent help and examples block
I use a couple of regex expressions to make sure we’re getting what we expect. Both are based off of the Microsoft documentation for the LSN formats. So for the numeric LSN we’re looking for 26 numeric digits, and for the hexadecimal LSN representation we’re after 2 8 character hex numbers and a 4 character one separated by colons (‘:’). If the LSN passed in doesn’t match either of those, we raise a warning as we can’t do anything with it.
For hex to numeric, we split the string on :. Convert each section from hex to int64 (using int64 as LSNs can get really big), then convert to a string and pad with the requisite number of 0s.
For number to hex we work from the back of the LSN as we have to cope with potential leading 0’s.