Edit

Share via

Create a PowerShell Script Job Step

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

This topic describes how to create and define a SQL Server Agent job step that executes a PowerShell script in SQL Server by using SQL Server Management Studio or Transact-SQL.

Before You Begin

Security

For detailed information, see Implement SQL Server Agent Security.

We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. You can click the This page button in the Feedback section at the bottom of this page. We read every item of feedback about SQL, typically the next day. Thanks.

Using SQL Server Management Studio

To create a PowerShell Script job step

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties. For more information on creating a job, see Creating Jobs.

  3. In the Job Properties dialog, click the Steps page, and then click New.

  4. In the New Job Step dialog, type a job Step name.

  5. In the Type list, click PowerShell.

  6. In the Run as list, select the proxy account with the credentials that the job will use.

  7. In the Command box, enter the PowerShell script syntax that will be executed for the job step. Alternately, click Open and select a file containing the script syntax. For an example of a PowerShell script, see Using Transact-SQL below.

  8. Click the Advanced page to set the following job step options: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and how often retry attempts should be made.

Using Transact-SQL

To create a PowerShell Script job step

  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 PowerShell job step that finds the processes
-- that use more than 1000 MB of memory and kills them  
USE msdb;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_name = N'Kills all processes that use more than 1000 MB of memory',  
    @subsystem = N'PowerShell',  
    @command = N'Get-Process | Where-Object { $_.WS -gt 1000MB } | Stop-Process',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO

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

Using SQL Server Management Objects

To create a PowerShell Script job step

Use the JobStep class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell.