Sql-server – Update strings for all cmdexec Jobs

jobssql serversql-server-2008sql-server-agentt-sql

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?

We'd like to change our logging location, and in principle I'd think this could be done very simply by changing the /O "\\LOCATION\file.log" strings.

I can see how you'd do that in fact

USE MSDB
GO
update dbo.sysjobsteps
set [command] = replace([command], '\\OLDLOCATION\... ', '\\NEWLOCATION\... ')
WHERE...

BUT I've had it drummed into me that you should never update the sys tables manually, so the idea of doing this makes me beyond nervous. For anything else I'd use the dbo.sp_update_job proc (http://msdn.microsoft.com/en-gb/library/ms188745.aspx for those who've strayed here looking for something else job related), but that procedure doesn't seem to be able to handle this.

I have a hunch that this might possibly be an exception to the "Never Update the Sys Tables" rule though? I can't imagine that the cmdexec string is implied in any further relations in the way, for example, enabled/disabled status evidently is. Wishful thinking?

So I don't know for sure, and I can't seem to find out. Anyone know or have any experience with this?

Thanks
Dan

Best Answer

In my opinion, this is one of those operations that would definitely benefit from PowerShell. All you'd need to do is gather the list of job steps that have your old string (in this case, your old location) and replace it with the new string (read: new location).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$OldLocation = "<YourOldLocation>"
$NewLocation = "<YourNewLocation>"

$SqlServerName = "localhost"    # by default this will look at the local default instance
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

# loop through all of the SQL Server Agent Jobs on the instance
foreach ($Job in $SqlServer.JobServer.Jobs) {

    # loop through each Job Step on the current Job
    foreach ($JobStep in $Job.JobSteps) {

        # test to see if the Job Step Command text contains your original string
        if ($JobStep.Command.Contains($OldLocation)) {
            $JobStep |
                Select-Object Parent, Name, Command

            # the below two lines would make the change to the new string and apply them
            #$JobStep.Command = $JobStep.Command.Replace($OldLocation, $NewLocation)
            #$JobStep.Alter()
        }
    }
}

NOTE: This is sample code and should thoroughly be tested in a non-production environment. Ensure that your previous data is backed up, and that you are positive the change is the desirable outcome.

All this code does it loops through all your jobs on a specific instance. If the job step command contains a certain string, then it will show you what job it belongs to, the name of the step, and the command containing the string. I have deliberately commented out the command modification lines so that the code only actually identifies which steps contain your string.

You can also narrow your search by adding additional conditional clauses to the if block. For instance, if you just want to check CmdExec jobs, add this with an -and conditional check:

$JobStep.SubSystem -eq [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::CmdExec

This is simply one way to do this. I will stress my point again: make sure you test out this theory and execution far away from production, and ensure that you can revert changes with the necessary backup.

Related Question