Create a SQL Server Agent Master Job

  • Article

This topic describes how to create a master Microsoft SQL Server Agent job in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

  • Before you begin:

    Limitations and Restrictions

    Security

  • To create a master SQL Server Agent job, using:

    SQL Server Management Studio

    Transact-SQL

Before You Begin

Limitations and Restrictions

Changes to master SQL Server Agent jobs must be propagated to all involved target servers. Because target servers do not initially download a job until those targets are specified, Microsoft recommends that you complete all job steps and job schedules for a particular job before you specify any target servers. Otherwise, you must manual request that the target servers download the modified job again, either by executing the sp_post_msx_operation stored procedure or modifying the job using SQL Server Management Studio. For more information, see sp_post_msx_operation (Transact-SQL) or Modify a Job.

Security

Permissions

Distributed jobs that have steps which are associated with a proxy run under the context of the proxy account on the target server. Make sure that the following conditions are met or job steps that are associated with a proxy will not be downloaded from the master server to the target:

  • The registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) is set to 1 (true). By default, this subkey is set to 0 (false).

  • A proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

If job steps that use proxy accounts fail when downloading them from the master server to the target server, you can check the error_message column in the sysdownloadlist table in the msdb database for the following error messages:

  • "The job step requires a proxy account, however proxy matching is disabled on the target server." To resolve this error, set the AllowDownloadedJobsToMatchProxyName registry subkey to 1.

  • "Proxy not found." To resolve this error, make sure a proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

Using SQL Server Management Studio

To create a master SQL Server Agent job

  1. In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.

  2. Click the plus sign to expand SQL Server Agent.

  3. Right-click the Jobs folder and select New Job….

  4. In the New Job dialog box, on the General page, modify the general properties of the job. For more information on the available options on this page, see Job Properties / New Job (General Page)

  5. On the Steps page, organize the job steps. For more information on the available options on this page, see Job Properties / New Job (Steps Page)

  6. On the Schedules page, organize schedules for the job. For more information on the available options on this page, see Job Properties / New Job (Schedules Page)

  7. On the Alerts page, organize the alerts for the job. For more information on the available options on this page, see Job Properties / New Job (Alerts Page)

  8. On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes. For more information on the available options on this page, see Job Properties / New Job (Notifications Page).

  9. On the Targets page, manage the target servers for the job. For more information on the available options on this page, see Job Properties / New Job (Targets Page).

  10. When finished, click OK.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To create a master SQL Server Agent job

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE msdb ;
GO
-- Adds a new job executed by the SQLServerAgent service called 'Weekly Sales Data Backup'
EXEC dbo.sp_add_job
    @job_name = N'Weekly Sales Data Backup' ;
GO
-- Adds a step (operation) to the 'Weekly Sales Data Backup' job.
EXEC sp_add_jobstep
    @job_name = N'Weekly Sales Data Backup',
    @step_name = N'Set database to read only',
    @subsystem = N'TSQL',
    @command = N'ALTER DATABASE SALES SET READ_ONLY', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
-- Creates a schedule called RunOnce
EXEC dbo.sp_add_schedule
    @schedule_name = N'RunOnce',
    @freq_type = 1,
    @active_start_time = 233000 ;
USE msdb ;
GO
-- Sets the 'RunOnce' schedule to the "Weekly Sales Data Backup' Job
EXEC sp_attach_schedule
   @job_name = N'Weekly Sales Data Backup',
   @schedule_name = N'RunOnce';
GO
-- assigns the multiserver job Weekly Sales Backups to the server SEATTLE2
-- assumes that SEATTLE2 is registered as a target server for the current instance.
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Sales Data Backups',
    @server_name = N'SEATTLE2' ;
GO

For more information, see:

Arrow icon used with Back to Top link [Top]