It didn’t take long for the community to start adding more cmdlets to the (somewhat limited) default Microsoft’s implementation of PowerShell cmdlets and provider for SQL.
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"
add-pssnapin Quest.LogReader.SqlServer
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:
Get-QLRSqlCommand $lr
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!
Tags: PowerShell, SQL, cmdlets
Subscribe by email
Does anyone know of a way to access a lightweight database from PowerShell (not all of us have a SQL Server handy to access)? I would like to have a place to store a small amount of persistent data between script runs. Ideally I would love to be able to access a SQLite database from PowerShell itself, but haven’t been able to figure out how to do so yet.
What I normally do in cases like that is use Export/Import xml cmdlets available in PowerShell. This is way easier than from PowerShell than any DB operations – because you can just pipeline your objects into the cmdlets.
If the amount of data is relatively small this should work for you.
Hi,
I’m currently using Quest softwares AD cmdlets, but am having real problems finding cmdlets to go with it.
For example, I wish to query AD using get-qaduser xxxx, then write those results to a local sql database so query using SQL Management studio where I will tie more data to it.
Does snyone know of any provider that will give me the desired cmdlets?
Robbie.
Robbie, please post the details to the discussion forums at http://powergui.org and we will do our best to help you out.