Edit

Share via

Add Steps to a SQL Server Agent Master Job

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

In This Topic

Before You Begin

Limitations and Restrictions

A SQL Server Agent master job cannot be targeted at both local and remote servers.

Security

Permissions

Unless you are a member of the sysadmin fixed server role, you can only modify jobs that you own. For detailed information, see Implement SQL Server Agent Security.

Using SQL Server Management Studio

To add steps to a SQL Server Agent master job

  1. In Object Explorer, click the plus sign to expand the server that contains the job to which you want to add steps.

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

  3. Click the plus sign to expand the Jobs folder.

  4. Right-click the job to which you want to add steps and select Properties.

  5. In the Job Properties -job_name dialog box, under Select a page, select Steps. For more information on the available options on this page, see Job Properties - New Job (Steps Page).

  6. When finished, click OK.

Using Transact-SQL

To add steps to a SQL Server Agent master 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.

    -- creates a job step that changes database access to read-only for the Sales database.   
-- specifies 5 retry attempts, with each retry to occur after a 5 minute wait.   
-- assumes that the Weekly Sales Data Backup job already exists  
USE msdb;  
GO  
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

For more information, see sp_add_jobstep (Transact-SQL).