Script to Generate Job Scripts – SQL Server

The following script generates the “Job Details” as in script form. I struggled a lot to migrate jobs from one server to another server and decided to write a script to do this. A bit long, but worth the time to look at 🙂

This script uses the following sys objects

sysjobs_view

sysjobs

sysjobservers

sysjobschedules

sysjobsteps

</pre>
<p style="padding-left: 30px;">SET STATISTICS IO, TIME OFF
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 SET NOCOUNT ON;
 SET ANSI_WARNINGS OFF;
 SET XACT_ABORT OFF;</p>
BEGIN TRY
<p style="padding-left: 30px;">/* Variables for all processes */</p>
<p style="padding-left: 30px;">DECLARE @JobID nvarchar(100),
 @JobName varchar (128),
 @JobCategory varchar (128),
 @JobCategoryClass varchar(128),
 @atthistime datetime,
 @timeinfo varchar(30),
 @MaxJobs int,
 @JobControl int,
 @enabled int,
 @notify_level_eventlog int,
 @notify_level_email int,
 @notify_level_netsend int,
 @notify_level_page int,
 @delete_level int,
 @description nvarchar(128),
 @category_name nvarchar(128),
 @owner_login_name nvarchar(128),
 @notify_email_operator_name nvarchar(128),
 @MaxSteps int,
 @LoopControl int,
 @step_name nvarchar (128),
 @step_id int,
 @cmdexec_success_code int,
 @on_success_action int,
 @on_success_step_id int,
 @on_fail_action int,
 @on_fail_step_id int,
 @retry_attempts int,
 @retry_interval int,
 @os_run_priority int,
 @subsystem nvarchar (128),
 @command nvarchar (max),
 @database_name nvarchar(128),
 @flags int,
 @MaxSchedules int,
 @SchedulesLoopControl int,
 @name nvarchar(2000),
 @sch_enabled int,
 @freq_type int,
 @freq_interval int,
 @freq_subday_type int,
 @freq_subday_interval int,
 @freq_relative_interval int,
 @freq_recurrence_factor int,
 @active_start_date int,
 @active_end_date int,
 @active_start_time int,
 @active_end_time int,
 @schedule_uid nvarchar (50),
 @server_name varchar(30)</p>
<p style="padding-left: 30px;">SELECT @atthistime = GETDATE()</p>
<p style="padding-left: 30px;">SELECT @timeinfo = CAST(@atthistime as varchar(30))</p>
<p style="padding-left: 30px;">CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50))</p>
<p style="padding-left: 30px;">/* insert the details of all enabled jobs into the temp table */</p>
<p style="padding-left: 30px;">INSERT INTO #Jobs (jobid)
 SELECT jobid = convert(varchar(50),job_id)
 FROM msdb.dbo.SysJobs WITH (NOLOCK)
 where enabled = 1
 order by name asc</p>
<p style="padding-left: 30px;">SELECT @JobControl = 1
 SELECT @MaxJobs = MAX(id) FROM #jobs</p>
<p style="padding-left: 30px;">--Create Jobs by looping through all the jobs</p>
<p style="padding-left: 30px;">WHILE (@JobControl <= @MaxJobs)
 BEGIN</p>
<p style="padding-left: 30px;">PRINT 'USE [msdb]'
 PRINT 'GO'
 PRINT ''</p>
<p style="padding-left: 30px;">SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl</p>
<p style="padding-left: 30px;">SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID
 SELECT @JobCategory =sc.name, @JobCategoryClass = category_class FROM msdb.dbo.sysjobs sj
 INNER JOIN msdb.dbo.syscategories sc
 ON sc.category_id = sj.category_id
 WHERE Job_ID = @JobID</p>
<p style="padding-left: 30px;">PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @timeinfo + ' ******/'
 PRINT 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'
 PRINT 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'
 PRINT 'GO'
 PRINT ''
 PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @timeinfo + ' ******/'
 PRINT 'BEGIN TRANSACTION'
 PRINT 'DECLARE @ReturnCode INT'
 PRINT 'SELECT @ReturnCode = 0'
 PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @timeinfo + ' ******/'
 PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'
 PRINT 'BEGIN'
 PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 PRINT ''
 PRINT 'END'
 PRINT ''
 PRINT 'DECLARE @jobId BINARY(16)'
 PRINT ''</p>
<p style="padding-left: 30px;">SELECT @enabled = sj.enabled,
 @notify_level_eventlog = sj.notify_level_eventlog,
 @notify_level_email = sj.notify_level_email,
 @notify_level_netsend = sj.notify_level_netsend,
 @notify_level_page = sj.notify_level_page,
 @delete_level = sj.delete_level,
 @description = sj.[description],
 @category_name = sc.name,
 @owner_login_name = SUSER_NAME(sj.owner_sid),
 @notify_email_operator_name = so.name
 FROM msdb.dbo.sysjobs sj
 INNER JOIN msdb.dbo.syscategories sc
 ON sc.category_id = sj.category_id
 LEFT OUTER JOIN msdb.dbo.sysoperators so
 ON sj.notify_email_operator_id = so.id
 WHERE Job_ID = @JobID</p>
<p style="padding-left: 30px;">print 'select @jobid=job_id from msdb.dbo.sysjobs where (name = N''' + @jobname + ''')'
 print 'if (@jobid is NULL)'
 print 'begin'</p>
<p style="padding-left: 30px;">PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + @JobName + ''','
 PRINT '@enabled=' + CAST(@enabled as varchar(30))+ ','
 PRINT '@notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','
 PRINT '@notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','
 PRINT '@notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','
 PRINT '@notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','
 PRINT '@delete_level=' + CAST(@delete_level as varchar(30))+ ','
 PRINT '@description=N''' + REPLACE(@description, '''','''''') + ''','
 PRINT '@category_name=N''' + @category_name + ''','
 PRINT '@owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''','</p>
<p style="padding-left: 30px;">/* sometime the following works and some it is not. better make changes to suit your purpose */</p>
<p style="padding-left: 30px;">IF @notify_email_operator_name IS NOT NULL
 PRINT '@notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'
 ELSE
 PRINT '@job_id = @JobID OUTPUT'
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 PRINT ''
 print 'end'</p>
<p style="padding-left: 30px;">/* Now start the work on the steps */</p>
<p style="padding-left: 30px;">SELECT @LoopControl = 1
 SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID</p>
<p style="padding-left: 30px;">WHILE (@LoopControl <= @MaxSteps)
 BEGIN</p>
<p style="padding-left: 60px;">SELECT @step_name = step_name,
 @step_id = step_id,
 @cmdexec_success_code = cmdexec_success_code,
 @on_success_action = on_success_action,
 @on_success_step_id = on_success_step_id,
 @on_fail_action = on_fail_action,
 @on_fail_step_id = on_fail_step_id,
 @retry_attempts = retry_attempts,
 @retry_interval = retry_interval,
 @os_run_priority = os_run_priority,
 @subsystem = subsystem,
 @command = command,
 @database_name = database_name,
 @flags = flags
 FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
 AND step_id = @LoopControl</p>
<p style="padding-left: 60px;">PRINT ''
 PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @timeinfo + '******/'
 print 'if not exists (select * from msdb.dbo.sysjobsteps where job_id=@jobid and step_id=' + cast(@step_id as varchar) + ')'
 PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','
 PRINT '@step_id=' + CAST(@step_id as varchar(30))+ ','
 PRINT '@cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','
 PRINT '@on_success_action=' + CAST(@on_success_action as varchar(30))+ ','
 PRINT '@on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','
 PRINT '@on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','
 PRINT '@on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','
 PRINT '@retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','
 PRINT '@retry_interval=' + CAST(@retry_interval as varchar(30))+ ','
 PRINT '@os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','
 PRINT '@command=N''' + REPLACE(@command, '''','''''') + ''','
 PRINT '@database_name=N''' + @database_name + ''','
 PRINT '@flags=' + CAST(@flags as varchar(30))
 PRINT ''
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 SELECT @LoopControl = @LoopControl + 1</p>
<p style="padding-left: 30px;">END -- End Steps</p>
<p style="padding-left: 30px;">PRINT ''
 PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 PRINT ''</p>
<p style="padding-left: 30px;">/* Finally Schedules  */</p>
<p style="padding-left: 30px;">SELECT @SchedulesLoopControl = 1</p>
<p style="padding-left: 30px;">CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)</p>
<p style="padding-left: 30px;">INSERT INTO #Schedules (schedule_id)
 SELECT schedule_id = sjs.schedule_id
 FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
 WHERE sjs.Job_ID = @JobID</p>
<p style="padding-left: 30px;">SELECT @MaxSchedules = MAX(id) FROM #Schedules</p>
<p style="padding-left: 30px;">IF EXISTS (SELECT COUNT(*) FROM #Schedules)
 BEGIN</p>
<p style="padding-left: 60px;">WHILE (@SchedulesLoopControl <= @MaxSchedules)
 BEGIN</p>
<p style="padding-left: 90px;">SELECT @name = name,
 @sch_enabled = enabled,
 @freq_type = freq_type,
 @freq_interval = freq_interval,
 @freq_subday_type = freq_subday_type,
 @freq_subday_interval = freq_subday_interval,
 @freq_relative_interval = freq_relative_interval,
 @freq_recurrence_factor = freq_recurrence_factor,
 @active_start_date = active_start_date,
 @active_end_date = active_end_date,
 @active_start_time = active_start_time,
 @active_end_time = active_end_time,
 @schedule_uid = schedule_uid
 FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
 INNER JOIN #Schedules s ON ss.schedule_id = s.schedule_id
 WHERE sjs.Job_ID = @JobID
 AND s.id = @SchedulesLoopControl</p>
<p style="padding-left: 90px;">PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','
 PRINT '@enabled=' + CAST(@sch_enabled as varchar(30))+ ','
 PRINT '@freq_type=' + CAST(@freq_type as varchar(30))+ ','
 PRINT '@freq_interval=' + CAST(@freq_interval as varchar(30))+ ','
 PRINT '@freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','
 PRINT '@freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','
 PRINT '@freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','
 PRINT '@freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','
 PRINT '@active_start_date=' + CAST(@active_start_date as varchar(30))+ ','
 PRINT '@active_end_date=' + CAST(@active_end_date as varchar(30))+ ','
 PRINT '@active_start_time=' + CAST(@active_start_time as varchar (30)) + ','
 PRINT '@active_end_time=' + CAST(@active_end_time as varchar (30))
 PRINT ''</p>
<p style="padding-left: 90px;">PRINT ''
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 PRINT ''</p>
<p style="padding-left: 90px;">SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1</p>
<p style="padding-left: 60px;">END -- End Schedules</p>
<p style="padding-left: 30px;">END -- END IF</p>
<p style="padding-left: 30px;">SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END
 FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID</p>
<p style="padding-left: 30px;">PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''
 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
 PRINT 'COMMIT TRANSACTION'
 PRINT 'GOTO EndSave'
 PRINT 'QuitWithRollback:'
 PRINT ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
 PRINT 'EndSave:'
 PRINT ''
 PRINT 'GO'
 PRINT ''
 PRINT ''</p>
<p style="padding-left: 30px;">SELECT @JobControl = @JobControl + 1</p>
<p style="padding-left: 30px;">DROP TABLE #Schedules</p>
END --End Jobs

DROP TABLE #Jobs

END TRY

BEGIN CATCH
 DROP TABLE #Jobs
 DROP TABLE #Schedules
 END CATCH;
<pre>

I will keep posting as many as possible.