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).
Dan Denis
This is the first, and only accurate example of executing a job with this scenario I have found, both on-line and in any reference book I have come across so far.
Stuart Moore
Thank You. Glad you’ve found it useful.
Arindam
Hi,
I tried following your method of defining Function within scriptblock and calling it.
But somehow, mine is not working. The script code is ok as I can use the code without Start-job and running as a synchronous operation and it works fine and updates database table.But while calling performing same action through function and calling it through Start-job, it fails.
Please let me know, where I can send you my code for your reference.
Thanks,
Arindam
Stuart Moore
Hi,
Yes, send me a copy and I’ll have a look at it. Either post it here as a comment, or submit it via the contact form at http://stuart-moore.com/contact/
Cheers
Stuart
DarkLite1
Thank you, this was really a great help to me! 🙂
DarkLite1
Thank you, this was really a great help to me! 🙂
I had to do it like this to be fully operational:
Start-Job -ScriptBlock {Param($Target, $OlderThanDays, $Server) Delete-OldFiles $Target $OlderThanDays $Server} -InitializationScript $JobFunc -ArgumentList ($Target, $OlderThanDays, $Server) -Name DelFiles
I’m now looking for a way to add my switch ‘-CleanFolders’ and then I’m all done.
Stuart Moore
Excellent! It’s really great to hear it’s been of use.
A commenter on another post suggested this for a way to control switches:
if ($x -eq y){
$RecoveryOption = $True
}else{
$RecoveryOption = $False
}
Restore-SQLDatabase -Database $DBName -BackupFile $backupfile -NoRecovery:$RecoveryOption
It’s the colon that makes it work. Not sure if that’s an option for you?
If you find a way to get it working I’d love to know.
Jameson
Is it possible to also initialize the function so it can be called from outside a background job?
Stuart Moore
Sorry for the tardy reply, I’ve been moving house recently.
I’m afraid I don’t quite understand what you are asking. The function could be initialized as a standard powershell function by removing the
$func={
…}
from around the function definitions. This would mean you could then just call it like any other function.If that’s not quite what you meant please let me know.
Cheers
Stuart
Alex
Many thanks. Great article, got me one step further in my PS journey.
Andrew L
Nice work. That has helped me out a load.
Cheers,
Andrew
Patrick
Hello, I found this while working on an issue where I have used Get-Service on a list of computers and occasionally one will hang the whole task. I’m wanting to get status SQL servers. After 10 seconds I want it to give up and move on to the next one instead of hanging.
I have tried your version and honestly exhausted my skills on variations. I’ve only been at this about 6 months.
$timeout = 10
$Server = “COMPUTERNAMEHERE”
$Servicename=”mssqlserver”
$func = {function do-this
{[string]$Server, [string]$Servicename, [string]$SQLServiceStatus
$SQLServiceStatus = Invoke-Command -ComputerName $Server -ScriptBlock {param($Server,$Servicename) Get-Service -computer $Server |Where-Object {$_.name -eq $Servicename}|foreach {$_.status} } -ArgumentList $Server, $Servicename, $SQLServiceStatus
}
}
Start-Job -ScriptBlock {param($Server,$Servicename) do-this $Server,$Servicename } -InitializationScript $func -ArgumentList ($Server,$Servicename, $SQLServiceStatus)
Wait-Job do-this -Timeout $timeout
Stop-Job do-this
Receive-Job do-this
Remove-Job do-this
write-host $Servicename “Status is” $SQLServiceStatus
——————————————————
Should say mssqlserver Status is running
Stuart Moore
Hi,
I think you might be better off with a simpler version using Invoke-Command. I’m assuming you want to check for all running SQL Server instances on a group of servers? I use something similar to this to do that:
$session = New-PSSession -ComputerName Server1, server2,server3 -ErrorAction SilentlyContinue
$ServiceName = 'MSSQL`$'
$timeout = 10
$remote = Invoke-Command -Session $session -ScriptBlock {Get-Service -Name *$using:servicename*} -AsJob
$remote | Wait-Job -Timeout $timeout
$output = $remote | Receive-Job
$output | ForEach {Write-Host "$($_.Name) on $($_.PsComputerName) is $($_.Status)"}
Using Invoke-Command means there’s less overhead in building up the called function compared to Start-Job, and with the -AsJob switch it’s running as a job anyway.
just a couple of comments which might help explain bits:
I’ve changed the ServiceName to MSSQL$ (escaping teh $ with `) based on what I think you’re doing (though could be wrong).
I’m using the using scope modifier to tell Invoke-Command to using the variable $servicename in the calling scope (https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_scopes?view=powershell-6&viewFallbackFrom=powershell-5)
Hope that’s useful? Sometimes it’s easy to make something more complicated than it needs to be.
Let me know that’s not right or anything doesn’t make sense