Find-and-Replace for SQL Server Agent Jobs
Once in a while you might need to make common changes to a lot of SQL Server Agent Jobs. For example, if you change the path where you store SQL Server backup files, you might need to update many jobs to point at \\SERVERB\Backups instead of \\SERVERA\Backups. The script below provides a simple instance-wide find-and-replace for SQL Server Agent job-step commands. It modifies the command text for all jobs that contain the matching @Find parameter, replacing it with the provided @Replace value. You can exclude jobs by adding them to the list of values in the #excludeJobs table.
Sometimes it seems like you might need an entire committee to get any changes made. This find-and-replace script should help reduce the need for that!
Jan de Bray – The Governors of the Guild of St Luke, Haarlem
Here’s the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
USE msdb; DECLARE @Find nvarchar(max); DECLARE @Replace nvarchar(max); DECLARE @DebugOnly bit; SET @Find = N'\\SERVERA\Backups'; SET @Replace = N'\\SERVERB\Backups'; SET @DebugOnly = 1; IF OBJECT_ID(N'tempdb..#excludeJobs', N'U') IS NOT NULL BEGIN DROP TABLE #excludeJobs; END CREATE TABLE #excludeJobs ( JobName sysname NOT NULL PRIMARY KEY CLUSTERED ); INSERT INTO #excludeJobs (JobName) VALUES ('The Name of a job you want to skip'); IF OBJECT_ID(N'tempdb..#deets', N'U') IS NOT NULL DROP TABLE #deets; CREATE TABLE #deets ( JobName sysname NOT NULL , StepName sysname NOT NULL , OldCommand nvarchar(max) NOT NULL , NewCommand nvarchar(max) NOT NULL , PRIMARY KEY (JobName, StepName) ); DECLARE @JobName sysname; DECLARE @StepName sysname; DECLARE @StepID int; DECLARE @Command nvarchar(max); DECLARE @NewCommand nvarchar(max); BEGIN TRY BEGIN TRANSACTION; DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT sj.name , sjs.step_name , sjs.step_id , sjs.command FROM dbo.sysjobsteps sjs INNER JOIN dbo.sysjobs sj ON sjs.job_id = sj.job_id WHERE sjs.command LIKE N'%' + @Find + N'%' ESCAPE N'|' COLLATE SQL_Latin1_General_CP1_CI_AS AND sj.enabled = 1 AND NOT EXISTS ( SELECT 1 FROM #excludeJobs ej WHERE ej.JobName = sj.name ) ORDER BY sj.name , sjs.step_name; OPEN cur; FETCH NEXT FROM cur INTO @JobName , @StepName , @StepID , @Command; WHILE @@FETCH_STATUS = 0 BEGIN SET @NewCommand = REPLACE(@Command, @Find, @Replace) COLLATE SQL_Latin1_General_CP1_CI_AS; INSERT INTO #deets (JobName, StepName, OldCommand, NewCommand) SELECT JobName = @JobName , StepName = @StepName , PriorCommand = @Command , NewCommand = @NewCommand; IF @DebugOnly = 0 BEGIN EXEC dbo.sp_update_jobstep @job_name = @JobName, @step_id = @StepID, @command = @NewCommand; PRINT N'Updated ' + @JobName; END FETCH NEXT FROM cur INTO @JobName , @StepName , @StepID , @Command; END CLOSE cur; DEALLOCATE cur; SELECT * FROM #deets; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; PRINT N'Transaction rolled back'; END PRINT ERROR_MESSAGE(); PRINT ERROR_LINE(); END CATCH |
Set the @DebugOnly parameter to 0 to have the script make changes to your jobs. If you leave it at 1, the script simply produces output showing change candidates, along with the code-changes that would be made. You should run the script with 1 until you’re comfortable the changes made by the script are the ones you intend to make.
The script uses the T-SQL REPLACE function to make the changes to the job-step command string. According to the Microsoft Documentation for REPLACE, the function will truncate values over 8,000 characters in length unless you pass in MAX type variables. The code defines the @Find and @Replace values as nvarchar(max) to avoid this potential pitfall.
Let me know if this find-and-replace script was helpful, and please check out the rest of our SQL Server tools.
[…] Hannah Vernon shares a script to perform a find-and-replace across SQL Agent jobs: […]
[…] Find-and-Replace for SQL Server Agent Jobs – SQL Server Science […]