More SQL Cmdlets

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: , ,

About these ads

5 Responses to “More SQL Cmdlets”


  1. 1 Anonymous September 26, 2008 at 4:44 pm

    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.

  2. 2 Dmitry Sotnikov September 26, 2008 at 6:31 pm

    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.

  3. 3 Robbie December 14, 2009 at 1:53 pm

    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.

  4. 4 Dmitry Sotnikov December 15, 2009 at 11:13 am

    Robbie, please post the details to the discussion forums at http://powergui.org and we will do our best to help you out.


  1. 1 Quest SQL Cmdlet References « Dmitry’s PowerBlog: PowerShell and beyond Trackback on December 29, 2008 at 9:04 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

September 2008
M T W T F S S
« Aug   Oct »
1234567
891011121314
15161718192021
22232425262728
2930  

Follow

Get every new post delivered to your Inbox.

Join 2,329 other followers

%d bloggers like this: