Script Out SQL Agent Jobs (SQL Server)

# REF: http://sqlblog.com/blogs/allen_white/archive/2012/05/02/script-and-migrate-agent-jobs-between-servers-using-powershell.aspx
# REF: https://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/

# This script is my attempt at merging of the above two scripts. -dougl

# To execute: Script Out SQL Agent Jobs.ps1 <ServerNameListFile>

param([String]$ServerListPath)

#write-host "Parameter: $ServerListPath"

#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath

#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

#For each server in the array do the following.
foreach($ServerName in $ServerNameList)
{
    Write-Host "Beginning with Server: $ServerName"

    Try
    {
        $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
            Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
            $objSQLConnection.Open() | Out-Null
            Write-Host "Success."
        $objSQLConnection.Close()
    }
    Catch
    {
        Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
        $errText =  $Error[0].ToString()
            if ($errText.Contains("network-related"))
        {Write-Host "Connection Error. Check server name, port, firewall."}

        Write-Host $errText
        continue
    }

    # Won't be using this object again
    Remove-Variable -Name objSQLConnection

    #If the output folder does not exist then create it
    $OutputFolder = ".\$ServerName"
    if (!(Test-Path $OutputFolder))
    {
        write-host ("Creating directory: " + $OutputFolder)
        New-Item -ItemType directory -Path $OutputFolder
    }
    else
    {
        write-host ("Directory already exists: " + $OutputFolder)
    }

    #DEBUG write-host "File: $(".\$OutputFolder\" + $($_.Name -replace '\\', '') + ".job.sql")"

    # Connect to the instance using SMO
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

    #DEBUG write-host ("SQL Server Edition: " + $s.Edition)
    #DEBUG write-host ("SQL Agent ErrorLogFile: " + $s.JobServer.ErrorLogFile)

    # Instantiate the Scripter object and set the base properties
    $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($ServerName)

    write-host ("SCRP ToString():" + $scrp.ToString())
    write-host ("Test scrp - Server: " + $scrp.Server)

    #The next step is to set the properties for the script files:
    $scrp.Options.ScriptDrops = $False
    $scrp.Options.WithDependencies = $False
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.AppendToFile = $False
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.DriAll = $True
    $scrp.Options.Indexes = $False
    $scrp.Options.Triggers = $False
    $scrp.Options.IncludeIfNotExists = $True

    #Now, we can cycle through the jobs and create scripts for each job on the server.

    # Create the script file for each job
    foreach ($job in $s.JobServer.Jobs)
    {
        $jobname = $job.Name

        #DEBUG write-host ("Job: " + $jobname)

        $jobfilename = ($OutputFolder + "\" + $jobname + ".job.sql")
        $scrp.Options.FileName = $jobfilename

        #DEBUG write-host "Filename: $jobfilename"

        #This line blows up
        $scrp.Script($job)
    }
}