How to create SQL job in SQL server? | Create SQL job in SQL server using PowerShell

This article provide guide for How to create SQL job in SQL server?

Below script Create SQL job in SQL server using PowerShell. You can create same job on multiple SQL Server. This will automate process and reduce manual efforts to create same SQL Server jobs on Multiple SQL Server Instance.

Step 1: Create List of server name text file. E.g., ListOfServers.txt and add server name where you want to Create SQL job in SQL server using PowerShell.

Step 2.: Execute below script.

Before execution change login and password in script. Change SQLJobStep.Command as per your jobs type. Change database name in SQLJobStep.DatabaseName .

$ServerList = Get-Content E:\DBA\Powershell\ListOfServers.txt
foreach ($srv in $ServerList)
{
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($srv)
$SQLSvr.ConnectionContext.LoginSecure = $false
$SQLSvr.ConnectionContext.Login="sa"
$SQLSvr.ConnectionContext.Password="Pnx@2019"

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

    $SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example1_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, "Example1_Job_Schedule"

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

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

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

How To Configure Database Mail on azure SQL managed instance |SQL Server Database Mail