Building a SQL Agent job using PowerShell and SMO

15 Aug, 2023

SQL Server Agent is a native SQL Server platform for automating database administrative tasks such as backup, index maintenance, event monitoring, data purging and more. A scheduled administrative task is called a job. In this article, I'll show you how to programmatically create a job to check the database integrity using PowerShell and SMO.

SQL Management Objects (SMO), according to the official website, are objects used for programmatic management of SQL Server. It is an API for scripting and managing SQL Server and Azure SQL Database. SMO is open source and its repo is here.

Pre-requisite

As a first step, we need to load the SMO assemblies into PowerShell. There are two ways to do it:

Using SQLPS module - it is included with the SQL Server installation (for backward compatibility) but is no longer updated.
Using SqlServer module - it is the current PowerShell module to use which contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features. For this article we will use the SqlServer module. To install the SqlServer module from the PowerShell Gallery, we need to run the bellow:

Install-Module SqlServer

To check if the module was installed successfully, run it:

Get-Module -ListAvailable -Name SqlServer

Once installed, load the SqlServer module in the PowerShell session:

Import-Module SqlServer

1. Create the job

To create the job, we first connect to the SQL Server instance by creating a new Server object which receives as parameter the variable with the server name, $serverName. Then we assign it to the $server variable:

$server = [Microsoft.SqlServer.Management.Smo.Server]::New("SERVER-1")

Next, we create the job at the connected instance by creating a new Job object which receives as parameters the $server.JobServer and the job name string, $jobName. Here we assign it to the $job variable:

$job = [Microsoft.SqlServer.Management.Smo.Agent.Job]::New($server.JobServer, "Check database integrity for AdventureWorks")

After that, we specify the Description, OwnerLoginName, Category properties of the $job object and then create the job by calling the $job.Create() method:

$job.Description = 'This is a job that was created by using PowerShell and SMO'
$job.OwnerLoginName = 'sa'
$job.Category = "Database Maintenance"
$job.Create()

2. Create the job step

With the job created, let's add a job step to it. In this case, we are adding a step to perform a database integrity check routine with the DBCC CHECKDB command. To do this, we create a new JobStep object which receives as parameters the $j o b o b j e c t a n d t h e s t r i n g n a m e o f t h e j o b s t e p, " R u n a g e n t " . T h e n a s s i g n i t t o t h e `$ jobStep` variable.

$jobStep = [Microsoft.SqlServer.Management.Smo.Agent.JobStep]::new($job, "Run agent")

We enter the SubSystem, Command, OnSuccessAction and OnFailAction properties of the JobStep object and the create the job step calling the $jobStep.Create() method:

$jobStep.SubSystem = 'TransactSql'
$jobStep.Command = "DBCC CHECKDB ('AdventureWorks') WITH NO_INFOMSGS" 
$jobStep.OnSuccessAction = 'QuitWithSuccess'
$jobStep.OnFailAction = 'QuitWithFailure'
$jobStep.Create()

3. Apply the job to SQL Server instance

An important step is to apply the job to the SQL Server instance we have connected:

$job.ApplyToTargetServer($server.DomainInstanceName.ToString())
$job.StartStepID = $jobStep.ID
$job.Alter()

4. Create the job schedule

The last step is to schedule the job to run every week on Saturday at 2:00:00 AM. To do this, we create a new JobSchedule object which receives the $job object and the string name of the job schedule, "Sched 01". Then assign it to the $jobSchedule variable:

$jobSchedule = [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]::New($job, "Sched 01")

We enter the JobSchedule object's properties to define the time and frequency of our job. In this case, the job will run on Saturdays at 2:00 am.

TIP: To set a time/frequency of your choice, consult the sp_add_schedule documentation to choose the correct property values.

$ts = "20000".PadLeft(6, '0')
$ActiveStartTimeOfDay = [timespan]::ParseExact($ts, 'hhmmss', $null)
$jobSchedule.ActiveStartTimeOfDay = $ActiveStartTimeOfDay
$jobSchedule.FrequencyTypes = 8                # weekly
$jobSchedule.FrequencyInterval = 64            # Saturday
$jobSchedule.FrequencySubDayTypes = 1          # at specific time     
$jobSchedule.ActiveStartDate = get-date
$jobSchedule.FrequencyRecurrenceFactor = 1     # every one week
$jobSchedule.Create()

Note the $jobSchedule.ActiveStartTimeOfDay and $jobSchedule.ActiveEndTimeOfDay properties are TimeSpan but the corresponding parameters in sp_add_schedule procedure are Int, with default values of 000000 (12:00:00 AM) and 235959 (11:59:59 PM), respectively. They values must be entered using the form HHmmss. So it requires to perform some additional steps to convert a given Int time value to TimeSpan, that is, you have to create a TimeSpan object to convert 20000 to 2:00 PM, for example:

$ts = "20000".PadLeft(6, '0')
$ActiveStartTimeOfDay = [timespan]::ParseExact($ts, 'hhmmss', $null)
$jobSchedule.ActiveStartTimeOfDay = $ActiveStartTimeOfDay

5. Wrap up

The complete script is here:

Import-Module SqlServer

$server = [Microsoft.SqlServer.Management.Smo.Server]::New("SERVER-1")
$Server.ConnectionContext.ConnectTimeout = 120
$Server.ConnectionContext.EncryptConnection = $true
$Server.ConnectionContext.TrustServerCertificate = $true

$job = [Microsoft.SqlServer.Management.Smo.Agent.Job]::New($server.JobServer, "Check database integrity for AdventureWorks")
$job.Description = 'This is a job that was created by using PowerShell and SMO'
$job.OwnerLoginName = 'sa'
$job.Category = "Database Maintenance"
$job.Create()

$jobStep = [Microsoft.SqlServer.Management.Smo.Agent.JobStep]::new($job, "Run agent")
$jobStep.SubSystem = 'TransactSql'
$jobStep.Command = "DBCC CHECKDB ('AdventureWorks') WITH NO_INFOMSGS"
$jobStep.OnSuccessAction = 'QuitWithSuccess'
$jobStep.OnFailAction = 'QuitWithFailure'
$jobStep.Create()

$job.ApplyToTargetServer($server.DomainInstanceName.ToString())
$job.StartStepID = $jobStep.ID
$job.Alter()

$jobSchedule = [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]::New($job, "Sched 01")
$ts = "20000".PadLeft(6, '0')
$ActiveStartTimeOfDay = [timespan]::ParseExact($ts, 'hhmmss', $null)
$jobSchedule.ActiveStartTimeOfDay = $ActiveStartTimeOfDay
$jobSchedule.FrequencyTypes = 8                # weekly
$jobSchedule.FrequencyInterval = 64            # Saturday
$jobSchedule.FrequencySubDayTypes = 1          # at specific time     
$jobSchedule.ActiveStartDate = get-date
$jobSchedule.FrequencyRecurrenceFactor = 1     # every one week
$jobSchedule.Create()

Got feedback? 😊😐☹️