SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell)

Views: 5.376 views

In this article

Reading Time: 8 minutes

Hello everybody!
All right?

In this post I will demonstrate how to back up all SQL Server Agent jobs via command line (CLR C # or Powershell) and export the results to SQL scripts. You can choose to generate 1 script for each job or 1 single script with all jobs in your instance.

During the migration from SQL Server 2008 R2 to 2014 this script was especially useful since in the report server instance there were little more than 700 jobs and the task of manually saving each script was not feasible. Backing up / restoring the msdb database could be a solution (although I don't find it very recommended), but since these were different versions of SQL Server, we discarded this hypothesis.

Remember that before you can restore jobs in the new environment, you must have created logins on the new instance. If you do not know how to backup logins and their permissions, please visit the post. SQL Server - How to transfer logins between instances by backing up users, logins, and permissions.

Prerequisites

As prerequisites for using the Stored Procedure stpJob_Backup, you will also need to create the following objects:

Run ScriptPowerShell (Click here to access the post)
View source

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;

}

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;

}

Server.cs file source code
View source

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;

            }
        }

    }

}

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;

}

Return class source code (Click here to access the post)
View source

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)
        {      
        }
    }
}

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)

{

}

CLR Procedure (C #) source code 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);
        }
    }

};

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);

}

};

Examples of use

Now I will demonstrate how to use the Stored Procedure created to export the SQL script from all SQL Server Agent jobs. These are the current instance jobs.

Example 1:

In this example, I will demonstrate how to export the creation script for all instance jobs, being 1 sql file for each 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

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

Result:

Example 2:

In this example, I will demonstrate how to export the creation script of all instance jobs, being 1 sql file containing the script of all 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

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

Result:

Using PowerShell and OLE Automation Only

If you do not want to use CLR for this solution and only want to use PowerShell for the database, you can do so as well.

Prerequisites

As prerequisites for using the procedure below, we will need some file manipulation functions. For this, we will use OLE Automation to manipulate these files. To learn more about this, see the post. File Operations Using OLE Automation in SQL Server.

stpWrite_File_File
View source

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

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (

@String VARCHAR(MAX),

@Ds_Arquivo VARCHAR(1501)

)

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,

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

fncFile_Exists_File
View source

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

CREATE FUNCTION [dbo].[fncArquivo_Existe_FSO] (

@strArquivo VARCHAR(1000)

)

RETURNS INT

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_File_File
View source

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

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

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

CREATE PROCEDURE [dbo].[stpExecuta_Script_Powershell]

@Ds_Script [varchar](MAX),

@Fl_Apaga_Script [bit] = 1

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

Example of use:

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

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

Result:

Using PowerShell Only

If you only want to use PowerShell script manually, follow the full script below.

# 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() }

}

# 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() }

}

That's it, guys.
A big hug and until next time.