Script SQL Server Agent Jobs with PowerShell and SMO

SQL-DMO and ActiveX Script job steps belong to an older SQL Server era. For current systems, use SQL Server Management Objects (SMO) from PowerShell and the maintained SqlServer module.

Script every SQL Server Agent job

Import-Module SqlServer

$serverName = "SERVER\INSTANCE"
$outputDir = "C:\DBA\SqlAgentJobs"

New-Item -ItemType Directory -Force -Path $outputDir | Out-Null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName

foreach ($job in $server.JobServer.Jobs) {
    $safeName = $job.Name -replace '[^a-zA-Z0-9._-]', '_'
    $path = Join-Path $outputDir "$safeName.sql"
    $job.Script() | Set-Content -LiteralPath $path -Encoding UTF8
}

Run the script with an account that can read the jobs. The output is one T-SQL file per job, with characters that are unsafe in Windows filenames replaced by underscores.

Validate the result

• Open several generated files and confirm that job steps, schedules, alerts, notifications, and target servers are represented.
• Restore into a non-production instance first.
• Review job owners and proxy access. A job can be created successfully and still fail if its owner cannot use the required proxy.
• Protect the output directory. Job commands can contain server names, paths, tokens, or other operational details.

Why replace the original approach?

The former SQL-DMO example depended on an ActiveX Script job step and wrote files without consistently clearing its per-job buffer. SQL-DMO is deprecated, while SMO is the supported automation model for administering SQL Server objects.

For a one-time task, SQL Server Management Studio can also script an individual job. For repeatable backups of job definitions, keep the PowerShell script in source control and run it through a secured administrative workflow.