January 19, 2012 at 6:17 am
I'm looking for a way to script out all of the jobs on a server to a text file for disaster recovery purposes. The idea is to have a nightly job which creates a new script file on the file system which is then transferred to a DR server. I don't want to import the jobs on the DR server (it's used as a QA server during non-DR time). I simply want an updated script of jobs ready in case of disaster. In SSMS, you can select SQL Server Agent / Jobs, F7 and then select all the jobs in the right window and "Script job to..." a file. But can this be automated? I assume there is a way to dig this info out of the system files but haven't found a script yet.
THANKS
January 19, 2012 at 7:28 am
Hi,
Below link may be useful for you, i haven't gone through in detail 🙂
http://www.sqlservercentral.com/scripts/generate+jobs+scipts/68105/
January 19, 2012 at 7:40 am
i get a boatload of errors with that script in SQL2008. 🙁
January 19, 2012 at 7:52 am
OLDCHAPPY (1/19/2012)
i get a boatload of errors with that script in SQL2008. 🙁
the code from the post is filled with CHAR(160) instead of spaces; it was used to mainiian formatting and spacing by the author, i guess
that char looks like a space, but is not.
here it is all cleaned up for paste-ability:
/**************************************Script to generate all the Jobs on a server **************************************/
--CreatedBy - Amit Mathur (v-amat)
--CreatedDate - 08/26/2009
/**************************************Script to generate all the Jobs on a server **************************************/
SET NOCOUNT ON
BEGIN TRY
PRINT 'USE [msdb]'
PRINT 'GO'
PRINT ''
DECLARE @JobID nvarchar(100),
@JobName varchar (128),
@JobCategory varchar (128),
@JobCategoryClass varchar(128),
@Now datetime,
@Nowtext varchar(30)
SELECT @Now = GETDATE()
SELECT @Nowtext = CAST(@Now as varchar(30))
CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50))
INSERT INTO #Jobs (jobid) SELECT jobid = convert(varchar(50),job_id) FROM msdb.dbo.SysJobs WITH (NOLOCK)
DECLARE @MaxJobs int,
@JobControl int
SELECT @JobControl = 1
SELECT @MaxJobs = MAX(id) FROM #jobs
--Create Jobs by looping through all the existing jobs on the server
WHILE (@JobControl <= @MaxJobs)
BEGIN --BEGIN Jobs
SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl
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
PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
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:' + @Nowtext + ' ******/'
PRINT 'BEGIN TRANSACTION'
PRINT 'DECLARE @ReturnCode INT'
PRINT 'SELECT @ReturnCode = 0'
PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'
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 ''
DECLARE @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)
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
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') + ''','
IF @notify_email_operator_name IS NOT NULL
BEGIN
PRINT ' @notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'
END
ELSE
BEGIN
PRINT ' @job_id = @JobID OUTPUT'
END
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
--CREATE STEPS
DECLARE @MaxSteps int,
@LoopControl int
SELECT @LoopControl = 1
SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
WHILE (@LoopControl <= @MaxSteps)
BEGIN
DECLARE @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
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
PRINT ''
PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'
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 ''
SELECT @LoopControl = @LoopControl + 1
END -- End Steps While
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
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 ''
--CREATE SCHEDULES
DECLARE @MaxSchedules int,
@SchedulesLoopControl int
SELECT @SchedulesLoopControl = 1
CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)
INSERT INTO #Schedules (schedule_id) SELECT schedule_id = sjs.schedule_id
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
--INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
WHERE sjs.Job_ID = @JobID
SELECT @MaxSchedules = MAX(id) FROM #Schedules
IF EXISTS (SELECT COUNT(*) FROM #Schedules)
BEGIN
WHILE (@SchedulesLoopControl <= @MaxSchedules)
BEGIN
DECLARE @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)
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
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 ' @schedule_uid=N''' + @schedule_uid + ''''
PRINT ''
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1
END -- End Schedules While loop
END -- END IF (SELECT COUNT(*) FROM #Schedules) > 0
DECLARE @server_name varchar(30)
SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END
FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID
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 ''
SELECT @JobControl = @JobControl + 1
DROP TABLE #Schedules
END --End Jobs
DROP TABLE #Jobs
END TRY
BEGIN CATCH
DROP TABLE #Jobs
DROP TABLE #Schedules
END CATCH;
Lowell
January 19, 2012 at 7:56 am
Here's a Powershell version I use. If you want all jobs, enabled or not, just remove the "if ($job.isEnabled) {" line and it's matching "}"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
$Server = 'Your_Server_Name_Here'
$outFile = "${Server}_Jobs.sql"
if (Test-Path $outFile ) { Remove-Item $outFile }
$srv = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
foreach ($job in $srv.JobServer.Jobs ) {
if ($job.isEnabled) {
$job.Script() | Out-File $outFile -Append
}
}
January 19, 2012 at 9:07 am
I have found the powershell method and the VB scripting method is the best way. If you use TSQL method then any job step that has more than 8000 characters will get truncated.
I found a good VB script online and I had documented that here
http://saveadba.blogspot.com/2011/12/sql-server-2005-generate-scripts-for.html
This works fine for SQL Server 2008 as well.
Blog
http://saveadba.blogspot.com/
January 19, 2012 at 10:43 am
Lowell,
The script gave me an erroron the While Statement?
WHILE (@LoopControl <= @MaxSteps)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply