functions/Invoke-DBSqlAgentScripter.ps1
|
function Invoke-DBSqlAgentScripter { <# .SYNOPSIS Will script out all Sql Agent objects to sql script files. .DESCRIPTION Will script out all Sql Agent objects to sql script files. .PARAMETER ServerInstances The sql server instances to connect to. .PARAMETER Credentials Specifies credentials to connect to the database with. If not supplied then a trusted connection will be used. .PARAMETER OutputPath The output path for the scripts. .PARAMETER DoNotScriptJobDrop APPLIES TO JOBS ONLY: if this switch is present, then jobs wills be scripted without a drop. .PARAMETER IncludeIfNotExists If this switch is present an IF NOT EXISTS WILL be added to all scripts so they will only get created if they don't already exist .PARAMETER DoNotGenerateForSqlCmd If this switch is present then $ tokens in the script will be left alone. Else they will be replaced with a token that will work for SqlCmd. #> [CmdletBinding()] param ( [Parameter(Mandatory=$true)] [ValidateCount(1, 9999)] [string[]]$ServerInstances, [pscredential]$Credentials, [System.IO.DirectoryInfo] $OutputPath, [switch] $DoNotScriptJobDrop, [switch] $IncludeIfNotExists, [switch] $DoNotGenerateForSqlCmd ) begin { # dont care for the drop statement generated by SSMS or SMO, so lets use our own which uses job name instead of Id, which can technically vary $scriptHeaderReplaceRegex = 'BEGIN\sTRANSACTION\r\nDECLARE\s@ReturnCode\sINT\r\nSELECT\s@ReturnCode\s=\s0\r\n' $scriptHeaderReplace = "`r`n$((GetSQLFileContent -fileName "SqlAgentIfNotExistsDrop.sql").TrimEnd())`r`n`r`n" } process { foreach($ServerInstance in $ServerInstances) { $server = New-DBSMOServer -ServerInstance $ServerInstance -Credentials $Credentials Write-InformationColorized "Generating server: $ServerInstance" -ForegroundColor Yellow #IF the output folder does not exist then create it $outFolder = [System.Io.Path]::Combine($outputPath, (ReplaceInvalidPathChars -str $ServerInstance)) if (!(Test-Path $outFolder)) { New-Item $outFolder -ItemType Directory -Force | Out-Null } $options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions $options.IncludeIfNotExists = $IncludeIfNotExists.IsPresent $options.AgentNotify = $true $options.AgentAlertJob = $true $options.AllowSystemObjects = $false $WriteAgentScriptFileParams = @{ scriptOptions = $options DoNotScriptJobDrop = $DoNotScriptJobDrop.IsPresent DoNotGenerateForSqlCmd = $DoNotGenerateForSqlCmd.IsPresent modifyScriptBlock = $modifyAgentScript scriptHeaderReplaceRegex = $scriptHeaderReplaceRegex scriptHeaderReplace = $scriptHeaderReplace } foreach ($alert in $server.JobServer.Alerts) { WriteAgentScriptFile -smoObject $alert @WriteAgentScriptFileParams } foreach ($operator in $server.JobServer.Operators) { WriteAgentScriptFile -smoObject $operator @WriteAgentScriptFileParams } foreach ($OperatorCategory in $server.JobServer.OperatorCategories) { WriteAgentScriptFile -smoObject $OperatorCategory @WriteAgentScriptFileParams } foreach ($category in $server.JobServer.JobCategories) { WriteAgentScriptFile -smoObject $category @WriteAgentScriptFileParams } $WriteAgentScriptFileParams.scriptOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions $WriteAgentScriptFileParams.modifyScriptBlock = $modifyAgentJobScript $options.IncludeIfNotExists = $IncludeIfNotExists.IsPresent #Script out each SQL Server Agent Job for the server foreach ($job in $server.JobServer.Jobs) { WriteAgentScriptFile -smoObject $job @WriteAgentScriptFileParams } } } end { } } |