Skip to content

add-steps-to-a-sql-server-agent-master-job.md

Latest commit

 

History

History
85 lines (58 loc) · 3.33 KB

add-steps-to-a-sql-server-agent-master-job.md

File metadata and controls

85 lines (58 loc) · 3.33 KB
title Add Steps to a SQL Server Agent Master Job
ms.custom seo-lt-2019
ms.date 01/19/2017
ms.prod sql
ms.prod_service sql-tools
ms.reviewer
ms.technology ssms
ms.topic conceptual
ms.assetid 9cc1e8ab-7ddc-427b-859e-203aa7e24642
author markingmyname
ms.author maghan

Add Steps to a SQL Server Agent Master Job

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] This topic describes how to add steps to a SQL Server Agent master job in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Topic

Before You Begin

Limitations and Restrictions

A [!INCLUDEssNoVersion] 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 [!INCLUDEssDE].

  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).