Automatically create scripts for all SQL Server Jobs

Question

Currently I'm trying to automatically generate a create script for all my SQL jobs of a MS SQL2005 Server.

One method I found was done manually http://msdn.microsoft.com/en-us/library/ms191450.aspx
A second method I found could be done automatically but I don't have direct access to the SQL server.

Broken link removed: relatedterms.com/thread/1916663/Can%20I%20script%20out%20SQL%20Server%20jobs%20programmatically

Does anyone know a good TSQL statement or a simple program for this?

You said you don't have direct access to the SQL Server? How do you plan to run a TSQL solution please? — gbn
– gbn, Commented Jul 29, 2010 at 9:57
I meant that I only can access the server through SQL Management Studio and not by for example Remote Desktop Protocol. So I can't run third party software on the server. — freggel
– freggel, Commented Jul 29, 2010 at 10:12

Kin Shah · Accepted Answer · 2018-10-16 15:48:01Z

7

This is pretty old question, but for future visitors my answer can be helpful :

Another way is to just export to file using dbatools

Get-DbaAgentJob -SqlInstance workstation\sql2016 |  Export-DbaScript

you can even script out your entire instance or subset including agent jobs, operators, logins, etc for disaster recovery purpose.

answered Oct 16, 2018 at 15:48

Kin Shah

6126 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ardalan Shahgholi · Accepted Answer · 2017-05-24 23:05:16Z

I use this Stored Procedur for create script from all job in one database :

USE msdb
Go

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

Go
Create PROCEDURE CreateBackUpFromJob
@JobName NVARCHAR(1000) ,
@Path VARCHAR(1000)
AS 
BEGIN

    DECLARE @JobCommand NVARCHAR(MAX) = ''
    DECLARE @JobCommand1 NVARCHAR(MAX) = ''
    DECLARE @JobCommand2 NVARCHAR(MAX) = ''

---------------- Declare Temp Table ------------------
    DECLARE @OutPut TABLE
        (
          Id INT IDENTITY(1, 1)
                 PRIMARY KEY ,
          Echo NVARCHAR(MAX) NOT NULL
        )
------------------------------------------------------
    SELECT  
--* , 
            @JobCommand = ' INSERT INTO [msdb].[dbo].[sysjobs] '
            + ' ([job_id],[originating_server_id],[name],[enabled],[description],    [start_step_id],[category_id] '
            + ' ,[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page] '
            + ' ,[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level] '
            + ' ,[date_created],[date_modified],[version_number]) '
            + ' VALUES ( ''' + CONVERT(VARCHAR(MAX), [job_id]) + ''','
            + +CONVERT(VARCHAR(MAX), [originating_server_id]) + ',' + ''''
            + [name] + ''',' + CONVERT(VARCHAR(MAX), [enabled]) + ','
            + '''' + [description] + ''','
            + CONVERT(VARCHAR(MAX), [start_step_id]) + ','
            + CONVERT(VARCHAR(MAX), [category_id]) + ','
            --? Convert varbinary to varchar
            --+CONVERT(VarChar(4000),[owner_sid])+','
            + '0x01' + ',' + CONVERT(VARCHAR(MAX), [notify_level_eventlog])
            + ',' + CONVERT(VARCHAR(MAX), [notify_level_email]) + ','
            + CONVERT(VARCHAR(MAX), [notify_level_netsend]) + ','
            + CONVERT(VARCHAR(MAX), [notify_level_page]) + ','
            + CONVERT(VARCHAR(MAX), [notify_email_operator_id]) + ','
            + CONVERT(VARCHAR(MAX), [notify_netsend_operator_id]) + ','
            + CONVERT(VARCHAR(MAX), [notify_page_operator_id]) + ','
            + CONVERT(VARCHAR(MAX), [delete_level]) + ',' + ''''
            + CONVERT(VARCHAR(MAX), [date_created]) + ''',' + ''''
            + CONVERT(VARCHAR(MAX), [date_modified]) + ''','
            + CONVERT(VARCHAR(MAX), [version_number]) + ' ) '
    FROM    msdb.dbo.sysjobs AS Job
    WHERE   Job.name = @JobName
---------------------------------------------------------------------------------------
    SET @JobCommand = ' ' + @JobCommand + ' '
    INSERT  INTO @OutPut
            ( Echo )
    VALUES  ( @JobCommand )
    SET @JobCommand = ' '
---------------------------------------------------------------------------------------
    SET @JobCommand = ' ' + @JobCommand + ' ';

    DECLARE @job_id [uniqueidentifier] 
    DECLARE @step_id [int] 
    DECLARE @step_name [sysname] 
    DECLARE @subsystem NVARCHAR(40) 
    DECLARE @command NVARCHAR(MAX) 
    DECLARE @flags [int] 
--Declare @additional_parameters [ntext] 
    DECLARE @additional_parameters NVARCHAR(MAX)  
    DECLARE @cmdexec_success_code [int] 
    DECLARE @on_success_action [tinyint] 
    DECLARE @on_success_step_id [int] 
    DECLARE @on_fail_action [tinyint] 
    DECLARE @on_fail_step_id [int] 
    DECLARE @server [sysname] 
    DECLARE @database_name [sysname] 
    DECLARE @database_user_name [sysname] 
    DECLARE @retry_attempts [int] 
    DECLARE @retry_interval [int] 
    DECLARE @os_run_priority [int] 
    DECLARE @output_file_name [nvarchar](200) 
    DECLARE @last_run_outcome [int] 
    DECLARE @last_run_duration [int] 
    DECLARE @last_run_retries [int] 
    DECLARE @last_run_date [int] 
    DECLARE @last_run_time [int] 
    DECLARE @proxy_id [int] 
    DECLARE @step_uid [uniqueidentifier] 


    DECLARE Cur CURSOR
    FOR
        SELECT  JobSteps.*
        FROM    msdb.dbo.sysjobsteps JobSteps
                INNER JOIN msdb.dbo.sysjobs Job ON Job.job_id = JobSteps.job_id
        WHERE   Job.name = @JobName
    OPEN Cur

    FETCH NEXT FROM Cur INTO @job_id, @step_id, @step_name, @subsystem,
        @command, @flags, @additional_parameters, @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,
        @step_uid 

    WHILE ( @@FETCH_STATUS = 0 ) 
        BEGIN

            SET @JobCommand = ' INSERT INTO [msdb].[dbo].[sysjobsteps] '
                + '([job_id],[step_id],[step_name],[subsystem],[command],[flags],    [additional_parameters] '
                + ' ,[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],[step_uid]) '
                + ' VALUES ( ''' + ISNULL(CONVERT(VARCHAR(MAX), @job_id),
                                          'Null') + ''','
                + ISNULL(CONVERT(VARCHAR(MAX), @step_id), 'Null') + ','
                + +'''' + ISNULL(CONVERT(VARCHAR(MAX), @step_name), 'Null')
                + ''',' + '''' + ISNULL(CONVERT(VARCHAR(MAX), @subsystem),
                                        'Null') + ''',' + '''';
            SET @JobCommand1 = ISNULL(CONVERT(VARCHAR(MAX), @command),
                                      'Null') + ''',';
            SET @JobCommand1 = REPLACE(@JobCommand1, CHAR(13) + CHAR(10),
                                       ' ')
            SET @JobCommand2 = ISNULL(CONVERT(VARCHAR(MAX), @flags),
                                      'Null') + ',' + ''''
                + ISNULL(CONVERT(VARCHAR(MAX), @additional_parameters),
                         'Null') + ''','
                + ISNULL(CONVERT(VARCHAR(MAX), @cmdexec_success_code),
                         'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @on_success_action), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), @on_success_step_id),
                               'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @on_fail_action), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), @on_fail_step_id),
                               'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @server), 'Null') + ','
                + +'''' + ISNULL(CONVERT(VARCHAR(MAX), @database_name),
                                 'Null') + ''',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), @database_user_name),
                         'Null') + ''','
                + ISNULL(CONVERT(VARCHAR(MAX), @retry_attempts), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), @retry_interval),
                               'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @os_run_priority), 'Null')
                + ',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), @output_file_name), 'Null')
                + ''',' + ISNULL(CONVERT(VARCHAR(MAX), @last_run_outcome),
                                 'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @last_run_duration), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), @last_run_retries),
                               'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @last_run_date), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), @last_run_time),
                               'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), @proxy_id), 'Null') + ','
                + +'''' + ISNULL(CONVERT(VARCHAR(MAX), @step_uid), 'Null')
                + '''' + ' )'

            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @JobCommand )
            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @JobCommand1 )
            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @JobCommand2 )

            FETCH NEXT FROM Cur INTO @job_id, @step_id, @step_name,
                @subsystem, @command, @flags, @additional_parameters,
                @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, @step_uid 
        END

    CLOSE Cur
    DEALLOCATE Cur      

---------------------------------------------------------------------------------------
    SET @JobCommand = @JobCommand + ' ';
    SET @JobCommand = ''
---------------------------------------------------------------------------------------
    SET @JobCommand = @JobCommand + ' ';
    DECLARE @Command_sysjobschedules NVARCHAR(MAX) = ''
    DECLARE @Comand_sysschedules NVARCHAR(MAX) = ''

    DECLARE Cur CURSOR
    FOR
        SELECT  Command_sysjobschedules = ' INSERT INTO [msdb].[dbo].[sysjobschedules]     '
                + ' ( [schedule_id],[job_id],[next_run_date],[next_run_time] ) '
                + ' VALUES ( ' + '@@IDENTITY , ' + ''''
                + CONVERT(VARCHAR(MAX), JobSch.job_id) + ''' , '
                + CONVERT(VARCHAR(MAX), JobSch.next_run_date) + ' , '
                + CONVERT(VARCHAR(MAX), JobSch.next_run_time) + ' ) ' ,
                Comand_sysschedules = ''
                + ' INSERT INTO [msdb].[dbo].[sysschedules]( '
                + ' [schedule_uid],[originating_server_id],[name],[owner_sid], '
                + ' [enabled],[freq_type],[freq_interval] '
                + ' ,[freq_subday_type],[freq_subday_interval], '
                + ' [freq_relative_interval],[freq_recurrence_factor] '
                + ' ,[active_start_date],[active_end_date],[active_start_time], '
                + ' [active_end_time],[date_created],[date_modified],[version_number]) '
                + 'VALUES ( ' + ''''
                + CONVERT(VARCHAR(MAX), Sch.schedule_uid) + ''',' + +''''
                + CONVERT(VARCHAR(MAX), Sch.originating_server_id) + ''','
                + +'''' + CONVERT(VARCHAR(MAX), Sch.name) + ''','
                + --Convert(VarChar(Max),Sch.owner_sid + ','+
                '0x01' + ',' + CONVERT(VARCHAR(MAX), Sch.enabled) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_type) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_interval) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_subday_type) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_subday_interval) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_relative_interval) + ','
                + CONVERT(VARCHAR(MAX), Sch.freq_recurrence_factor) + ','
                + CONVERT(VARCHAR(MAX), Sch.active_start_date) + ','
                + CONVERT(VARCHAR(MAX), Sch.active_end_date) + ','
                + CONVERT(VARCHAR(MAX), Sch.active_start_time) + ','
                + CONVERT(VARCHAR(MAX), Sch.active_end_time) + ',' + +''''
                + CONVERT(VARCHAR(MAX), Sch.date_created) + ''',' + +''''
                + CONVERT(VARCHAR(MAX), Sch.date_modified) + ''','
                + CONVERT(VARCHAR(MAX), Sch.version_number) + '  ) '
        FROM    msdb.dbo.sysjobschedules AS JobSch
                INNER JOIN msdb.dbo.sysjobs AS Job ON Job.job_id = JobSch.job_id
                INNER JOIN msdb.dbo.sysschedules AS Sch ON JobSch.schedule_id =     Sch.schedule_id
        WHERE   Job.name = @JobName

    OPEN Cur

    FETCH NEXT FROM Cur INTO @Command_sysjobschedules,
        @Comand_sysschedules

    DECLARE @Temp NVARCHAR(MAX) = ''

    WHILE ( @@FETCH_STATUS = 0 ) 
        BEGIN
    --Set @JobCommand = ' ' + @Comand_sysschedules+ ' ' +       @Command_sysjobschedules
    --Insert InTo @OutPut ( Echo ) Values ( @JobCommand )

            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @Comand_sysschedules )
            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @Command_sysjobschedules )

            FETCH NEXT FROM Cur INTO @Command_sysjobschedules,
                @Comand_sysschedules
        END

    SET @JobCommand = @JobCommand + ' ' + @Temp
    CLOSE Cur
    DEALLOCATE Cur      


---------------------------------------------------------------------------------------
    SET @JobCommand = @JobCommand + ' ';
    SET @JobCommand = ''
---------------------------------------------------------------------------------------
    SET @JobCommand = @JobCommand + ' ';

    DECLARE @Command_notifications VARCHAR(MAX)
    DECLARE @Command_operators VARCHAR(MAX)
    DECLARE @Command_sysalerts VARCHAR(MAX)

    DECLARE Cur CURSOR
    FOR
        SELECT  Command_notifications = ' INSERT INTO [msdb].[dbo].[sysnotifications] '
                + ' ([alert_id] ' + ' ,[operator_id] '
                + ' ,[notification_method]) ' + ' VALUES ' + ' ( '
                + '@Al,' + '@OP,'
                + CONVERT(VARCHAR(MAX), Noti.notification_method) + ' ) ' ,
                Command_operators = ' INSERT INTO [msdb].[dbo].[sysoperators] '
                + ' ( [name],[enabled],[email_address],[last_email_date],[last_email_time],[pager_address]'
                + ' ,[last_pager_date],[last_pager_time],[weekday_pager_start_time],[weekday_pager_end_time]'
                + ' ,[saturday_pager_start_time],[saturday_pager_end_time],[sunday_pager_start_time]'
                + ' ,[sunday_pager_end_time],[pager_days],[netsend_address],    [last_netsend_date]'
                + ' ,[last_netsend_time],[category_id]) ' + ' VALUES ( '
                + +'''' + CONVERT(VARCHAR(MAX), Ope.[name]) + ''','
                + CONVERT(VARCHAR(MAX), Ope.[enabled]) + ',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ope.[email_address]), '')
                + ''',' + CONVERT(VARCHAR(MAX), Ope.[last_email_date])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[last_email_time]) + ','
                + +'''' + ISNULL(CONVERT(VARCHAR(MAX), Ope.[pager_address]),
                                 '') + ''','
                + CONVERT(VARCHAR(MAX), Ope.[last_pager_date]) + ','
                + CONVERT(VARCHAR(MAX), Ope.[last_pager_time]) + ','
                + CONVERT(VARCHAR(MAX), Ope.[weekday_pager_start_time])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[weekday_pager_end_time])
                + ','
                + CONVERT(VARCHAR(MAX), Ope.[saturday_pager_start_time])
                + ','
                + CONVERT(VARCHAR(MAX), Ope.[saturday_pager_end_time])
                + ','
                + CONVERT(VARCHAR(MAX), Ope.[sunday_pager_start_time])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[sunday_pager_end_time])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[pager_days]) + ','
                + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ope.[netsend_address]), '')
                + ''',' + CONVERT(VARCHAR(MAX), Ope.[last_netsend_date])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[last_netsend_time])
                + ',' + CONVERT(VARCHAR(MAX), Ope.[category_id])
                + ' ) Set @OP = @@IDENTITY ' ,
                Command_sysalerts = 'INSERT INTO [msdb].[dbo].[sysalerts] '
                + ' ([name],[event_source],[event_category_id],[event_id],[message_id]     '
                + ' ,[severity],[enabled],[delay_between_responses],    [last_occurrence_date] '
                + ' ,[last_occurrence_time],[last_response_date],[last_response_time] '
                + ' ,[notification_message],[include_event_description],[database_name] '
                + ' ,[event_description_keyword],[occurrence_count],[count_reset_date] '
                + ' ,[count_reset_time],[job_id],[has_notification],[flags] '
                + ' ,[performance_condition],[category_id]) ' + ' VALUES '
                + ' ( ' + +'''' + CONVERT(VARCHAR(MAX), Ale.[name])
                + ''',' + +'''' + CONVERT(VARCHAR(MAX), Ale.[event_source])
                + ''','
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[event_category_id]),
                         'Null') + ','
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[event_id]), 'Null')
                + ',' + ISNULL(CONVERT(VARCHAR(MAX), Ale.[message_id]), '')
                + ',' + CONVERT(VARCHAR(MAX), Ale.[severity]) + ','
                + CONVERT(VARCHAR(MAX), Ale.[enabled]) + ','
                + CONVERT(VARCHAR(MAX), Ale.[delay_between_responses])
                + ',' + CONVERT(VARCHAR(MAX), Ale.[last_occurrence_date])
                + ',' + CONVERT(VARCHAR(MAX), Ale.[last_occurrence_time])
                + ',' + CONVERT(VARCHAR(MAX), Ale.[last_response_date])
                + ',' + CONVERT(VARCHAR(MAX), Ale.[last_response_time])
                + ',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[notification_message]),
                         '') + ''','
                + CONVERT(VARCHAR(MAX), Ale.[include_event_description])
                + ',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[database_name]), '')
                + ''',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[event_description_keyword]),
                         '') + ''','
                + CONVERT(VARCHAR(MAX), Ale.[occurrence_count]) + ','
                + CONVERT(VARCHAR(MAX), Ale.[count_reset_date]) + ','
                + CONVERT(VARCHAR(MAX), Ale.[count_reset_time]) + ','
                + +'''' + CONVERT(VARCHAR(MAX), Ale.[job_id]) + ''','
                + CONVERT(VARCHAR(MAX), Ale.[has_notification]) + ','
                + CONVERT(VARCHAR(MAX), Ale.[flags]) + ',' + +''''
                + ISNULL(CONVERT(VARCHAR(MAX), Ale.[performance_condition]),
                         '') + ''','
                + CONVERT(VARCHAR(MAX), Ale.[category_id])
                + +') Set @Al = @@IDENTITY '
        FROM    msdb.dbo.sysalerts AS Ale
                INNER JOIN msdb.dbo.sysnotifications AS Noti ON ( Ale.id =     Noti.alert_id )
                INNER JOIN msdb.dbo.sysoperators AS Ope ON ( Noti.operator_id = ope.id     )
                INNER JOIN msdb.dbo.sysjobs AS Job ON ( Job.job_id = Ale.job_id )
        WHERE   Job.name = 'aaJob'

    OPEN Cur

    FETCH NEXT FROM Cur INTO @Command_notifications, @Command_operators,
        @Command_sysalerts

    DECLARE @Temp1 NVARCHAR(MAX) = ''
    DECLARE @Temp2 NVARCHAR(MAX) = ''

    SET @JobCommand = ' Declare @OP int; Declare @Al int;  '
    INSERT  INTO @OutPut
            ( Echo )
    VALUES  ( @JobCommand )

    WHILE ( @@FETCH_STATUS = 0 ) 
        BEGIN
    --Set @JobCommand = ' ' + @Command_operators + ' ' + @Command_sysalerts + '     ' + @Command_notifications
    --Insert InTo @OutPut ( Echo ) Values ( @JobCommand )

            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @Command_operators )
            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @Command_sysalerts )
            INSERT  INTO @OutPut
                    ( Echo )
            VALUES  ( @Command_notifications )

            FETCH NEXT FROM Cur INTO @Command_notifications,
                @Command_operators, @Command_sysalerts
        END

    CLOSE Cur
    DEALLOCATE Cur

---------------------------------------------------------------------------------------
    SET @JobCommand = @JobCommand + ' ';
    SET @JobCommand = ''
---------------------------------------------------------------------------------------

    DECLARE @EchoField NVARCHAR(4000) = ''
    DECLARE @EchoCommand NVARCHAR(4000) = ''

    DECLARE Cur CURSOR
    FOR
        SELECT  RTRIM(LTRIM(Echo)) AS EchoCommand
        FROM    @OutPut
    OPEN Cur

    FETCH NEXT FROM Cur INTO @EchoField

    WHILE ( @@FETCH_STATUS = 0 ) 
        BEGIN
            SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
                + @JobName
                + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
                                  ''), ':', '') + '.txt'
    --Print @EchoCommand
            EXEC xp_cmdshell @EchoCommand
            FETCH NEXT FROM Cur INTO @EchoField
        END

    CLOSE Cur
    DEALLOCATE Cur

END

And for all DataBase in SQL Server use this Stored Procedure

Use msdb
Go

Create Procedure CreateBackUpFromAllJob
@Path VarChar(1000)  
As
Begin
Declare @JobName NVarChar(1000) = ''

Declare Cur1 Cursor For
    Select job.name 
    From sysjobs As job

Open Cur1

Fetch Next From Cur1 InTo @JobName

While( @@FETCH_STATUS = 0 )
    Begin
        Exec msdb.dbo.CreateBackUpFromJob @JobName , @Path
        Fetch Next From Cur1 InTo @JobName
    End

Close Cur1
Deallocate Cur1

End

this didnt work for me! I am still looking for a good and reliable proc that would script my jobs
@MarcelloMiorelli what was your problem? why did not this code work for you? what was your expectection and what was the result of this code?

freggel · Accepted Answer · 2010-07-30 07:41:37Z

3

After some further investigation I found a good script that did the job Generate-Scripts-for-SQL-Server-Objects

The code below is what I needed:

DECLARE @object int
exec sp_OACreate 'SQLDMO.SQLServer', @object OUT
exec sp_OASetProperty @object, 'LoginSecure', TRUE
exec sp_OAMethod @object,'Connect(sqltest)'
exec sp_OAMethod @object, 'Jobserver.Jobs().Script()'
exec sp_OADestroy @object

Thanks for the help anyway

answered Jul 30, 2010 at 7:41

freggel

5522 gold badges6 silver badges13 bronze badges

Comments

AlexanderT · Accepted Answer · 2020-04-24 09:23:11Z

It helped me:

Script All Sql Agent Jobs Using Powershell

http://www.dbascript.com/script-all-sql-agent-jobs-using-powershell

An easy way to script, document and backup all SQL Agent job using Powershell. The script will create a SQL job called "Backup SQL Agent Jobs" which will script all job and save the script in the specified location.

USE [msdb]
GO

/****** Object:  Job [Backup SQL Agent Jobs]   ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup SQL Agent Jobs', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup SQL Agent Jobs]     ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup SQL Agent Jobs', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'PowerShell', 
        @command=N'[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.Smo'') | Out-Null
$serverInstance = "DELL"

$server = New-Object (''Microsoft.SqlServer.Management.Smo.Server'') $serverInstance

$jobs = $server.JobServer.Jobs 
#$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}

if ($jobs -ne $null)
{

$serverInstance = $serverInstance.Replace("\", "-")

ForEach ( $job in $jobs )
{
$FileName = "C:\SQLBackup\SQLJobs\" + $serverInstance + "_" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Mike Fuller · Accepted Answer · 2022-03-12 00:20:45Z

I know this is an old post, but I wrote something similar today and figured I'd share it. I have a case where I need to move jobs from one server to another and remap the proxy, operator, email, target host, etc.

To use the proc just pass it a valid job name and what you want to adjust the input params to, and it kicks out the script. Keep in mind I created this rather quickly. May not be perfect yet, but should make life easier for someone out there :-)

CREATE PROCEDURE usp_scriptSQLjob_w_remapper 
(
    @nvJobFilter as NVARCHAR(150), 
    @nvProxy as NVARCHAR(50), 
    @nvNewOwner_login_name AS NVARCHAR(50), 
    @nvNewNotify_email_operator_name AS NVARCHAR(150),
    @nvNewServerName as NVARCHAR(50)
)
/**********************************************************************************************************************************/
/****** Name: usp_scriptSQLjob_w_remapper                                                                                    ******/
/****** Purpose: Maps the operator, proxy, email, ect in script for Migration.                                               ******/
/******                                                                                                                      ******/
/******                                                                                                                      ******/
/****** Input:      @nvJobFilter as NVARCHAR(150),  @nvProxy as NVARCHAR(50),   @nvNewOwner_login_name AS NVARCHAR(50),      ******/
/******             @nvNewNotify_email_operator_name AS NVARCHAR(150),                                                       ******/
/******             @nvNewServerName as NVARCHAR(50)                                                                         ******/
/****** Output:                                                                                                              ******/
/****** Rules:  Pass in the job Filter the values you would like to reassign Proxy, Owner, email, target server name    to   ******/
/******                                                                                                                      ******/
/****** Created by: Mike Fuller https://www.linkedin.com/in/michaelfuller333/                                                ******/
/****** Date Created: 03/10/2022                                                                                             ******/
/****** Created for: Fun                                                                                                     ******/
/**********************************************************************************************************************************/

AS
DECLARE @iPos AS INTEGER = 1, 
        @iRCount AS INTEGER,
        @nvJob_name AS NVARCHAR(150),
        @nvJobDescription AS NVARCHAR(1000), 
        @nvJobCategory_name AS NVARCHAR(150), 
        @nvJobOwner_login_name AS NVARCHAR(50) = @nvNewOwner_login_name, 
        @nvJobNotify_email_operator_name AS NVARCHAR(150) = @nvNewNotify_email_operator_name,
        @iJob_Enabled AS INT, 
        @iJobStartStep AS INT,
        @iJob_Notify_level_eventlog AS INT, 
        @iJob_Notify_level_email AS INT, 
        @iJob_Notify_level_netsend AS INT, 
        @iJob_Notify_level_page AS INT, 
        @iJob_Delete_level AS INT,
        @nvStep_name AS NVARCHAR(150),
        @nvStepProxy AS NVARCHAR(50) = @nvProxy, --You can map everything out like this with the new user here
        @iStep_id AS INT, 
        @iStep_cmdexec_success_code AS INT, 
        @iStep_on_success_action AS INT, 
        @iStep_on_success_step_id AS INT, 
        @iStep_on_fail_action AS INT, 
        @iStep_on_fail_step_id AS INT, 
        @iStep_retry_attempts AS INT, 
        @iStep_retry_interval AS INT , 
        @iStep_os_run_priority AS INT, 
        @nvStep_output_file_name AS NVARCHAR(1000),
        @nvStep_subsystem AS NVARCHAR(50), 
        @nvStep_command AS NVARCHAR(1000),--You could update your connection info to match the target configuration if you want.
        @nvStep_database_name AS NVARCHAR(150), 
        @iStep_flags AS INT,
        @nvScheduleName NVARCHAR(150),
        @iSchedule_enabled AS INT, 
        @iSchedule_freq_type AS INT, 
        @iSchedule_freq_interval AS INT, 
        @iSchedule_freq_subday_type AS INT, 
        @iSchedule_freq_subday_interval AS INT, 
        @iSchedule_freq_relative_interval AS INT, 
        @iSchedule_freq_recurrence_factor AS INT, 
        @iSchedule_active_start_date  AS INT, 
        @iSchedule_active_end_date AS INT, 
        @iSchedule_active_start_time AS INT, 
        @iSchedule_active_end_time AS INT, 
        @nvSchedule_uid AS NVARCHAR(50)

    --Get the job information so I can generate the script
    SELECT
        @nvJob_name = j.[name],
        @iJob_Enabled = j.[enabled], 
        @iJob_Notify_level_eventlog = j.notify_level_eventlog, 
        @iJob_Notify_level_email = Notify_level_email, 
        @iJob_Notify_level_netsend = Notify_level_netsend, 
        @iJob_Notify_level_page = Notify_level_page, 
        @iJob_Delete_level = Delete_level,
        --@nvJobOwner_login_name= su.name,--these you can easily add back if needed
        --@nvJobNotify_email_operator_name=(SELECT name from msdb.dbo.sysoperators so where so.id=j.notify_email_operator_id),
        @nvJobDescription=j.description, 
        @nvJobCategory_name=sc.name,
        @iJobStartStep= j.start_step_id,
        @nvStep_name = js.step_name,
        @iStep_id = js.step_id, 
        @iStep_cmdexec_success_code = js.cmdexec_success_code, 
        @iStep_on_success_action = js.on_success_action, 
        @iStep_on_success_step_id = js.on_success_step_id, 
        @iStep_on_fail_action = js.on_fail_action, 
        @iStep_on_fail_step_id = js.on_fail_step_id, 
        @iStep_retry_attempts = js.retry_attempts, 
        @iStep_retry_interval = js.retry_interval , 
        @iStep_os_run_priority = js.os_run_priority, 
        @nvStep_output_file_name= js.output_file_name,
        @nvStep_subsystem = js.subsystem, 
        @nvStep_command = js.command,
        @nvStep_database_name = js.database_name, 
        @iStep_flags = js.flags
FROM msdb.dbo.sysjobs j INNER JOIN  
     msdb.sys.sysusers su 
        ON su.sid = j.owner_sid INNER JOIN  
     msdb.dbo.syscategories sc
        ON j.category_id=sc.category_id LEFT JOIN 
     msdb.dbo.sysjobsteps js ON 
        j.job_id = js.job_id
WHERE 
   j.name = @nvJobFilter
ORDER BY 
   js.step_id DESC

--Get the count
SELECT @iRCount=@@ROWCOUNT

--Make the script... easy breezy
PRINT '/****** Object:  Job ['+ @nvJob_name + ']    Script Date: ' + CAST(Getdate() AS NVARCHAR(20)) + ' ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [' + @nvJobCategory_name + ']    Script Date: ' + CAST(Getdate() AS NVARCHAR(20)) + ' ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+ @nvJobCategory_name +''' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''' + @nvJobCategory_name +'''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
'
PRINT 'DECLARE @jobId BINARY(16)'
PRINT 'EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N''' + @nvJob_name + ''', '
    PRINT'      @enabled='+ CAST(@iJob_Enabled AS NVARCHAR(50)) +', '
    PRINT'      @notify_level_eventlog=' +CAST(@iJob_Notify_level_eventlog AS NVARCHAR(50))+', '
    PRINT'      @notify_level_email='+ CAST(@iJob_Notify_level_email AS NVARCHAR(50))+', '
    PRINT'      @notify_level_netsend='+CAST(@iJob_Notify_level_netsend AS NVARCHAR(50))+', '
    PRINT'      @notify_level_page='+CAST(@iJob_Notify_level_page AS NVARCHAR(50))+', '
    PRINT'      @delete_level='+CAST(@iJob_Delete_level AS NVARCHAR(50))+', '
    PRINT'      @description=N''' + @nvJobDescription +''', '
    PRINT'      @category_name=N''' + @nvJobCategory_name+ ''', '
    PRINT'      @owner_login_name=N''' + @nvJobOwner_login_name +''', '
    PRINT'      @notify_email_operator_name=N'''+ @nvJobNotify_email_operator_name + ''', @job_id = @jobId OUTPUT'
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' --always in case we fail


WHILE @iPos <= @iRCount 
    BEGIN
    PRINT'/****** Object:  Step ['''+@nvStep_name +''']    Script Date: ' + CAST(Getdate() AS NVARCHAR(20)) + ' ******/'
    PRINT'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'''+@nvStep_name +''', '
    PRINT'      @step_id='+ CAST (@iStep_id AS NVARCHAR(50))+','
    PRINT'      @cmdexec_success_code='+CAST(@iStep_cmdexec_success_code AS NVARCHAR(50))+', '
    PRINT'      @on_success_action='+CAST(@iStep_on_success_action AS NVARCHAR(50))+','
    PRINT'      @on_success_step_id='+ CAST(@iStep_on_success_step_id AS NVARCHAR(50))+', '
    PRINT'      @on_fail_action='+CAST (@iStep_on_fail_action AS NVARCHAR(50))+', '
    PRINT'      @on_fail_step_id='+CAST (@iStep_on_fail_step_id AS NVARCHAR(50))+', '
    PRINT'      @retry_attempts='+CAST (@iStep_retry_attempts AS NVARCHAR(50))+', '
    PRINT'      @retry_interval='+CAST (@iStep_retry_interval AS NVARCHAR(50))+', '
    PRINT'      @os_run_priority='+CAST (@iStep_os_run_priority  AS NVARCHAR(50))+', @subsystem=N'''+ @nvStep_subsystem + ''','
    PRINT'      @command=N'''+@nvStep_command+''', '
    PRINT'      @database_name=N'''+ @nvStep_database_name +''','
    IF ISNULL(@nvStep_output_file_name, 1)<>1 PRINT'        @output_file_name=N'''+ @nvStep_output_file_name +''',
    'IF @nvStep_subsystem ='SSIS' PRINT'      @proxy_name=N'''+ @nvStepProxy + ''','  
    PRINT'      @flags='+CAST (@iStep_flags  AS NVARCHAR(50)) 
        
    PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' --always in case we fail
    SET @iPos= @iPos + 1

    SELECT
        @nvStep_name = js.step_name,
        @iStep_id = js.step_id, 
        @iStep_cmdexec_success_code = js.cmdexec_success_code, 
        @iStep_on_success_action = js.on_success_action, 
        @iStep_on_success_step_id = js.on_success_step_id, 
        @iStep_on_fail_action = js.on_fail_action, 
        @iStep_on_fail_step_id = js.on_fail_step_id, 
        @iStep_retry_attempts = js.retry_attempts, 
        @iStep_retry_interval = js.retry_interval , 
        @iStep_os_run_priority = js.os_run_priority, 
        @nvStep_subsystem = js.subsystem, 
        @nvStep_command = js.command,
        @nvStep_database_name = js.database_name, 
        @iStep_flags = js.flags,
        @nvStep_output_file_name = js.output_file_name
    FROM msdb.dbo.sysjobs j INNER JOIN
         msdb.sys.sysusers su ON
            su.sid = j.owner_sid INNER JOIN
         msdb.dbo.syscategories sc ON 
            j.category_id=sc.category_id LEFT JOIN 
         msdb.dbo.sysjobsteps js ON 
            j.job_id = js.job_id
    WHERE 
       j.name= @nvJob_name AND 
       js.step_id = @iPos
    ORDER BY js.step_id DESC

    END

SELECT
        @nvScheduleName = s.[name],
        @iSchedule_enabled =  s.[enabled], 
        @iSchedule_freq_type =  s.freq_type, 
        @iSchedule_freq_interval =  s.freq_interval, 
        @iSchedule_freq_subday_type =  s.freq_subday_type, 
        @iSchedule_freq_subday_interval =  s.freq_subday_interval, 
        @iSchedule_freq_relative_interval =  s.freq_relative_interval, 
        @iSchedule_freq_recurrence_factor =  s.freq_recurrence_factor, 
        @iSchedule_active_start_date  =  s.active_start_date, 
        @iSchedule_active_end_date =  s.active_end_date, 
        @iSchedule_active_start_time =   s.active_start_time, 
        @iSchedule_active_end_time =  s.active_end_time, 
        @nvSchedule_uid =  s.schedule_uid
FROM  msdb.dbo.sysschedules s INNER JOIN 
      msdb.dbo.sysjobschedules js ON  
        js.schedule_id = s.schedule_id INNER JOIN 
      msdb.dbo.sysjobs j ON
        js.job_id=j.job_id
WHERE j.name = @nvJob_name
     --Get the count
SELECT @iRCount=@@ROWCOUNT

--restart loop
SET @iPos = 1


PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = ' + CAST(@iJobStartStep AS NVARCHAR(2)) + '
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
WHILE @iPos <= @iRCount 
    BEGIN
        PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + @nvScheduleName + ''', '
        PRINT'      @enabled='+CAST(@iSchedule_enabled AS NVARCHAR(50))+', '
        PRINT'      @freq_type='+CAST(@iSchedule_freq_type AS NVARCHAR(50))+', '
        PRINT'      @freq_interval='+CAST(@iSchedule_freq_interval AS NVARCHAR(50))+', '
        PRINT'      @freq_subday_type='+CAST(@iSchedule_freq_subday_type AS NVARCHAR(50))+', '
        PRINT'      @freq_subday_interval='+CAST(@iSchedule_freq_subday_interval AS NVARCHAR(50))+', '
        PRINT'      @freq_relative_interval='+CAST(@iSchedule_freq_relative_interval AS NVARCHAR(50))+', '
        PRINT'      @freq_recurrence_factor='+CAST(@iSchedule_freq_recurrence_factor AS NVARCHAR(50))+', '
        PRINT'      @active_start_date='+CAST(@iSchedule_active_start_date AS NVARCHAR(50))+', '
        PRINT'      @active_end_date='+CAST(@iSchedule_active_end_date AS NVARCHAR(50))+', '
        PRINT'      @active_start_time='+CAST(@iSchedule_active_start_time AS NVARCHAR(50))+', '
        PRINT'      @active_end_time='+CAST(@iSchedule_active_end_time AS NVARCHAR(50))+', '
        PRINT'      @schedule_uid=N''' + @nvSchedule_uid + ''''
        PRINT'      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

SET @iPos=@iPos + 1
END

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''' + @nvNewServerName + ''''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO'

How this helps someone out there :-)

the join on sysusers may miss some logins that exist at the server level but haven't been created as users in the MSDB database. Best to use sys.server_pricipals.

Jeremy Caney · Accepted Answer · 2023-01-10 02:15:24Z

0

It's not automatic, but you could run the following across all of your database servers using a central management server:

select @@servername, j.name, js.step_id, js.step_name, js.command
from sysjobs j inner join sysjobsteps js
on j.job_id = js.job_id
order by j.name, js.step_id

You could create a job on all servers to dump the output to a central location.

edited Jan 10, 2023 at 2:15

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered Jan 9, 2023 at 4:17

Charles Candale

312 bronze badges

1 Comment

CaM Over a year ago

This won't capture the schedules.

Mr Shoubs · Accepted Answer · 2010-07-29 10:58:10Z

-1

I maintain files of sql for each table then use cygwin to run a shell script that cats them together then run it on the server.

#!/bin/bash
# Please add new creation scripts and meta data scripts to the script below.
# To be able to run this script don't forget to chmod 744 it.
# Make sure the create script are saved using the ANSI encoding and not unicode etc.

master_script="create_all.sql"
#path="C:\\"

#cd $path

cat *.sql >$master_script

notepad $master_script

#rm $master_script

answered Jul 29, 2010 at 10:58

Mr Shoubs

15.6k18 gold badges72 silver badges110 bronze badges

3 Comments

freggel Over a year ago

I think you misunderstood the question, I'm not looking for a method to execute the script, but I'm looking for a script that generate the create statements of all my sql jobs, this by a stored procedure that I can trigger with an osql statement. I need this to backup my sql jobs as a script.

Mr Shoubs Over a year ago

Bit harsh that I get -1 when your question isn't particularly clear. - it sounds like you DON'T have the sql tables already created and expect the to magically appear

Igor Borisenko Over a year ago

It would be fair to upvote this downvote. Although, after so many years :)

Collectives™ on Stack Overflow

Automatically create scripts for all SQL Server Jobs

7 Answers 7

Comments

2 Comments

Comments

Comments

1 Comment

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

Comments

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related