Altering a Job Step on Hundreds of SQL Servers with PowerShell

I flew to Utrecht last week to present with Chrissy LeMaire and Sander Stad to present to the joint Dutch SQL and PowerShell User Groups. Whilst I was sat at the airport I got a phone call from my current client. “We need to change the backup path for all of the servers to a different share, how long will it take you?”

About 5 minutes  (PowerShell is very powerful – be careful when following these examples 😉 )

We will use the sqlserver module, so you will need to have installed the latest version of SSMS from https://sqlps.io/dl

This code was run using PowerShell version 5 and will not work on Powershell version 3 or lower as it uses the where method.

Lets grab all of our jobs on the estate. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file)

$Jobs = Get-SQLAgentJob -ServerInstance $Servers

Once we have the jobs we need to iterate only through the ones we need to. This step could also have been done in the line above. Lets assume we are using the Ola Hallengren Solution to backup our estate

Foreach($job in $Jobs.Where{$_.Name -like '*DatabaseBackup*' -and $_.isenabled -eq $true})

Then because I have to target a specific job step I can iterate through those and filter in the same way

foreach ($Step in $Job.jobsteps.Where{$_.Name -like '*DatabaseBackup*'})

Now all I need to do is to replace C:\Backup with C:\MSSQL\Backup (in this example I am using my labs backup paths)

$Step.Command = $Step.Command.Replace("Directory = N'C:\Backup'","Directory = N'C:\MSSQL\Backup'")

And then call the Alter method

$Step.Alter()

And that is all there is to it. Here is the full script I used

$Jobs = Get-SQLAgentJob -ServerInstance $Servers

Foreach($job in $Jobs.Where{$_.Name -like '*DatabaseBackup*' -and $_.isenabled -eq $true})
{
foreach ($Step in $Job.jobsteps.Where{$_.Name -like '*DatabaseBackup*'})
{
$Step.Command = $Step.Command.Replace("Directory = N'C:\Backup'","Directory = N'C:\MSSQL\Backup'")
$Step.Alter()
}
}

In only a few minutes I had altered several hundred instances worth of Ola Hallengren Jobs 🙂

This is one of the many reasons I love PowerShell, it enables me to perform mass changes very quickly and easily. Of course, you need to make sure that you know that what you are changing is what you want to change. I have caused severe issues by altering the SQL alerts frequency to 1 second instead of one hour on an estate!! Although the beauty of PowerShell meant that I was able to change it very quickly once the problem was realised
You can change a lot of settings. If you look at what is available at a job step level
job-step-properties
Happy Automating

Advertisements

5 thoughts on “Altering a Job Step on Hundreds of SQL Servers with PowerShell

  1. Pingback: Friday Reading 2017-02-17 | DBA From The Cold

  2. Pingback: Adding a T-SQL Job Step to a SQL Agent Job with PowerShell | SQL DBA with A Beard

  3. Maybe add a step to verify first that the new backup target path exists?
    And if not, send out an email warning.
    I don’t think the PS script should just create the folder, because permissions need to be set to limit who can access it, right?

  4. Pingback: Backing up SQL Server on Linux using Ola Hallengrens Maintenance Solution | SQL DBA with A Beard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s