SQL Server - Como fazer backup de todos os jobs do SQL Agent via linha de comando (CLR C# ou Powershell)

Olá pessoal!
Tudo certo?

Neste post vou demonstrar como fazer backup de todos os jobs do SQL Server Agent via linha de comando (CLR C# ou Powershell) e exportar os resultados para scripts SQL. Você pode optar por gerar 1 script para cada job ou 1 script único com todos os jobs da sua instância.

Durante a migração do SQL Server 2008 R2 para o 2014 esse script foi especialmente útil, uma vez que na instância do servidor de relatórios existiam pouco mais de 700 jobs e a tarefa de salvar o script de cada um manualmente não era viável. Fazer o backup/restore do database msdb poderia ser uma solução (embora não ache muito recomendável), mas como se tratavam de versões diferentes do SQL Server, descartamos essa hipótese.

Lembre-se que antes de fazer o restore dos jobs no novo ambiente, você precisará ter criado os logins na instância nova. Caso você não saiba como fazer o backup dos logins e suas permissões, acesse o post SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões.

Pré-requisitos

Como pré-requisitos para a utilização da Stored Procedure stpJob_Backup, você precisará criar também, os objetos abaixo:

Utils.ExecutaScriptPowerShell (Clique aqui para acessar o post)
Visualizar código-fonte

public static string ExecutaScriptPowerShell(string dsScript, string servidor = "")
{

    var idArquivo = Guid.NewGuid();
    var arquivo = $@"C:\Temp\{idArquivo}.ps1";


    using (var fileStream = new FileStream(arquivo, FileMode.Create))
    {
        using (var sw = new StreamWriter(fileStream, Encoding.GetEncoding("UTF-8")) { NewLine = "\r\n" })
        {
            sw.Write(dsScript);
        }
    }


    string argumentos;
    if (string.IsNullOrEmpty(servidor))
    {
        argumentos = @"-ExecutionPolicy ByPass -File """ + arquivo + @""""; // -ExecutionPolicy Unrestricted
    }
    else
    {
        // Para funcionar, precisa executar antes no powershell da máquina destino: Enable-PSRemoting –Force
        argumentos = @"-ExecutionPolicy ByPass -Command { Invoke-Command -ComputerName " + servidor + @" -FilePath """ + arquivo + @""" }";
    }


    string output;
    string msgErro;

    using (var scriptProc = new Process { StartInfo = { FileName = "powershell", Arguments = argumentos, UseShellExecute = false, RedirectStandardOutput = true, RedirectStandardError = true, StandardOutputEncoding = Encoding.GetEncoding(850), CreateNoWindow = true } })
    {

        scriptProc.Start();
        scriptProc.WaitForExit(1000 * 60); // 60 segundos

        File.Delete(arquivo);
        //Retorno.Mensagem(argumentos);

        output = scriptProc.StandardOutput.ReadToEnd();

        msgErro = scriptProc.StandardError.ReadToEnd();
    }


    if (!string.IsNullOrEmpty(msgErro))
        output += "\nERRO: " + msgErro;


    return output;

}

Código fonte do arquivo Servidor.cs
Visualizar código-fonte

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Bibliotecas.Model
{

    public class ServidorAtual
    {

        public string NomeServidor { get; set; }

        public ServidorAtual()
        {

            try
            {

                using (var conn = new SqlConnection(Servidor.Context))
                {

                    conn.Open();

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT @@SERVERNAME AS InstanceName";
                        NomeServidor = (string) cmd.ExecuteScalar();
                    }

                    var partes = NomeServidor.Split('\\');

                    if (partes.Length <= 1) return;
                    if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase))
                        NomeServidor = partes[0];
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }


    public static class Servidor
    {

        public static string Ds_Usuario => "Usuario";
        public static string Ds_Senha => "Senha";

        public static string PRODUCAO => "data source=PRODUCAO;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";
        public static string Context => "context connection=true";
        public static string Localhost => "data source=LOCALHOST;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";

        public static string getLocalhost()
        {

            var servidorAtual = new ServidorAtual().NomeServidor;
            return "data source=" + servidorAtual + ";initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";

        }

        public static List<string> Servidores
        {
            get
            {
                var servidores = new List<string>
                {
                    PRODUCAO,
                    Localhost
                };

                return servidores;

            }
        }

    }

}

Código-fonte da classe Retorno (Clique aqui para acessar o post)
Visualizar código-fonte

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SqlServer.Server;

namespace Bibliotecas.Model
{

    public static class Retorno
    {

        public static void Erro(string erro)
        {

            /*

            IF (OBJECT_ID('CLR.dbo.Log_Erro') IS NOT NULL) DROP TABLE CLR.dbo.Log_Erro
            CREATE TABLE CLR.dbo.Log_Erro (
	            Id_Erro INT IDENTITY(1,1),
	            Dt_Erro DATETIME DEFAULT GETDATE(),
	            Nm_Objeto VARCHAR(100),
	            Ds_Erro VARCHAR(MAX),
	            CONSTRAINT [PK_Log_Erro] PRIMARY KEY CLUSTERED (Id_Erro)
            )

            */

            using (var conexao = new SqlConnection(Servidor.getLocalhost()))
            {

                var Comando = new SqlCommand("INSERT INTO dbo.Log_Erro (Nm_Objeto, Ds_Erro) VALUES (@Nm_Objeto, @Ds_Erro)", Conexao);

                var stackTrace = new StackTrace();
                var objeto = stackTrace.GetFrame(1).GetMethod().Name;

                Comando.Parameters.Add(new SqlParameter("@Nm_Objeto", SqlDbType.VarChar, 100)).Value = objeto;
                Comando.Parameters.Add(new SqlParameter("@Ds_Erro", SqlDbType.VarChar, 8000)).Value = erro;
                Conexao.Open();

                Comando.ExecuteNonQuery();
            }
            

            throw new ApplicationException(erro);
        }


        public static void Mensagem(string mensagem)
        {

            using (var conexao = new SqlConnection(Servidor.Context))
            {

                var Comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", Conexao);
                Conexao.Open();

                if ((int) Comando.ExecuteScalar() != 0) return;

                var _retorno = SqlContext.Pipe;
                _retorno.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem);
            }

        }

        public static void RetornaReader(SqlDataReader dataReader)
        {
            var _retorno = SqlContext.Pipe;
            _retorno.Send(dataReader);
        }
    }

    public class Ret : Exception
    {
        public Ret(string str) : base(str)
        {      
        }
    }
}

Código-fonte da Procedure do CLR (C#) stpJob_Backup

using System;
using System.Data.SqlTypes;
using Bibliotecas.Model;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpJob_Backup(SqlString Ds_Servidor, SqlString Ds_Diretorio_Destino, SqlBoolean Fl_Arquivo_Unico)
    {


        try
        {

            var comando = @"
$ServerNameList = """ + Ds_Servidor.Value + @"""
[System.Reflection.Assembly]::LoadWithPartialName(""Microsoft.SqlServer.Smo"") | Out-Null

$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

foreach ($ServerName in $ServerNameList)
{

    Try
    {
        $objSQLConnection.ConnectionString = ""Server=$ServerName;Integrated Security=SSPI;""
        Write-Host ""Tentando se conectar na instância do servidor $ServerName..."" -NoNewline
        $objSQLConnection.Open() | Out-Null
        Write-Host ""Conectado.""
        $objSQLConnection.Close()
    }
    Catch
    {
        Write-Host -BackgroundColor Red -ForegroundColor White ""Falha""
        $errText = $Error[0].ToString()
        if ($errText.Contains(""network-related""))
            {Write-Host ""Erro de conexão à instância. Por favor, verifique o nome do servidor digitado, porta ou firewall.""}

        Write-Host $errText
        
        continue

    }

    $OutputFolder = """ + Ds_Diretorio_Destino.Value + @"""
    $DoesFolderExist = Test-Path $OutputFolder
    $null = if (!$DoesFolderExist){MKDIR ""$OutputFolder""}

    $srv = New-Object ""Microsoft.SqlServer.Management.Smo.Server"" $ServerName";


            if (Fl_Arquivo_Unico.Value)
            {
                comando += @"
    $srv.JobServer.Jobs | foreach {$_.Script() + ""GO`r`n""} | out-file ""$OutputFolder\jobs.sql""";
            }
            else
            {
                comando += @"
    $srv.JobServer.Jobs | foreach-object -process {out-file -filepath $(""$OutputFolder\"" + $($_.Name -replace '\\', '') + "".sql"") -inputobject $_.Script() }";
            }

            comando += @"
}";


            var saida = Utils.ExecutaScriptPowerShell(comando);
            Retorno.Mensagem(saida);


        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message);
        }
    }

};

Exemplos de uso

Agora vou demonstrar como utilizar a Stored Procedure criada para exportar o script SQL de todos os jobs do SQL Server Agent. Esses são os jobs atuais da instância.

Exemplo 1:

Neste exemplo, vou demonstrar como exportar o script de criação de todos os jobs da instância, sendo 1 arquivo sql para cada job.

EXEC CLR.dbo.stpJob_Backup
    @Ds_Servidor = N'VM-DBA', -- nvarchar(max)
    @Ds_Diretorio_Destino = N'C:\Temp\Jobs', -- nvarchar(max)
    @Fl_Arquivo_Unico = 0 -- bit

Resultado:

Exemplo 2:

Neste exemplo, vou demonstrar como exportar o script de criação de todos os jobs da instância, sendo 1 arquivo sql contendo o script de todos os jobs.

EXEC CLR.dbo.stpJob_Backup
    @Ds_Servidor = N'VM-DBA', -- nvarchar(max)
    @Ds_Diretorio_Destino = N'C:\Temp\Jobs', -- nvarchar(max)
    @Fl_Arquivo_Unico = 1 -- bit

Resultado:

Utilizando apenas o PowerShell e OLE Automation

Caso você não queira utilizar o CLR para esta solução e deseja utilizar apenas o PowerShell pelo banco de dados, você também pode fazê-lo.

Pré-requisitos

Como pré-requisitos para utilizar a procedure abaixo, precisaremos de algumas funções para manipulação de arquivos. Para isso, utilizaremos o OLE Automation para manipular esses arquivos. Para saber mais sobre isso, veja o post Operações com arquivos utilizando OLE Automation no SQL Server.

stpEscreve_Arquivo_FSO
Visualizar código-fonte

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (
    @String VARCHAR(MAX),
    @Ds_Arquivo VARCHAR(1501)
)
AS
BEGIN

    DECLARE
        @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Command VARCHAR(1000),
        @hr INT

    SET NOCOUNT ON

    SELECT
        @strErrorMessage = 'opening the File System Object'
    
    EXECUTE @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT

    
    IF @HR = 0
        SELECT
            @objErrorObject = @objFileSystem,
            @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objFileSystem,
            'CreateTextFile',
            @objTextStream OUT,
            @Ds_Arquivo,
            2,
            True

    IF @HR = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Write',
            NULL,
            @String

    
    IF @HR = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Close'

    
    IF @hr <> 0
    BEGIN
    
        DECLARE
            @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT
    
        EXECUTE sp_OAGetErrorInfo
            @objErrorObject,
            @source OUTPUT,
            @Description OUTPUT,
            @Helpfile OUTPUT,
            @HelpID OUTPUT
        
        
        SELECT
            @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
        RAISERROR (@strErrorMessage,16,1)
        
    END
    
    
    EXECUTE sp_OADestroy
        @objTextStream
    
    EXECUTE sp_OADestroy
        @objTextStream
        
END

fncArquivo_Existe_FSO
Visualizar código-fonte

CREATE FUNCTION [dbo].[fncArquivo_Existe_FSO] (
    @strArquivo VARCHAR(1000)
)
RETURNS INT 
AS 
BEGIN

    DECLARE	
        @hr INT,
        @objFileSystem INT,
        @retorno INT,
        @source VARCHAR(250),
        @description VARCHAR(2000)


    EXEC @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT
        
    IF @hr <> 0
    BEGIN
    
        EXEC sp_OAGetErrorInfo
            @objFileSystem,
            @source OUT,
            @description OUT
            
        RETURN 0
        
    END

    EXEC @hr = sp_OAMethod
        @objFileSystem,
        'FileExists',
        @retorno OUT,
        @strArquivo
        
    IF (@hr <> 0)
    BEGIN
    
        EXEC sp_OAGetErrorInfo
            @objFileSystem,
            @source OUT,
            @description OUT
            
        EXEC sp_OADestroy
            @objFileSystem
            
        RETURN 0
        
    END
    
    
    EXEC sp_OADestroy
        @objFileSystem
        
        
    RETURN @retorno

END

stpApaga_Arquivo_FSO
Visualizar código-fonte

CREATE PROCEDURE [dbo].[stpApaga_Arquivo_FSO] (@strArquivo VARCHAR(1000))
AS 
BEGIN

    DECLARE	
        @hr INT,
        @objFileSystem INT,
        @source VARCHAR(250),
        @description VARCHAR(2000)


    EXEC @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT
        
    IF @hr <> 0
    BEGIN
    
        EXEC sp_OAGetErrorInfo
            @objFileSystem,
            @source OUT,
            @description OUT
        
    END

    
    IF (dbo.fncArquivo_Existe_FSO(@strArquivo) = 1)
    BEGIN
    
        EXEC @hr = sp_OAMethod
            @objFileSystem,
            'DeleteFile',
            NULL,
            @strArquivo
            
    END
    
    
    IF (@hr <> 0)
    BEGIN
    
        EXEC sp_OAGetErrorInfo
            @objFileSystem,
            @source OUT,
            @description OUT
            
        EXEC sp_OADestroy
            @objFileSystem
        
    END
    
    
    EXEC sp_OADestroy
        @objFileSystem
    

END

Stored Procedure stpExecuta_Script_Powershell

CREATE PROCEDURE [dbo].[stpExecuta_Script_Powershell]
    @Ds_Script [varchar](MAX),
    @Fl_Apaga_Script [bit] = 1
AS
BEGIN
    

    SET NOCOUNT ON
    

    DECLARE 
        @QuebraLinha VARCHAR(10) = CHAR(13) + CHAR(10),
        @arquivo VARCHAR(MAX),
        @diretorio VARCHAR(MAX) = 'C:\Temp\',
        @scriptPS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(50)) + '.ps1',
        @caminho VARCHAR(MAX)


    SET @caminho = @diretorio + @scriptPS
        
    
    EXEC dbo.stpEscreve_Arquivo_FSO
        @String = @Ds_Script, -- varchar(max)
        @Ds_Arquivo = @caminho -- varchar(1501)
    
    SET @scriptPS = @diretorio + @scriptPS
        
        
    DECLARE @cmd VARCHAR(4000)
    SET @cmd = 'powershell -ExecutionPolicy Unrestricted -File "' + @scriptPS + '"'
    
    
    DECLARE @Retorno TABLE (Ds_Texto VARCHAR(MAX))
    
    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell @cmd
    
    
    -- Apaga o script gerado
    IF (@Fl_Apaga_Script = 1)
    BEGIN
    
        EXEC dbo.stpApaga_Arquivo_FSO
            @strArquivo = @scriptPS -- varchar(1000)
        
    END
    

    SELECT * FROM @Retorno

    
END

Exemplo de uso:

DECLARE @Script VARCHAR(MAX) = '
$ServerNameList = "VM-DBA"
$OutputFolder = "C:\Teste\Jobs\"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

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

$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

foreach ($ServerName in $ServerNameList)
{

    Try
    {
        $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
        Write-Host "Tentando se conectar na instância do servidor $ServerName..." -NoNewline
        $objSQLConnection.Open() | Out-Null
        Write-Host "Conectado."
        $objSQLConnection.Close()
    }
    Catch
    {
        Write-Host -BackgroundColor Red -ForegroundColor White "Falha"
        $errText = $Error[0].ToString()
        if ($errText.Contains("network-related"))
            {Write-Host "Erro de conexão à instância. Por favor, verifique o nome do servidor digitado, porta ou firewall."}

        Write-Host $errText
        
        continue

    }

    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

    # Arquivo único com todos os jobs
    # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "$OutputFolder\jobs.sql"

    # Um arquivo por job
    $srv.JobServer.Jobs | foreach-object -process {out-file -filepath $("$OutputFolder\" + $($_.Name -replace "\\", "") + ".sql") -inputobject $_.Script() }

}'


EXEC dbo.stpExecuta_Script_Powershell
    @Ds_Script = @Script, -- varchar(max)
    @Fl_Apaga_Script = 0 -- bit

Resultado:

Utilizando apenas PowerShell

Se você quiser utilizar apenas script PowerShell manualmente, segue abaixo o script completo.

# Nome da sua máquina
$ServerNameList = "VM-DBA"

# Diretório para salvar os scripts
$OutputFolder = "C:\Jobs\"

$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

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

$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

foreach ($ServerName in $ServerNameList)
{

    Try
    {
        $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
        Write-Host "Tentando se conectar na instância do servidor $ServerName..." -NoNewline
        $objSQLConnection.Open() | Out-Null
        Write-Host "Conectado."
        $objSQLConnection.Close()
    }
    Catch
    {
        Write-Host -BackgroundColor Red -ForegroundColor White "Falha"
        $errText = $Error[0].ToString()
        if ($errText.Contains("network-related"))
            {Write-Host "Erro de conexão à instância. Por favor, verifique o nome do servidor digitado, porta ou firewall."}

        Write-Host $errText
        
        continue

    }

    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
    
    # Arquivo único com todos os jobs
    # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "$OutputFolder\jobs.sql"

    # Um arquivo por job
    $srv.JobServer.Jobs | foreach-object -process {out-file -filepath $("$OutputFolder\" + $($_.Name -replace "\\", "") + ".sql") -inputobject $_.Script() }

}

É isso aí, pessoal.
Um grande abraço e até a próxima.