Post reply

Scripting SQL jobs using PowerShell

  • May 3, 2012 at 1:14 am

    #255260

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

  • 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

    }

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

  • April 27, 2016 at 1:55 pm

    #1875070

    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