Script to create Sql jobs / query them and steps

SQLJob.ps1

#http://sqlblog.com/blogs/allen_white/archive/2008/01/09/create-agent-jobs-to-run-powershell-scripts.aspx
#https://msdn.microsoft.com/en-us/library/ms162162.aspx
#Requires -Version 3.0 
function Get-SqlJobs
{
  param([string]$sqlServer, [string]$sqlinstance = $null)
  $s = New-SqlServerConnection -sqlServer $sqlServer -sqlinstance $sqlinstance  
  $jobs = $s.JobServer.Jobs
  $jobs
}

function get-SqlJob
{
    param
    ([object]$jobs,
     [string]$job
    )
    
    $j = ($jobs | ?{$_.name -like $job})

    $j
}

function New-SqlServerConnection
{
  param([string]$sqlServer, [string]$sqlinstance = $null)
  
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
  $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$sqlServer\$sqlinstance"
  $s
}

function New-SQLJob
{
  param([Microsoft.SqlServer.Management.Smo.Server]$Serverconnection,
        [string]$JobName = 'Job1',
        [string]$Description = 'Job1 My Job Name',
        [string]$Category ,#= '[Uncategorized (Local)]',
        [string]$OwnerLoginName = 'sa')
  $j = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($Serverconnection.JobServer,$JobName)
  $j.description = $Description
  $j.category = $Category
  $j.OwnerLoginName = $OwnerLoginName
  
  $j.create()
 
}

function Test-SQLJobStep
{

}

function New-SQLJobStep
{
# New-SQLJobStep -job (get-SqlJob -jobs $jobs -job 'test') -JobStepName 'test2' -Subsystem 'CmdExec' -command 'Powershell "test $(get-date)" | Out-File -FilePath c:\temp\test.txt -Append' -OnSuccessAction QuitWithSuccess -OnFailAction QuitWithSuccess
  param
  ([Microsoft.SqlServer.Management.Smo.Agent.Job]$job,
   [string]$JobStepName,
   [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]$Subsystem ,
   [string]$command = 'powershell "& C:\Admin\backupdb.ps1"',
   [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]$OnSuccessAction  ,
   [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]$OnFailAction    
  )
  #$jobStep =  New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($Serverconnection.JobServer,$JobStepName)
  $jobStep =  New-Object 'Microsoft.SqlServer.Management.Smo.Agent.JobStep' ($job,$JobStepName)
  $jobStep.Subsystem = $Subsystem
  $JobStep.command = $command
  $jobstep.OnSuccessAction = $OnSuccessAction
  $jobstep.OnFailAction = $OnFailAction
  $jobstep.create()
  #this job.originating server is the property on which you are changing the job if it is diffrent than a case other than originating server will need to account for it.

  $job.ApplyToTargetServer($job.originatingserver)
  $job.Alter()

}

function Get-SQLJobSteps
{
    #-job (get-SqlJobGuid -jobs $jobs -job 'test')
    param
    (
       [Microsoft.SqlServer.Management.Smo.Agent.Job]$job 
    )
    $jobSteps = $job.JobSteps
    $jobSteps
}

function Alter-SQLJobStep
{
    param
    (
       [Microsoft.SqlServer.Management.Smo.Agent.Job]$job,
       [int32]$StartStepId
    )
    $job.StartStepID = $StartStepId
    $job.Alter()
}

$serverconnection = New-SqlServerConnection -sqlServer . 
#$jobs = get-sqlJobs -sqlServer .
#$job = get-SqlJob -jobs $jobs -job 'test'
#$jobSteps = Get-SQLJobSteps -job $job
#Alter-SQLJobStep -job $job -StartStepId 3
#New-SQLJobStep -job (get-SqlJob -jobs (get-sqljobs -sqlserver .) -job 'test3') -JobStepName 'step3' -Subsystem 'CmdExec' -command 'Powershell "test $(get-date)" | Out-File -FilePath c:\temp\test.txt -Append' -OnSuccessAction QuitWithSuccess -OnFailAction QuitWithSuccess

I've begun to work on JobSteps for SQL server this is what I've come up with so far.

A couple comments:

• Use 'return' for your function's object output. I.e. 'return $job' instead of just '$job'
• Use full cmdlet names, not aliases, i.e. Where-Object instead of '?'
• Move the SMO assembly load all the way to the top, so it's loaded for everything and easily recognized.

In addition, I wouldn't do a Get-SqlJob and Get-SqlJobs, that is confusing. I would simply have Get-SqlJobs and understand it's returning an array that I would filter as necessary. You could even consider adding a -Filter to Get-SqlJobs to restrict it by name. For New-SqlServerConnection, I wouldn't separate the host name and instance name into two separate arguments, but simply make it a single instance name argument that I would pass the full name to (i.e. 'FOO' or 'FOO\BAR'). This would be mostly for clarity.

Otherwise, good start. I'd like to see how this develops.

thank you for your feedback Mike. appreciate it.