Creating SQL Server agent job step in SQL Server through Powershell.

#creating sql server agent step in sql server through powershell.

cls
Import-Module sqlps -DisableNameChecking
$instanceName="RAMESH"#Provide sql server named instance or default instance here
$server=New-Object `
-TypeName Microsoft.SqlServer.Management.Smo.Server `
-ArgumentList $instanceName
$getJobs=$server.JobServer.Jobs
$getJobs|
ForEach-Object{
$IndividualJob=$_
#now let's add a simple T-SQL Job Step
$jobStep = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep($IndividualJob,"TestStep")#TestStep is a step name.
$jobStep.Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::TransactSql
$result=$jobStep.Command = "SELECT GETDATE()"
$jobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::GoToNextStep
$jobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure
$jobStep.ID=1
$jobStep.Create()
}

x

#Deleting a particular step id from the jobs. Here it is deleting 2 nd step of the job from all the jobs.

cls
Import-Module sqlps -DisableNameChecking
$instanceName="RAMESH"
$server=New-Object `
-TypeName Microsoft.SqlServer.Management.Smo.Server `
-ArgumentList $instanceName
$getJobs=$server.JobServer.Jobs
$getJobs|
ForEach-Object{
$IndividualJob=$_
Invoke-Sqlcmd `
-Query "EXEC msdb.dbo.sp_delete_jobsteP 
    @job_name ='$IndividualJob', 
    @step_id = 2" `
-ServerInstance $instanceName
}

<#
This query will provide the failure list of jobs from
mulitple instance.
#>
Cls
#Import sql serer module
Import-Module sqlps -DisableNameChecking
#Provide instance name
$ServerNames=@("RAMESH","RAMESH\UAT")
$ServerNames|
ForEach-Object{
$ComputerName=$_
$Server=New-Object `
-TypeName Microsoft.SQLServer.Management.Smo.Server `
-ArgumentList $ComputerName
$Jobs=$Server.JobServer.Jobs
$Jobs|Select-Object LastRunOutcome,OriginatingServer,Name,OwnerLoginName,LastRunDate,CurrentRunStatus,NextRunDate |
Where-Object {$_.LastRunOutcome -like '*fail*'}|
Format-Table -AutoSize -Wrap
} 

The below piece of code will edit the job schdule startime in all the mentioned instances.

cls

Import-Module sqlps -DisableNameChecking
$instanceNames=@("RAMESH","RAMESH\UAT")
$instanceNames|
ForEach-Object{
$instanceName=$_
Invoke-Sqlcmd `
-Query "update s set
    s.active_start_time = 180000
    from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name like '%USER_DATABASES - FULL%'" `
-ServerInstance $instanceName
}