Last week I found this blog post by Jacob Bindslet in which he is using PowerShell to get SQL transaction data!
And once I found out that the cmdlets are actually shipped with a trial version of Litespeed (which you can freely download here) – I got really interested and gave it a try.
For some reason (“to ship is to choose”? ;)) the cmdlets were not registered by default, but this was easy to fix:
set-alias installutil $env:windir\Microsoft.NET\Framework\v2.0.50727\installutil.exe
installutil "C:\Program Files\Quest Software\LiteSpeed\SQL Server\Quest.LogReader.SqlServer.PSSnapIn.dll"
Now I could connect to a database:
$lr = New-QLRSqlReader -Server spb9771 -Database AdventureWorksDW -LoginMode Windows -Online
And retrieve the latest changes from the transaction log:
Or use parameters to limit the set:
Get-QLRSqlCommand $lr -After '24-09-2008 12:00:00'
And not only can I use standard PowerShell cmdlets to output the data into csv/xml/html – the snapin has more advanced Out-* cmdlets that let me save the data into another DB:
Get-QLRSqlCommand $lr | Out-QLRSqlDatabase -Server mydbserv -Database MyAuditDB -Table AdventureWorksDW_log -LoginMode Windows
Now, if I schedule that to be automatically run every now and then (or actually better off just before the backup is started) I get full audit trail of any changes made to my database! Is that cool or not?
Besides Litespeed, Log Reader seems to also be available with Toad and Change Director for SQL Server – if you have any of these products, look for the
Quest.LogReader.SqlServer.PSSnapIn.dll file. If not, download the trial here!