Stuart Moore

Musings of a Data professional

Stuart Moore

Month: June 2013

Calling a PowerShell function in a Start-Job script block when it’s defined in the same script

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).

Upcoming SQL Server User Group sessions I’m speaking at

Just a quick note about some upcoming sessions I’m presenting at SQL Server User groups over the next couple of months:

18th July – SQL SouthWest (Exeter) – Using PowerShell to Automate SQL Server Backups and Restores 

7th AugustSQL Southampton – Using PowerShell to Automate SQL Server Backups and Restores

17th OctoberCardiff SQL User group – Using PowerShell to Automate SQL Server Backups and Restores

The session will be about how I’m using PowerShell to manage awkward backup scheduling (instances with hundreds of databases), performing automated restores to test those backups, and then to verify the restored database. With all of this being logged somewhere to keep the auditors happy.

Hopefully see some of you there.

Powered by WordPress & Theme by Anders Norén