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…