Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating