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:

1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/28/2016 14:55 PM')

convert-lsn1a.png

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:

 1#Requires -Version 3.0
 2function Convert-MrSqlLogSequenceNumber {
 3
 4<#
 5.SYNOPSIS
 6    Converts a SQL LSN (log sequence number) from a three part hexadecimal number
 7    format to a decimal based string format.
 8
 9.DESCRIPTION
10    Convert-MrSqlLogSequenceNumber is an advanced PowerShell function that converts
11    one or more SQL transaction log sequence numbers from a three part hexadecimal
12    format that is obtained from querying a transaction log backup or the active
13    transaction log to a decimal based string format which can be used to perform
14    point in time recovery of a SQL Server database using the StopAtMark option.
15
16.PARAMETER LogSequenceNumber
17    The three part hexadecimal SQL log sequence number obtained from an insert, update,
18    or delete operation that has been recorded in the transaction log or log backup.
19
20.EXAMPLE
21     Convert-MrSqlLogSequenceNumber -LogSequenceNumber '0000002e:00000158:0001'
22
23.EXAMPLE
24     '0000002e:00000158:0001' | Convert-MrSqlLogSequenceNumber
25
26.INPUTS
27    String
28
29.OUTPUTS
30    PSCustomObject
31
32.NOTES
33    Author:  Mike F Robbins
34    Website: http://mikefrobbins.com
35    Twitter: @mikefrobbins
36#>
37
38    [CmdletBinding()]
39    param (
40        [Parameter(Mandatory,
41                   ValueFromPipeline,
42                   ValueFromPipelineByPropertyName)]
43        [ValidateScript({
44          If ($_ -match '^[0-9A-Fa-f]+:[0-9A-Fa-f]+:[0-9A-Fa-f]+$') {
45            $True
46          }
47          else {
48            Throw "$_ is not a valid three part hexadecimal SQL log sequence number from a transaction log backup."
49          }
50        })]
51        [Alias('LSN', 'Current LSN')]
52        [string[]]$LogSequenceNumber
53    )
54
55    PROCESS {
56
57        foreach ($Number in $LogSequenceNumber) {
58            $i = 1
59
60            $Results = foreach ($n in $Number -split ':') {
61                $Int = [convert]::ToInt32($n, 16)
62                switch ($i) {
63                    1 {$Int; break}
64                    2 {"$([string]0*(10-(([string]$Int).Length)))$Int"; break}
65                    3 {"$([string]0*(5-(([string]$Int).Length)))$Int"; break}
66                    Default {Throw 'An unexpected error has occured.'}
67                }
68                $i++
69            }
70
71            [pscustomobject]@{
72                'OriginalLSN' = $Number
73                'ConvertedLSN' = $Results -join ''
74            }
75
76        }
77
78    }
79
80}

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 .

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

1Convert-MrSqlLogSequenceNumber -LSN 0000002e:00000158:0001

convert-lsn2a.png

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:

1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/28/2016 14:55 PM') |
2Convert-MrSqlLogSequenceNumber

convert-lsn3a.png

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

1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/01/2016 14:55 PM') |
2Convert-MrSqlLogSequenceNumber

convert-lsn4a.png

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

µ