SQL Server Agent: run a PowerShell script from a job
Two ways to run a PowerShell script in a SQL Server Agent job step, with error handling.
This page shows two ways to run a PowerShell script from a SQL Server Agent job step, plus a few commands useful to prepare the environment.
Run the script in a separate PowerShell process
The script is run by a new powershell.exe. With $ErrorActionPreference = "Stop" and Throw, an error makes the step fail.
$ErrorActionPreference = "Stop"
try
{
Set-Location C:\PowerShell\Scripts
powershell.exe -c "C:\PowerShell\Scripts\refresh.ps1"
}
catch
{
Throw
}
Run the script in the current session
The script is loaded by dot sourcing (.), without an extra process.
$ErrorActionPreference = "Stop"
try
{
Set-Location C:\PowerShell\Scripts
. C:\PowerShell\Scripts\refresh.ps1
}
catch
{
Throw
}
Other useful commands
Call the PowerShell executable directly with a script:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe D:\Scripts\PowerShell\Restore.ps1
Load a functions file:
. D:\PS\Functions.ps1
Allow local scripts for the current user:
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser -Force
Install the NTFSSecurity module at a specific version:
Install-Module -Name NTFSSecurity -RequiredVersion 4.2.4