Skip to content

Instantly share code, notes, and snippets.

@tcartwright
Created August 22, 2021 22:00
Show Gist options
  • Save tcartwright/fea32fb2e077de1a75b2b9aa78e10649 to your computer and use it in GitHub Desktop.
Save tcartwright/fea32fb2e077de1a75b2b9aa78e10649 to your computer and use it in GitHub Desktop.
POWERSHELL: Generates SQL Server Agent objects to sql files
[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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment