PowerShell: backup your SQL Agent Jobs

I needed to backup some SQL Agent Jobs. I decided to save them to a file, but I wanted to schedule this (in the event they might change).

Below the PowerShell Script I used


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$serverInstance = "."

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}

if ($jobs -ne $null)
{

ForEach ( $job in $jobs )
{
$FileName = "d:\backup\SQLjobs\" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>