Alter SQL Jobs With PowerShell

I’ve been experimenting with PowerShell and SQL Server recently and noticed how missing the “Alter” step when altering SQL Server jobs with PowerShell may cause some confusion. Let’s walk through the process of disabling SQL Server jobs using PowerShell.

Disable Jobs

First, let’s pick a set of jobs to disable. We don’t want to disable all jobs on our server, but we want to disable more than one, so for this example, we’ll want to grab all jobs that contain “Blog Post Test” in the job name. We can track down these jobs in SSMS:

USE msdb;
GO

SELECT name, enabled
FROM sysjobs
ORDER BY name;
GO

And we can look these jobs up using PowerShell:

Import-Module SQLSERVER

$myJobs = Get-ChildItem -Path SQLSERVER:\SQL\localhost\default\JobServer\Jobs |
	Where-Object {$_.Name -like "*Blog Post Test*"}

$myJobs

Now that we’ve found our jobs and saved them to $myJobs, let’s add a ForEach loop to step through and disable each one:

Import-Module SQLSERVER

$myJobs = Get-ChildItem -Path SQLSERVER:\SQL\localhost\default\JobServer\Jobs |
    Where-Object {$_.Name -like "*Blog Post Test*"}

ForEach ($j in $myJobs) {
    $j.IsEnabled = $false
}

$myJobs

We can check in PowerShell and confirm that IsEnabled is $false for each job:

But we’re not done yet!

Don’t Forget Alter

If we go back to SSMS and refresh, we see that the jobs are still enabled. Why? Because we can’t forget to use the Alter function to apply our changes after disabling the jobs. Let’s apply our Alter in PowerShell:

Import-Module SQLSERVER

$myJobs = Get-ChildItem -Path SQLSERVER:\SQL\localhost\default\JobServer\Jobs | 
	Where-Object {$_.Name -like "*Blog Post Test*"}

ForEach ($j in $myJobs) {
    $j.IsEnabled = $false
}

$myJobs.Alter()

Now we can go back to SSMS one more time, re-run our query, and see the jobs are disabled:

Conclusion

We disabled a handful of jobs in the example above. As per usual with PowerShell, it can be versatile enough to make changes beyond just a few jobs. PowerShell can make changes for a few jobs, a few jobs on multiple servers, etc.

Happy Halloween, and thanks for reading!