Scripting SQL jobs using PowerShell

• Premjit Das

  SSC Enthusiast

  Points: 190

  More actions

  May 3, 2012 at 1:14 am

  #255260

  Comments posted to this topic are about the item Scripting SQL jobs using PowerShell

• tim.shirey

  SSC Enthusiast

  Points: 145

  More actions

  December 13, 2013 at 10:22 am

  #1673342

  I really like your script. I modified it a little to be more flexible. Now the script can be run from any directory and the output will go to the same directory. I also modified the output file name to include the SQL instance name. I hope you like it.

  Tim

  #------------------------------------------------------------------------------------------------

  param($sqlserver)

  $scriptpath = $MyInvocation.MyCommand.Path

  $dir = Split-Path $scriptpath

  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

  $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

  $jobs = $srv.JobServer.Jobs | Where-Object {$_.category -notlike "*repl*" -and $_.category -notlike "*shipping*" -and $_.category -notlike "*Maintenance*" }

  ForEach ( $job in $jobs )

  {

  $jobname = $dir + "\" + $sqlserver.replace("\","_") + "_" + $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_") + ".sql"

  $job.Script() | Out-File $jobname

  }

  #------------------------------------------------------------------------------------------------

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  April 27, 2016 at 1:55 pm

  #1875070

  Thanks for the script.