SQL Server Jobs – How to Update Commands in Ola Hallengren’s SQL Server Jobs?

ola-hallengrensql server

I am looking for an easy way to update the jobs that get created from Ola's MaintenanceSolution.sql script without having to completely delete and re-create them or having to modify each job individually by going into the job's properties, then into the step and modifying the command.

Specifically, I'm trying to update the @CleanupTime argument, which is used to tell the job when to delete old backups. According to his FAQ, whenever a newer version of his script is available and you want to update to that newer version, you just need to download the MaintenanceSolution.sql script and run it again, with the option of specifying SET @CreateJobs = 'N' if you don't want to create the jobs. That tells me that if you leave @CreateJobs = 'Y', it would create the jobs anew, but this doesn't seem to be the case.

For example, if the "DatabaseBackup – USER_DATABASES – FULL" job already exists, with the command in the step showing @CleanupTime = 48, then I re-run the MaintenanceSolution.sql script with @CleanupTime = 168, and then go back into the job's step, it still shows @CleanupTime = 48.

Best Answer

You can update steps without completely recreating everything and without pointing and clicking yourself to death in the UI. You can just write manual queries directly against msdb.dbo.sysjobsteps (start with SELECTs that issue the REPLACE() calls, then when you're happy, change it to UPDATE).

SELECT REPLACE(js.command, N'foo', N'bar') 
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

When you're satisfied it's correct, only the first couple of lines change:

UPDATE js
  SET command = REPLACE(js.command, N'foo', N'bar') 
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

It's tough to get more automated than that (e.g. update more than one job at once), since the string you want to replace might not be exactly the same in all job steps, and you might get false positives, too.

Related Question