Scripting SQL jobs using PowerShell

  • Premjit Das

    SSC Enthusiast

    Points: 188

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

  • tim.shirey

    SSC Enthusiast

    Points: 145

    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

    Thanks for the script.

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

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