As of this writing, the most recent version of the SQLServer PowerShell module (which installs as part of SQL Server Management Studio) includes cmdlets for retrieving information about SQL agent jobs, but no cmdlets for starting them.
1 | Get-Command -Module SQLServer -Name *job* |
I recently ran into a situation where I needed to start a SQL agent job from PowerShell. The solution needed to be a tool that others could use who may or may not have the SQLServer module, SQLPS module or older SQL Server snap-in installed.
I decided to write a function to leverage the .NET Framework from PowerShell to start a SQL Server job:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | #Requires -Version 3.0 function Start-MrSqlAgentJob { <# .SYNOPSIS Starts the specified SQL Agent Job on the specified target instance of SQL Server. .DESCRIPTION Start-MrSqlAgentJob is a PowerShell function that is designed to start the specified SQL Server Agent job on the specified target instance of SQL Server without requiring the SQL Server PowerShell module or snap-in to be installed. .PARAMETER ServerInstance The name of an instance of SQL Server where the SQL Agent is running. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName. .PARAMETER Name Specifies the name of the Job object that this cmdlet gets. The name may or may not be case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running. .PARAMETER Credential SQL Authentication userid and password in the form of a credential object. .EXAMPLE Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history .EXAMPLE Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential) .EXAMPLE 'syspolicy_purge_history' | Start-MrSqlAgentJob -ServerInstance SQLServer01 .INPUTS String .OUTPUTS Boolean .NOTES Author: Mike F Robbins Website: http://mikefrobbins.com Twitter: @mikefrobbins #> [CmdletBinding()] param ( [Parameter(Mandatory)] [string]$ServerInstance, [Parameter(Mandatory, ValueFromPipeLine)] [string]$Name, [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty ) BEGIN { $Database = 'msdb' $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection if (-not($PSBoundParameters.Credential)) { $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;" } else { $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=False;" $userid = $Credential.UserName -replace '^.*\\|@.*$' ($password = $credential.Password).MakeReadOnly() $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password) $connection.Credential = $sqlCred } $connection.ConnectionString = $connectionString $ErrorActionPreference = 'Stop' try { $connection.Open() Write-Verbose -Message "Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened." } catch { Write-Error -Message "An error has occurred. Error details: $($_.Exception.Message)" } $ErrorActionPreference = 'Continue' $command = $connection.CreateCommand() } PROCESS { $Query = "EXEC dbo.sp_start_job N'$Name'" $command.CommandText = $Query $ErrorActionPreference = 'Stop' try { $result = $command.ExecuteNonQuery() } catch { Write-Error -Message "An error has occured. Error Details: $($_.Exception.Message)" } $ErrorActionPreference = 'Continue' if ($result -eq -1) { Write-Output $true } else { Write-Output $false } } END { $connection.Close() $connection.Dispose() } } |
The job returns a Boolean. True means it started successfully and false means it failed to start:
1 | Start-MrSqlAgentJob -ServerInstance SQL011 -Name syspolicy_purge_history |
The Start-MrSqlAgentJob function shown in the previous code example can be downloaded from my SQL repository on GitHub.
Update
Thanks to Rob Sewell for pointing out that Get-SqlAgentJob returns a SMO object which has a start method:
1 | Get-SqlAgentJob -ServerInstance SQL011 -Name test | Get-Member -MemberType Method |
That means it can be used to start a SQL agent job:
1 2 | (Get-SqlAgentJob -ServerInstance SQL011 -Name test).Start() Get-SqlAgentJob -ServerInstance SQL011 -Name test |
It does require that SQL Server Management Studio 2016 be installed on the machine it’s being run from.
µ
Good work
(Get-SQLAgentJob -Instance Server -Name Jobnane).start() will work and stop too as GetSqlagentjob returns a smo object