实现自动备份sqlserver代理作业JOB

step1

阅读WindowShell脚本

$ServerName='XXX'
$FilePath='XX'
echo $env:COMPUTERNAME
echo  $ServerName
if(Test-Path $FilePath)
{
        #check if the instance name is available on the server
        
         [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null
         $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
        if($ServerName.contains($env:COMPUTERNAME) -and ('true'))
          {
            $jobs = $srv.JobServer.Jobs | Where-Object {$_.category -notlike "*repl*" -and $_.category -notlike "*shipping*" -and $_.category -notlike "*Maintenance*" }  
  
            ForEach ( $job in $jobs) 
               {
                     $jobname = $FilePath +'\' + $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_") + ".sql" 
                     $job.Script() | Out-File $jobname 
                Write-Host 'Scripting out ' $job ' successfully!'
               }
         }
        else 
        {
         Write-Host 'The server name you entered is not available!'
       }

}
else
{
Write-Host 'The path does not exist, please retype again!'

}

step2：

  将Step1中的内容修改服务器名称及备份内容存放此路径，保存在 名称为 GetAllAgentJobs.ps1的文件

step3

将下属内容保存为批处理文件

   powershell XX/GetAllAgentJobs.ps1

step4

 新增计划任务，调用3中保存到批处理文件