Allen White

Create Agent Jobs to run PowerShell Scripts

Actually, let's use PowerShell and SMO to create an Agent job which will run a PowerShell script. (Or is that a circular reference?)

I've created a number of PowerShell scripts which automate database management processes, and I wanted to be able to run them from an Agent job. The trick, of course, is to be able to get to the PowerShell environment from within Agent. It's not as hard as I thought. Let's say I have a script to back up my SQL Server user databases, and that script is in C:\Admin and it's called backupdb.ps1. The command from a Windows command window is this:

powershell "& c:\Admin\backupdb.ps1"

Try it - it's a lot easier than I'd thought. Once you have that little tidbit it's just a matter of setting up the job to execute that exact command line from within an Agent job. First, we need to load the SMO dll, and connect to the SQL Server where we want the job to run.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next we instantiate an Agent Job object, set its properties, and create it.

$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($s.JobServer, 'FullBackup')
$j.Description = 'Backup User Databases'
$j.Category = '[Uncategorized (Local)]'
$j.OwnerLoginName = 'sa'
$j.Create()

Once the job exists we can create the step to execute the PowerShell script. By default the step default SubSystem is TransactSQL, so we have to specify that we want the command shell, so we set the JobStep.SubSystem property to 'CmdExec' and the command property to the powershell command we looked at earlier.

$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j, 'Step 01')
$js.SubSystem = 'CmdExec'
$js.Command = 'powershell "& C:\Admin\backupdb.ps1"'
$js.OnSuccessAction = 'QuitWithSuccess'
$js.OnFailAction = 'QuitWithFailure'
$js.Create()

The job needs to be scheduled so we create a JobSchedule object and set its properties. The time of day properties require a System.Timespan object, so we have to instantiate a couple of those, one for the start time and one for the end time, but that's pretty straightforward.

$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j, 'Sched 01')
$jsch.FrequencyTypes = 'Daily'
$jsch.FrequencySubDayTypes = 'Once'
$startts = new-object System.Timespan(2, 0, 0)
$jsch.ActiveStartTimeOfDay = $startts
$endts = new-object System.Timespan(23, 59, 59)
$jsch.ActiveEndTimeOfDay = $endts
$jsch.FrequencyInterval = 1
$jsch.ActiveStartDate = get-date
$jsch.Create()

Once this is done the job will run every day at 2am and execute the backupdb.ps1 script we created for this purpose.

Allen