Scripting SQL jobs using PowerShell

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

  • 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.




    $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



  • Thanks for the script.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply