sp_update_jobsteps change location

Question

sp_update_jobsteps change location

Matt Williamson

SSC Journeyman

Points: 88
More actions
January 3, 2007 at 2:11 pm

#62782

I need to change the backup location for all of my DB maintenance plans. If I access the sysjobsteps table directly, there is too much text in that column to display in enterprise mgr to modify manually and Query analyser truncates it in both text and grid mode as well. If I go into the job step manually to edit it, I can scroll all the way across and see the whole thing. Ideally, I'd like to run an update using what is already there and replace it with Replace(Existing, \\OLDSERVER, \\NEWSERVER) but I haven't worked out how to do it yet. Can anyone help me? This select statement returns the rows I want to update
SELECT job_id, command
FROM sysjobsteps
WHERE (command LIKE 'EXECUTE%')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

When I need to make a lot of changes to a job, I script it into an edit file, make the changes then recreate the job. To script the job, r-click on it in Enterprise Manager -> All Tasks -> Generate SQL Script. Run the script in Query Analyzer after you've made your changes. If you want to script all the jobs, r-click on Jobs under SQL Server Agent.

Greg

Sam Greene SSCertifiable Points: 5252 More actions · Answer 2

the 'execute%' will get you what you need for now, but there are other things which may be added. You really need to look for -BkUpDB and your path in:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 9E6C1744-B83D-4FC2-AB33-1A35D64A3339 -WriteHistory -BkUpMedia DISK -BkUpDB "F:\Backups" -CrBkSubDir -BkExt "BAK"

You'd want to find -BkUpDB "mypath" with a finding function....which eludes me right now and then use the STUFF function to put your new path in.

As far everything working fine after you do this, someone more experienced could probably tell you that.

everything i just said is most likely wrong, wait for a second opinion

Matt Williamson SSC Journeyman Points: 88 More actions · Answer 3

This is really what I was looking for.

Update sysjobsteps

set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')

where command like 'EXECUTE%'

There are 2 paths in the command field that I'm updating at once, so looking for -BkUpDB isn't enough. I also need to change -Rpt as well. Luckily, it's the same server, so one replace takes care of both.