Last active
January 11, 2016 16:51
-
-
Save crshnbrn66/7f589c7c2dae64c73c15 to your computer and use it in GitHub Desktop.
Script to create Sql jobs / query them and steps
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I've begun to work on JobSteps for SQL server this is what I've come up with so far.