PowerShell.com
Library » Script Library » SQL Server » Create-MSSQLJob-UsingSMO

Create-MSSQLJob-UsingSMO

Previous | Next | View all files | View Slideshow
posted by Richard Giles
10-06-2008
Downloads: 504
File size: 4.1kB
Views: 2,257
Embed
Create-MSSQLJob-UsingSMO
  1. ## ===================================================================== 
  2. ## Title       : Create-MSSQLJob-UsingSMO 
  3. ## Description : Create a daily SQL job to call a powershell script 
  4. ## Author      : Idera 
  5. ## Date        : 9/1/2008 
  6. ## Input       : -server <server\instance> 
  7. ##                      -jobName <jobname> 
  8. ##               -taskDesc <job description> 
  9. ##                      -category <job category> 
  10. ##                   -hrSched <n - hour military time> 
  11. ##               -minSched <n - minute military time> 
  12. ##                      -psScript <path\script.ps1> 
  13. ##                   -verbose  
  14. ##                   -debug     
  15. ## Output      : SQL Job, job step and schedule for running a PowerShell script 
  16. ## Usage            : PS> . Create-MSSQLJob-UsingSMO -server MyServer -jobName MyJob  
  17. ##                          -taskDesc Perform something... -category Backup Job  
  18. ##                            -hrSched 2 -psScript C:\TEMP\test.ps1 -minSched 0 -v -d 
  19. ## Notes            : Adapted from an Allen White script 
  20. ## Tag            : SQL Server, SMO, SQL job 
  21. ## Change Log  : 
  22. ## ===================================================================== 
  23.  
  24. param 
  25. ( 
  26.       [string]$server = "(local)", 
  27.     [string]$jobname = "PowerShellJob", 
  28.     [string]$taskDesc = "Perform some task", 
  29.     [string]$category = "[Uncategorized (Local)]", 
  30.     [string]$psScript = "C:\TEMP\test.ps1", 
  31.     [int]$hrSchedule = 2, 
  32.     [int]$minSchedule = 0, 
  33.     [switch]$verbose, 
  34.     [switch]$debug 
  35. ) 
  36.  
  37. function main() 
  38. { 
  39.     if ($verbose) {$VerbosePreference = "Continue"} 
  40.     if ($debug) {$DebugPreference = "Continue"} 
  41.     Create-MSSQLJob-UsingSMO $server $jobName $taskDesc $category $psScript $hrSchedule $minSchedule 
  42. } 
  43.  
  44. function Create-MSSQLJob-UsingSMO($server, $jobName, $taskDesc, $category, ` 
  45.                                     $psScript, $hrSched, $minSched) 
  46. { 
  47.     # TIP: using PowerShell to create an exception handler 
  48.    trap [Exception]  
  49.     { 
  50.       write-error $("TRAPPED: " + $_.Exception.Message); 
  51.       continue; 
  52.    } 
  53.  
  54.     # Load SMO assembly 
  55.     [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') 
  56.      
  57.     # Instantiate SMO server object 
  58.     # TIP: instantiate object with parameters 
  59.     $namedInstance = new-object ('Microsoft.SqlServer.Management.Smo.Server') ($server) 
  60.      
  61.     # Instantiate an Agent Job object, set its properties, and create it 
  62.     Write-Debug "Create SQL Agent job ..." 
  63.     $job = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($namedInstance.JobServer, $jobName) 
  64.     $job.Description = $taskDesc 
  65.     $job.Category = $category 
  66.     $job.OwnerLoginName = 'sa' 
  67.      
  68.     # Create will fail if job already exists 
  69.     #  so don't build the job step or schedule 
  70.     if (!$job.Create()) 
  71.     { 
  72.         # Create the step to execute the PowerShell script 
  73.         #   and specify that we want the command shell with command to execute script,  
  74.         Write-Debug "Create SQL Agent job step..." 
  75.         $jobStep = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($job, 'Step 1') 
  76.         $jobStep.SubSystem = "CmdExec" 
  77.         $runScript = "powershell " + "'" + $psScript + "'" 
  78.         $jobStep.Command = $runScript 
  79.         $jobStep.OnSuccessAction = "QuitWithSuccess" 
  80.         $jobStep.OnFailAction = "QuitWithFailure" 
  81.         $jobStep.Create() 
  82.          
  83.         # Alter the Job to set the target server and tell it what step should execute first 
  84.         Write-Debug "Alter SQL Agent to use designated job step..." 
  85.         $job.ApplyToTargetServer($namedInstance.Name) 
  86.         $job.StartStepID = $jobStep.ID 
  87.         $job.Alter() 
  88.      
  89.         # Create start and end timespan objects to use for scheduling 
  90.         # TIP: using PowerShell to create a .Net Timespan object 
  91.         Write-Debug "Create timespan objects for scheduling the time for 2am..." 
  92.         $StartTS = new-object System.Timespan($hrSched, $minSched, 0) 
  93.         $EndTS = new-object System.Timespan(23, 59, 59) 
  94.          
  95.         # Create a JobSchedule object and set its properties and create the schedule 
  96.         Write-Debug "Create SQL Agent Job Schedule..." 
  97.         $jobSchedule = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($job, 'Sched 01') 
  98.         $jobSchedule.FrequencyTypes = "Daily" 
  99.         $jobSchedule.FrequencySubDayTypes = "Once" 
  100.         $jobSchedule.ActiveStartTimeOfDay = $StartTS 
  101.         $jobSchedule.ActiveEndTimeOfDay = $EndTS 
  102.         $jobSchedule.FrequencyInterval = 1 
  103.         $jobSchedule.ActiveStartDate = get-date 
  104.         $jobSchedule.Create() 
  105.          
  106.         Write-Host SQL Agent Job: $jobName was created 
  107.     } 
  108. } 
  109.  
  110. main 
Filed under: , , ,

Create a SQL Server job to call a PowerShell script

Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.