Update Job Step Across Multiple SQL Servers

I recently needed to update the command of a job step for a job existing on multiple servers. Logging into each server to make the same changes can be time consuming. Thankfully, there are better methods.

Let’s look at a couple of ways to update a job step more efficiently.

Update with Central Management Server

If you have your database servers registered in a Central Management Server, updating the SQL jobs should be a piece of cake.

(Note: If you’re not familiar with setting up Central Management Server, click on this link to read Microsoft’s documentation.)

Let’s say we have a group of servers setup for Prod. We’ll right click on that group and select New Query.

If the job name is identical across each server, we should only have to run a query once to update all of the jobs.

Let’s update a job called Backup_Test. We can execute something like:

USE msdb;
GO

EXEC dbo.sp_update_jobstep @job_name = N'Backup_Test'
	,@step_id = 1
	,@command = N'EXECUTE dbo.DatabaseBackup
@Databases = ''USER_DATABASES'',
@Directory = ''D:\FullBackup'',
@BackupType = ''FULL'',
@Verify = ''Y'',
@Compress = ''Y'',
@CheckSum = ''Y'',
@CleanupTime = 24'
GO

In our Messages window, we will hopefully see a message stating “Commands completed successfully. (X servers)” with X indicating the number of servers our query ran against. I only have two servers with my test group so I see:

Our Backup_Test job steps on each server in our group have been updated.

Update with PowerShell

What if you aren’t utilizing Central Management Server? Another way to quickly update the job step on multiple servers is by using PowerShell.

First, make sure the SqlServer module is installed by running the following in PowerShell:

Install-Module -Name SqlServer -RequiredVersion 21.1.18256

Now, we can utilize that SqlServer module to update our job step. We’ll start by adding our list of servers to a $ServerList variable:

# Add server names to $ServerList
$ServerList = "ProdServer1", "ProdServer2", "ProdServer3"

And then we can load the jobs from those servers:

# Retrieve jobs from $ServerList using Get-SQLAgentJob
$Jobs = Get-SQLAgentJob -ServerInstance $ServerList

Next, we’ll cycle through the jobs and update the job steps for our Backup_Test job:

# Check jobs for Backup_Test name and set job step 1 command when found
Foreach($job in $Jobs.Where{$_.Name -eq "Backup_Test" -and $_.isenabled -eq $true})
{
    Foreach ($Step in $Job.jobsteps.Where{$_.Id -eq "1"})
    {
        $Step.Command = $Step.Command = "EXECUTE dbo.DatabaseBackup
        @Databases = 'USER_DATABASES',
        @Directory = 'D:\FullBackup',
        @BackupType = 'FULL'',
        @Verify = 'Y',
        @Compress = 'Y',
        @CheckSum = 'Y',
        @CleanupTime = 24'
        GO"
        $Step.Alter()
    }
}

If we put those PowerShell steps together, we have:

# Add server names to $ServerList
$ServerList = "ProdServer1", "ProdServer2", "ProdServer3"

# Retrieve jobs from $ServerList using Get-SQLAgentJob
$Jobs = Get-SQLAgentJob -ServerInstance $ServerList

# Check jobs for Backup_Test name and set job step 1 command when found
Foreach($job in $Jobs.Where{$_.Name -like '*Backup_Test*' -and $_.isenabled -eq $true})
{
    Foreach ($Step in $Job.jobsteps.Where{$_.Id -eq "1"})
    {
        $Step.Command = $Step.Command = "EXECUTE dbo.DatabaseBackup
        @Databases = 'USER_DATABASES',
        @Directory = 'D:\FullBackup',
        @BackupType = 'FULL'',
        @Verify = 'Y',
        @Compress = 'Y',
        @CheckSum = 'Y',
        @CleanupTime = 24'
        GO"
        $Step.Alter()
    }
}

Saving You Time and Money

Consider giving one of these options a try the next time you have to make job changes. Hopefully, it makes your life a little bit easier.

Thanks for reading!