Scheduling a SQL Server job

In this recipe, we will demonstrate how to schedule a SQL Server job using PowerShell and SMO.

Getting ready

In this recipe, we assume you have a job in your development environment called Test Job that you can run. If not, pick another job in your system that you can run.

We will schedule this job to run every weekend night at 10 P.M.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module and create a new SMO Server object, as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run:

   $jobserver = $server.JobServer
   $jobname = "Test Job"
   
   $job = $jobserver.Jobs[$jobname]
   $jobschedule =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule...