SQL Server Agent jobs are an essential component of managing and automating various database tasks. Whether you’re scheduling backups, running maintenance plans, or executing custom scripts, these jobs are crucial for keeping your SQL Server environment running smoothly. But what happens when you need to back up and recover these jobs? Automating the process of scripting out SQL Server Agent jobs ensures you always have a backup of these critical assets. In this blog post, we’ll explore how to use the SqlServer PowerShell module to automate this process.
Now obviously, since SQL Server stores the Agent jobs in msdb, if you want to be able to include SQL Server Agent jobs in a full restore of a SQL Server then it is crucial that you are including msdb in your regular database backup routines. But let’s say that you also wanted to automate a process of scripting out all of the SQL Server Agent jobs on a SQL Server. There are a number of reasons that you may want to do this, some of which include:
- Backup and recovery: Safeguard your jobs in case of accidental deletion or server failure
- Migration: Easily transfer jobs to another SQL Server instance
- Version Control: Keep track of changes made to your jobs over time
While SQL Server Management Studio (SSMS) allows you to manually script out jobs, automating the process with PowerShell is faster, repeatable, and less error-prone.
Prerequisites
To follow along, ensure the following:
- PowerShell is installed on your machine.
- The SqlServer PowerShell module is installed. If not, you can install it by running:
Install-Module -Name SqlServer -Scope CurrentUser
- You have sufficient permissions to access SQL Server Agent and script out jobs.
Import the SqlServer module and create a SMO object:
Once you have validated that the SqlServer module is installed you can create a new SMO (SQL Management Object) connection to SQL Server by executing the following command in PowerShell:
Import-Module SqlServer
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "YourSQLServerName"
Non-Standard port: If your SQL Server runs on a non-standard port then you can follow the server name by the port number separated by a comma. Like below:
Import-Module SqlServer
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "YourSQLServerName,1234"
How let’s list out the jobs:
Once you have created the SMO object to your SQL Server you can list out the jobs that are on the server by running the following command:
$srv.JobServer.Jobs
And from here you can script each job by executing the following command:
$srv.JobServer.Jobs | foreach{
$_.Script() | Out-File "C:\temp\AllMyJobs.sql" -Append # put in the path here
}
However…
It may not be very helpful to have everything all mashed into one big file like a SQL Server Agent stew (probably wouldn’t taste very good anyway!). So instead let’s put everything together and add some magic so that we can loop through each job and create a separate script file for each job.
Here is the script that puts it all together and scripts out each SQL Server Agent job to an individually named SQL script file:
clear-host
#------------------------------------------------------------------------------------------
# The following only needs to be run if the SqlServer module has not been installd yet:
# Install-Module -Name SqlServer -AllowClobber -Scope CurrentUser
#------------------------------------------------------------------------------------------
#------------------------------------------------------------------------------------------
# Define the SQL Server name that you want to script out Agent Jobs for.
# If the server is running on a non-standard port then include the port number separated by a comma.
# For example: "ServerName,1234"
#------------------------------------------------------------------------------------------
$serverName = "ServerName"
#------------------------------------------------------------------------------------------
# Define the base folder where the backup scripts will be stored
# If this path does not exist then the script will attempt to create it.
# ** The final output folder will include a subfolder of this path named for the date and time
# with an additional subfolder named "SqlServerAgentJobs"
# For example, something like this: C:\Temp\ScriptedSQLObjects\2025-01-07_135322\SqlServerAgentJobs\
#------------------------------------------------------------------------------------------
[string]$basePath = "C:\Temp\ScriptedSQLObjects\"
#------------------------------------------------------------------------------------------
# Import the SQL Server module.
# ** Remember: this line of code will fail if the SqlServer module has not been installed.
# See the line at the top of the script for syntax for installeing the module
#------------------------------------------------------------------------------------------
Import-Module SqlServer
#------------------------------------------------------------------------------------------
# Create a new SMO connection to the SQL Server
#------------------------------------------------------------------------------------------
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
#------------------------------------------------------------------------------------------
# Define the final output folder path and attempt to create final output folder
#------------------------------------------------------------------------------------------
[string]$fileSuffix = (Get-Date -Format "yyyy-MM-dd_HHmmss").ToString().Trim()
[string]$outputFolder = [System.IO.Path]::Combine($basePath, $fileSuffix)
$objectFolderPath = [System.IO.Path]::Combine($outputFolder, "SqlServerAgentJobs")
if(!([System.IO.Directory]::Exists($objectFolderPath)))
{
write-host "Creating directory path $objectFolderPath" -ForegroundColor Cyan
[System.IO.Directory]::CreateDirectory($objectFolderPath) | out-null
}
write-host "Scripting SQL Server Agent Jobs to the following folder: $objectFolderPath" -ForegroundColor Cyan
#------------------------------------------------------------------------------------------
# Now cycle through the jobs and script out each job
#------------------------------------------------------------------------------------------
[int]$jobCnt = 0
$jobs = $srv.JobServer.Jobs
foreach($job in $jobs)
{
[string]$objName = $job.Name.ToString().Trim()
[string]$fileName = "Jobs_" + $objName + ".sql"
$fileName = $fileName.Replace("\","").Replace("/", "").Replace(":", "").Replace(";", "").Replace("'", "").Replace("*", "").Replace("<", "").Replace(">", "").Replace("?", "").Replace("!", "").Replace("$", "").Replace("+", "").Replace("]", "").Replace("[", "")
[string]$fullPath = join-path $objectFolderPath $fileName
[string]$separator = "--******************************************************************************`r`n-- Job Name: " + $job.Name.ToString().Trim() + "`r`n-- Job Category: " + $job.Category.ToString().Trim() + "`r`n--******************************************************************************`r`n"
$separator | Out-File $fullPath -Append
$job.Script() | Out-File $fullPath -Append
"`r`n go `r`n" | Out-File $fullPath -Append
$jobCnt++
}
Write-Host "Total Agent Jobs scripted for $serverName is $jobCnt" -ForegroundColor Cyan
The EckMan 