Category: powershell

While writing some scripts some upcoming SQL Server User Group presentations I had to remind myself of how to do something in PowerShell, and thought I’d put a reminder up here for myself and for anyone else who needs it.

The problem was that I wanted to show firing off multiple SQL Server backup jobs asynchronously from within a PowerShell script. Sounds nice and simple, write some custom modules, and then fire them off with Start-Job as required. And while that’s the best way to do things (PowerShell rule1: Write functions, not scripts), in this case I wanted to keep everything in one script as it would make it easier when presenting

So the basics set up is:

function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}

while($i>$x){
    Start-Job -ScriptBlock <WhatDoIPutHere?>
}

Normally I’d just add the other script or module in there, but what about the function? Well, the trick is to pass the function in as a variable to InitializationScript, and then call it in ScriptBlock like so:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {backup_db_id} -InitializationScript $func
}

So that’s the first step. But I also want to pass through the 2 variables, so I’ll need to parametise the function pass. Which means stacking things like this:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {param($tm,$ti) backup_db_id $tm $ti } -InitializationScript $func -ArgumentList($x,$i)
    $i++
}

And now everything is passed through happily.

The other thing to note from the function definition here, is that I’ve had to include the Import-Module to load the SQLPS extensions as PowerShell will be starting up a new instance for every Start-Job and that instance won’t have the SQLPS extensions loaded up (unless you’ve set it as a system wide default, but I don’t always want to rely on that).