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

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

I will keep posting as many as possible.