Tuesday, September 16, 2008

Read The Transactionlog of SQL Server from PowerShell

Yes, the header is correct. Thanks to Quest Software, the ability to read individual transactions from the transaction log of SQL Server has arrived!

If you want to try it out, I've written this very brief quick start guide:

Start by downloading the trial version of Quest's LiteSpeed product: www.quest.com (products -> all products -> LiteSpeed)

Install the LiteSpeed trial you've just downloaded.

Start PowerShell

Register the SnapIn:
C:\Winnt\Microsoft.NET\Framework\v2.0.50727\installutil.exe Quest.LogReader.SqlServer.PSSnapIn.dll

Verify that the SnapIn has been registered:
Get-PSSnapin -registered

Add the SnapIn to the current PowerShell session (remember to add this to your profil if you so desire):
add-pssnapin Quest.LogReader.SqlServer

Also register the data formatting information:

Update-FormatData -AppendPath 'C:\Program Files\Quest Software\LiteSpeed\SQL Server\Quest.LogReader.SqlServer.Format.ps1xml'

You are now ready to plays around with the new commands offered by the SnapIn. To get a list of the new commands you can either consult the .chm file in the LiteSpeed folder, or just use Get-Help:

Get-Command *-QLRSql*

If you want the quick path to playing around with a transaction log file, you can use the following miniguide:

Install the Log Reader server components on your SQL server:

Set-QLRSqlSrvAgent -Install -Loginmode Windows

Lets try to look at the transaction log for the Master database:

$log = New-QLRSqlReader -Online . Master Windows
Get-QLRSqlRecord $log

If the above returns too much data, try this (you may want to modify the date a bit):

Get-QLRSqlRecord $log -After '16-09-2008 12:00:00'

Please note the the help text included for the new Commandlets contains a number of errors - If you use the examples, you will encounter a number of syntax errors, but most a easily recognized and corrected.