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

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:

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.

Ads by Google, Paying the Rent: