Script - All Jobs (powershell/SMO)

# É NECESSÁRIO INFORMAR O DIRETÓRIO DESTINO E A INSTÂNCIA DO SQL
# EXECUTAR DIRETAMENTE DE UM STEP DE JOB DO TIPO POWERSHEL SUBSTITUA O NOME DA INSTANCIA POR "$(ESCAPE_DQUOTE(SRVR))"
[string] $path   = 'C:\! tmp\'
[string] $server = '.\SQL14DV01' #"$(ESCAPE_DQUOTE(SRVR))"

[Microsoft.SqlServer.Management.Smo.Server] $sql = $null;

[System.IO.FileStream]   $stream = $null;
[System.IO.StreamWriter] $writer = $null;

try{
  # prepara nome do arquivo como base no servername
  $path += [String]::Format("\backup_job_{0}.sql", $server.ToLower().Replace('\', '!'));

  # exclui arquivo existente
  if([System.IO.File]::Exists($path)){
    [System.IO.File]::Delete($path);
  }

  # prepara template de DROP
  [System.Text.StringBuilder] $templateDrop = New-Object System.Text.StringBuilder;
  $templateDrop=$templateDrop.AppendLine("IF EXISTS( SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = '{0}')");
  $templateDrop=$templateDrop.AppendLine("BEGIN");
  $templateDrop=$templateDrop.AppendLine("  EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name=N'{0}';");
  $templateDrop=$templateDrop.AppendLine("  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback")
  $templateDrop=$templateDrop.AppendLine("END");

  # conecta no sql
  $sql = New-Object Microsoft.SqlServer.Management.Smo.Server($server);

  # cria novo arquivo com CODEPAGE compatível para as acentuações
  $stream = [System.IO.File]::Open($path, [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite);
  $writer = New-Object System.IO.StreamWriter($stream, [System.Text.Encoding]::GetEncoding("iso-8859-1"));

  # percorre cada job do sql
  $sql.JobServer.Jobs | ForEach-Object{ 
    [datetime] $date   = Get-Date;
    [string]   $header = ( "--| Object:  Job [{0}] | Script Date: {1:dd/MM/yyyy HH:mm:ss} |--" -f $PSItem.Name, $date );
    [string]   $script = [string]::Empty;

    # recupera script do JOB    
    $PSItem.Script() | ForEach-Object{ $script += $PSItem; };

    # remove uid dos schedulers
    $PSItem.JobSchedules | ForEach-Object{ $script = $script.Replace("N'" + $PSItem.ScheduleUid + "'", "NULL"); };

    # adiciona instrução DROP
    [string] $drop = ($templateDrop.ToString() -f $PSItem.Name);
    $script = $script.Insert($script.IndexOf("IF NOT EXISTS"), $drop);

    # escreve script do job no arquivo
    $writer.WriteLine("".PadLeft($header.Length, '-'));
    $writer.WriteLine($header);
    $writer.WriteLine("".PadLeft($header.Length, '-'));
    $writer.WriteLine("RAISERROR('{0}...',0,0) WITH NOWAIT;", $PSItem.Name);
    $writer.WriteLine();
    $writer.WriteLine($script);
    $writer.WriteLine("GO");
  }
} finally {
  # libera recursos e arquivo
  if( $writer -ne $null ){
    $writer.Flush();
    $writer.Close();
    $writer.Dispose();
  }

  if( $stream -ne $null ){
    $stream.Close();
    $stream.Dispose();
  }
}