Adding SQL Server jobs using PowerShell

A sudden requirement for a new SQL Server agent job to be put on to every instance in our Enterprise was hardly greeted with joy. At least by those who hadn’t been playing with PowerShell.

Just like everything else in SQL Server, we can automate SMO to achieve our ends. And once we’ve got it working on one SQL Sever instance, it’s simple to extend this to hundreds.

So, to begin with a single instace:

#Connect to our SQL Sever Instance:
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("MyServer\Instance1")

#As this is a new job, we create a new object and then create it. If you look on your SQL instance now you'll see a job without steps, schedules or notifications
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $SQLSvr.JobServer, "Example_Job"
$SQLJob.Create()

#Now we add a step to our Job so it will actually do some work. Again as this is a new job step,
$SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step"
#For this example, we'll do something simple and foolproof........
$SQLJobStep.Command = "select * from sys.databases"
$SQLJobStep.DatabaseName = "master"
$SQLJobStep.Create()

#Now add a schedule to our job to finish it off
$SQLJobSchedule =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $SQLJob, "Example_Job_Schedule"

#Need to use the built in types for Frequency, in this case we'll run it every day
$SQLJobSchedule.FrequencyTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
#As we've picked daily, this will repeat every day
$SQLJobSchedule.FrequencyInterval = 1

#Need to tell SQL when during the day we want to acutally run it. This is a timespan base on 00:00:00 as the start,
#Here we're saying to run it at 13:30. You could combine these lines, but I've left them seperate to make it easier to read.
$TimeSpan1 = New-TimeSpan -hours 13 -minutes 30
$SQLJobSchedule.ActiveStartTimeofDay = $TimeSpan1

#Set the job to be active from now
$SQLJobSchedule.ActiveStartDate = get-date
$SQLJobSchedule.create()

And there we have it, a nice simple job built on our SQL Server instance. Now, what happens if we wanted to push that out to a whole load of instances. That’s easy, assuming you’ve a reliable list of your SQL instances lying around:

$ServerList = Get-Content c:\ListOfServers.txt
foreach ($srv in $ServerList)
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($srv)

    $SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist    $SQLSvr.JobServer, "Example_Job"
    $SQLJob.Create()

    $SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step"
    $SQLJobStep.Command = "select * from sys.databases"
    $SQLJobStep.DatabaseName = "master"
    $SQLJobStep.Create()

    $SQLJobSchedule =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $SQLJob, "Example_Job_Schedule"

    $SQLJobSchedule.FrequencyTypes =  "Daily"
    $SQLJobSchedule.FrequencyInterval = 1

    $TimeSpan1 = New-TimeSpan -hours 13 -minutes 30
    $SQLJobSchedule.ActiveStartTimeofDay = $TimeSpan1

    $SQLJobSchedule.ActiveStartDate = get-date
    $SQLJobSchedule.create()
}

And you’ve just deployed an identical SQL Server Agent job to every machine in your list. Suddenly that rush job doesn’t look quite so much like a right click hell.

But, what happens when the spec changes (because the requirements are always right the first time aren’t they?). Well, we can modify our jobs as well. In this example we’re going to change the T-SQL that’s going to be run, add another Job Step, and put in some basic control logic

Again, we’ll start with a single server instance:

#Connect to our instance
$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("MyServer\Instance1")

#As our job already exists, we can just assign it
$SQLJob = $sqlsvr.JobServer.jobs["Example_Job"]

#Now we modify our step to our Job. Again as this step already exists we can just assign it rather than creating a new object
$SQLJobStep = $sqljob.JobSteps["Example_Job_Step"]
$SQLJobStep.Command = "select * from sys.master_files"
$SQLJobStep.DatabaseName = "master"
$SQLJobStep.alter()

#Now we add a 2nd job step to our job. As this is a new step, we create a new object
$SQLJobStep2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step2"
$SQLJobStep2.Command = "select * from sys.databases"
$SQLJobStep2.DatabaseName = "master"
$SQLJobStep2.Create()

#Now we have multiple steps, we need to tell SQL Server Agent what to do when things succeed or fail
$SQLJobStep.OnSuccessAction = "GoToStep"
$SQLJobStep.OnSuccessStep=2
$SQLJobStep.OnFailAction = "QuitWithFailure"

#Finally we'll alter our job to save the changes
$SQLJobStep.Alter()

And again, applying that to a number of SQL instances, is as simple as wrapping it in a foreach loop:

$ServerList = Get-Content c:\ListOfServers.txt
foreach ($srv in $ServerList)
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($srv)

    $SQLJob = $sqlsvr.JobServer.jobs["Example_Job"]

    $SQLJobStep = $sqljob.JobSteps["Example_Job_Step"]
    $SQLJobStep.Command = "select * from sys.master_files"
    $SQLJobStep.DatabaseName = "master"
    $SQLJobStep.alter()

    $SQLJobStep2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step2"
    $SQLJobStep2.Command = "select * from sys.databases"
    $SQLJobStep2.DatabaseName = "master"
    $SQLJobStep2.Create()

    $SQLJobStep.OnSuccessAction = "GoToStep"
    $SQLJobStep.OnSuccessStep=2
    $SQLJobStep.OnFailAction = "QuitWithFailure"

    $SQLJobStep.Alter()
}

And there we go. A nice simple way of adding complex multi-step SQL agent jobs to lots of servers with minimal effort.