Monday, August 4, 2008

[Backups] Alter jobs to change your backup server

Something I had to whip up on short notice, so you can see what code I cribbed from SSMS.

Here's what we do:
  1. Find any jobs with "Backup" in the name
  2. Get the job details via sp_help_jobstep
  3. Step through each job step

    1. Create a different statement, with the new server's name
    2. If the step has BACKUP or SQLMAINT, and the old server's name, execute SP_UPDATE_JOBSTEP with the new code.

create table #tmp_sp_help_jobstep
step_id int null,
step_name nvarchar(128) null,
subsystem nvarchar(128) collate Latin1_General_CI_AS null,
command nvarchar(max) null,
flags int null,
cmdexec_success_code int null,
on_success_action tinyint null,
on_success_step_id int null,
on_fail_action tinyint null,
on_fail_step_id int null,
server nvarchar(128) null,
database_name sysname null,
database_user_name sysname null,
retry_attempts int null,
retry_interval int null,
os_run_priority int null,
output_file_name nvarchar(300) null,
last_run_outcome int null,
last_run_duration int null,
last_run_retries int null,
last_run_date int null,
last_run_time int null,
proxy_id int null,
job_id uniqueidentifier null)

declare @job_id UNIQUEIDENTIFIER
DECLARE @oldservername sysname, @newservername sysname
declare crs cursor local fast_forward
for ( SELECT sv.job_id AS [JobID]
FROM msdb.dbo.sysjobs_view AS sv
WHERE NAME LIKE '%backup%' )

SELECT @oldservername = 'ServerA'
SELECT @newservername = 'ServerB'
open crs
fetch crs into @job_id
while @@fetch_status >= 0
TRUNCATE TABLE #tmp_sp_help_jobstep
insert into #tmp_sp_help_jobstep(step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id)
exec msdb.dbo.sp_help_jobstep @job_id = @job_id
update #tmp_sp_help_jobstep set job_id = @job_id where job_id is NULL
--change to job step occurs here.
SELECT @minid = NULL, @maxid = NULL
SELECT @minid = MIN(step_id), @maxid = MAX(step_id) FROM #tmp_sp_help_jobstep
WHILE @minid <= @maxid BEGIN SELECT @jobcode = REPLACE(command, @oldservername, @newservername) FROM #tmp_sp_help_jobstep WHERE step_id = @minid IF EXISTS (SELECT * FROM #tmp_sp_help_jobstep WHERE step_id = @minid AND (command LIKE '%backup%' OR command LIKE '%sqlmaint%') AND command LIKE '%'+@oldservername+'%') -- EXEC msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=@minid, -- @command= @jobcode SELECT @jobcode SET @minid = @minid + 1 END --end change fetch crs into @job_id end close crs deallocate crs DROP table #tmp_sp_help_jobstep

No comments: