POWERSHELL: Generates SQL Server Agent objects to sql files

SQLAgentScripter.ps1

[cmdletbinding()]
Param(
    [Parameter(Mandatory=$true)]
    [string[]]$servers,
    
    [ValidateScript({
        if(-Not ($_ | Test-Path )) {
            throw "Folder does not exist"
        }
        return $true
    })]
    # if this is not passed in then .\Output is assumed.
    [System.IO.DirectoryInfo] $outputPath,
    # APPLIES TO JOBS ONLY: if this switch is present, then jobs wills be scripted without a drop. 
    [switch] $DoNotScriptJobDrop,
    # if this switch is present an IF NOT EXISTS WILL be added to all scripts so they will only get created if they dont already exist
    [switch] $IncludeIfNotExists,  
    # if this swith is present then $ tokens in the script will be left alone. Else they will be replaced with a token that will work for sqlcmd.
    [switch] $DoNotGenerateForSqlCmd
)
Set-StrictMode -Version 3.0
Clear-Host

#Load the SMO Assemly
$assembly = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
if ($assembly -eq $null) {
    Write-Error "Unable to load SMO dll" 
    return
    exit 1        
}


function ReplaceInvalidPathChars($str) {
    $str = $str.Split([IO.Path]::GetInvalidFileNameChars()) -join '_'
    $str = $str.Split([IO.Path]::GetInvalidPathChars()) -join '_'
    $str = $str -replace '\[|\]', ''
    return $str
}

function WriteScript($smoObject, [Microsoft.SqlServer.Management.Smo.ScriptingOptions]$scriptOptions, [ScriptBlock]$modifyScriptBlock) {
    $typeName = $smoObject.GetType().Name

    if ($typeName.EndsWith("y")) {
        $collectionName = "$($typeName.SubString(0, $typeName.Length -1))ies"
    } else {
        $collectionName = "$($typeName)s"
    }

    $validName = (ReplaceInvalidPathChars -str $smoObject.Name)
    try {
        $scriptFolder =  [System.Io.Path]::Combine($outFolder, $collectionName)

        if (!(Test-Path $scriptFolder -PathType Container)) {
            New-Item $scriptFolder -ItemType Directory -Force | Out-Null
        }
    
        Write-Output "Generating $($typeName): $validName.sql"
        if (!$scriptOptions) {        
            $scriptOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
        }

        $script = "$($smoObject.Script($scriptOptions))`r`nGO`r`n"

        if ($modifyScriptBlock) {
            $script = &$modifyScriptBlock -createScript $script -objectName ($smoObject.Name)
        }

        $script | Out-File ([System.Io.Path]::Combine($scriptFolder, "$validName.sql")) -Force -Encoding ascii
    } catch {
        Write-Error "`tException writing $typeName ($validName):`r`n`t$($_.Exception.GetBaseException().Message)" 
        continue
    } 
}

$scriptRoot = [System.IO.Directory]::GetParent($MyInvocation.MyCommand.Definition).FullName

if (!$outputPath) {
    $outputPath = [System.IO.Path]::Combine($scriptRoot, "Output")
}
 
# dont care for the drop statement generated by SSMS or SMO, so lets use our own which uses job name
$scriptHeaderReplaceRegex = 'BEGIN\sTRANSACTION\r\nDECLARE\s@ReturnCode\sINT\r\nSELECT\s@ReturnCode\s=\s0\r\n'
$scriptHeaderReplace = "
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT = 0

IF EXISTS (SELECT * FROM msdb.dbo.sysjobs j WHERE j.name = '<<job_name>>') BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name = '<<job_name>>', @delete_unused_schedule=1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

"

$connection = New-Object System.Data.SqlClient.SqlConnection

foreach($server in $servers) {
    # try to establish a connection ahead of time, because smo does not handle failed connections well.
    try {
        $connection.ConnectionString = "Server=$server;Integrated Security=SSPI;Connection Timeout=15;"
        $connection.Open() | Out-Null
    }
    catch {
       Write-Error "Exception connecting to ($server):`r`n`t$($_.Exception.GetBaseException().Message)" 
       continue
    } 
    finally {
        if ($connection) { $connection.Dispose() }
    }
    
    Write-Output "Generating server: $server"

    #IF the output folder does not exist then create it
    $outFolder =  [System.Io.Path]::Combine($outputPath,  (ReplaceInvalidPathChars -str $server))

    if (!(Test-Path $outFolder)) {
        New-Item $outFolder -ItemType Directory -Force | Out-Null
    }
 
    #Create a new SMO instance for this $ServerName
    $smoServer = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

    $options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $options.IncludeIfNotExists = $IncludeIfNotExists.IsPresent
    # $options.ScriptForCreateDrop = $true
    $options.AgentNotify = $true
    $options.AgentAlertJob = $true
    $options.AllowSystemObjects = $false

    $modifyScript = {
        Param(
            [string]$createScript,
            [string]$objectName
        )
        # if we are generating the job for sql cmd we need to replace any dollar signs with our own custom $ token
        # AND if the script contains a $ in the first place
        if (!$DoNotGenerateForSqlCmd.IsPresent -and $createScript.Contains("`$")) {
            $createScript = ":setvar dollar `"`$`"`r`n$($createScript -replace "\`$", "`$(dollar)")"
        }
        return $createScript;
    }

    foreach ($alert in $smoServer.JobServer.Alerts) {
        WriteScript -smoObject $alert -modifyScriptBlock $modifyScript -scriptOptions $options
    }

    foreach ($operator in $smoServer.JobServer.Operators) {
        WriteScript -smoObject $operator -modifyScriptBlock $modifyScript -scriptOptions $options
    }

    foreach ($OperatorCategory in $smoServer.JobServer.OperatorCategories) {
        WriteScript -smoObject $OperatorCategory -modifyScriptBlock $modifyScript -scriptOptions $options
    }

    foreach ($category in $smoServer.JobServer.JobCategories) {
        WriteScript -smoObject $category -modifyScriptBlock $modifyScript -scriptOptions $options
    }

    $modifyJobScript = {
        Param(
            [string]$createScript,
            [string]$objectName
        )
        #only add the drop statements if they want us to
        if (!$DoNotScriptJobDrop.IsPresent) {
            $dropJobSql = ($scriptHeaderReplace -ireplace "<<job_name>>", $objectName)
            $createScript = $createScript -ireplace $scriptHeaderReplaceRegex, $dropJobSql
        }
        # if we are generating the job for sql cmd we need to replace any dollar signs with our own custom $ token
        # AND if the script contains a $ in the first place
        if (!$DoNotGenerateForSqlCmd.IsPresent -and $createScript.Contains("`$")) {
            $createScript = ":setvar dollar `"`$`"`r`n$($createScript -replace "\`$", "`$(dollar)")"
        }
        return $createScript;
    }

    $options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $options.IncludeIfNotExists = $IncludeIfNotExists.IsPresent

    #Script out each SQL Server Agent Job for the server
    foreach ($job in $smoServer.JobServer.Jobs) {
        WriteScript -smoObject $job -modifyScriptBlock $modifyJobScript -scriptOptions $options
    }
}