Backup your SQL Server jobs using PowerShell script

This post will help you create a SQL Server job to backup all the jobs in a SQL Server Agent of an instance.

It’s an useful job that you have to have running in your environment. It could help you for the following:

• Backing up your jobs,

• When migrating from one server to another,

• When you want to check the history of changes for a job,

• If you want to keep documentation for your jobs and

The PowerShell script is given below. Save it on a location (.ps1 extension). Then you have to specify the path in the $SavePath variable.

param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
$jobs = $srv.JobServer.Jobs
/*
#Scripting execept Maintenance & Logshipping Jobs
$jobs = $srv.JobServer.Jobs | Where-Object {$_.category -notlike "*repl*" -and $_.category -notlike "*shipping*" 
-and $_.category -notlike "*Maintenance*" } 
*/#Build this portion of the directory structure out here in case scripting takes more than one minute.
$DateFolder = get-date -format yyyyMMddHHmm
$SavePath = "C:SqlServer2014Jobs" + $DateFolder
   
ForEach ( $job in $jobs )
{
#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
$scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
$jobname = $job.Name.replace(" ","_").replace("","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_")

 #This section builds folder structures.  Remove the date folder if you want to overwrite.
            if ((Test-Path -Path "$SavePath$TypeFolder") -eq "true")
                  {"Scripting Out $TypeFolder $ScriptThis"}
            else {new-item -type directory -name "$TypeFolder" -path "$SavePath"}
     $jobname = "$SavePath$TypeFolder$job.SQL"
 $job.Script() | Out-File $jobname
   }

Second step is to create the job. Download the script-code and specify your instance name. Run the script in SSMS studio and you have it. It runs under the sa user. 

That’s all.