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 hidden or 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
thank you for your feedback Mike. appreciate it.