PowerShell and SQL

Because I’ve been playing with PowerShell more and more recently, I’m going to do a talk at my user-group about it next week. Should be a lot of fun, and a great excuse to get deeper into the technology over the weekend.

One thing that I think is really great is the ease with which you can apply things to a list, because of the pipe operator – which works just like the pipe operator in any other kind of shell, but actually handles the things as objects. In my example here, it picks up the fact that I’m passing in a list ($servers), so that I can then do a ‘foreach’ loop (using the % operator). I put it all in a process section so that the $sql command drops out of scope, releasing the object nicely (which makes it equivalent to setting it to nothing). I could also used Remove-Variable ‘sql’ to drop the reference, or just set $sql to something else. But I prefer to let it fall out of scope – it just feels a bit neater.

Please forgive the cases, this is just as I typed it…

$servers = ‘server1′,’server2′,’server3’
& {
  $servers | % {
    $sql = New-Object (‘Microsoft.sqlserver.management.smo.server’) $_
    $sql.Databases | Select-Object @{Name = ‘server’;Expression = {$sql.name}}, name, lastbackupdate
  }
}

The @{Name=;Expression=} bit lets me put something into the table which doesn’t come from $sql.Databases, and hey presto, I have a nice table come out telling me when the databases were last backed up. I could easily push this through a Where-Object filter to only get ones that haven’t been backed up for some time too.

Incidentally – you should run

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) 

first to make this work. I’m just so used to having this load up as part of my PowerShell profile these days… 

One thought on “PowerShell and SQL”

  1. This is a great example of Powershell’s capaiblities as were your gadget demos. Powershell really takes shell-scripting to the next level through rich object support as you mentioned.

    The other nice side to this story is that you can build your own cmdlets in .NET that extend the shell itself. For example you can expose your own LOB App data stores so that they can be administered and accessed via the shell as virtual folders in the same way that you can access the file system, registry.

    Also you can easily access your own custom .NET classes and class them from within you shell scripts just as you have done here for example

    [System.Reflection.Assembly]:LoadWithPartialName(“MyAssembly”)

    The possiblities are endless!

Leave a Reply

Your email address will not be published. Required fields are marked *