Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Visualizações: 5.946 views
Tempo de Leitura: 8 minutos

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

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

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

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

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.

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.

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

fncArquivo_Existe_FSO
Visualizar código-fonte

stpApaga_Arquivo_FSO
Visualizar código-fonte

Stored Procedure stpExecuta_Script_Powershell

Exemplo de uso:

Resultado:

Utilizando apenas PowerShell

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

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

SQL Server – Como fazer backup de todos os jobs do SQL Agent via linha de comando (CLR C# ou Powershell) backup restore sql server agent jobs command line

SQL Server – Como fazer backup de todos os jobs do SQL Agent via linha de comando (CLR C# ou Powershell) backup restore sql server agent jobs command line