Como agendar e automatizar backups de bancos de dados do SQL Server no SQL Server Express

Este artigo descreve como usar um script Transact-SQL juntamente com o Agendador de tarefas do Windows para automatizar backups de bancos de dados do SQL Server Express de forma agendada.

Versão original do produto: SQL Server
Número original do KB: 2019698

Resumo

As edições do SQL Server Express não oferecem uma maneira de agendar tarefas ou planos de manutenção porque o componente SQL Server Agent não está incluído nessas editions. Portanto, você precisa seguir uma abordagem diferente para fazer backup de bancos de dados ao usar essas edições.

No momento, os usuários do SQL Server Express podem fazer backup de bancos de dados usando um dos seguintes métodos:

Use o SQL Server Management Studio ou o Azure Data Studio. Para obter mais informações sobre como usar essas ferramentas para fazer backup de um banco de dados, examine os seguintes links:

• Criar um backup completo do Banco de dados

• Tutorial: como fazer backup e restaurar bancos de dados usando o Azure Data Studio

• Use um script Transact-SQL que utilize a família de comandos BACKUP DATABASE. Para obter mais informações, confira BACKUP (Transact-SQL).

Este artigo descreve como usar um script Transact-SQL juntamente com o Agendador de tarefas do Windows para automatizar backups de bancos de dados do SQL Server Express de forma agendada.

Mais informações

Você deve seguir estas três etapas para fazer backup de bancos de dados do SQL Server usando o Agendador de tarefas do Windows:

Etapa A: criar um procedimento armazenado para fazer backup de seus bancos de dados.

Conecte-se à instância do SQL Express e crie um procedimento armazenado de sp_BackupDatabases no banco de dados mestre usando o script no seguinte local:

SQL_Express_Backups

Etapa B: Baixe a ferramenta SQLCMD (se aplicável).

O utilitário sqlcmd permite inserir instruções Transact-SQL, procedimentos do sistema e arquivos de script. No SQL Server 2014 e versões inferiores, o utilitário é enviado como parte do produto. A partir do SQL Server 2016, o utilitário sqlcmd é oferecido como um download separado. Para obter mais informações, examine o Utilitário sqlcmd.

Etapa C: criar arquivo em lotes usando o editor de texto.

Em um editor de texto, crie um arquivo em lotes chamado Sqlbackup.bat e copie o texto de um dos seguintes exemplos para esse arquivo, dependendo do cenário:

• Todos os cenários abaixo usam D:\SQLBackups como espaço reservado. O script precisa ser ajustado para a unidade certa e o local da pasta de Backup em seu ambiente.

• Se você estiver usando a autenticação do SQL, verifique se o acesso à pasta está restrito a usuários autorizados, pois as senhas são armazenadas em texto não criptografado.

Exemplo 1: backups completos de todos bancos de dados na instância nomeada local de SQLEXPRESS usando a Autenticação do Windows.

 // Sqlbackup.bat
 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Exemplo 2: backups diferenciais de todos os bancos de dados na instância nomeada local de SQLEXPRESS usando SQLLogin e sua senha.

 // Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType='D'"

Exemplo 3: backups de log de todos os bancos de dados na instância nomeada local de SQLEXPRESS usando a Autenticação do Windows

 // Sqlbackup.bat
 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Exemplo 4: backups completos do banco de dados USERDB na instância nomeada local de SQLEXPRESS usando a Autenticação do Windows

 // Sqlbackup.bat
 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Da mesma forma, você pode fazer um backup diferencial de USERDB colando "D" para o parâmetro @backupType e pode fazer um Backup de log de USERDB colando "L" para o parâmetro @backupType.

Estapa D: agendar um trabalho usando o Agendador de tarefas do Windows para executar o arquivo em lotes que você criou na etapa B. Para isso, siga estas etapas:

1. No computador que está executando o SQL Server Express, clique em Iniciar e, na caixa de texto, digite Agendador de tarefas.

   

2. Em Melhor correspondência, clique em Agendador de tarefas para iniciá-lo.

3. No Agendador de tarefas, clique com o botão direito do mouse em Biblioteca de agendamento de tarefas e clique em Criar tarefa básica….

4. Insira o nome da nova tarefa (por exemplo: SQLBackup) e clique em Avançar.

5. Selecione Diário para o Acionador de Tarefa e clique em Avançar.

6. Defina a recorrência para um dia e clique em Avançar.

7. Selecione Iniciar um programa como ação e clique em Avançar.

8. Clique em Procurar, no arquivo em lotes que você criou na etapa B e, em seguida, clique em Abrir.

9. Marque a caixa de diálogo Abrir a caixa de diálogo Propriedades desta tarefa quando eu clicar em Concluir.

10. Selecione a guia Geral,

    1. Revise as opções de segurança e verifique o seguinte para a conta de usuário que executa a tarefa (listada em Ao executar a tarefa, use a seguinte conta de usuário:)

       A conta deve ter pelo menos permissões de leitura e execução para iniciar o utilitário sqlcmd. Além disso,

       • se estiver autenticação do Windows no arquivo em lotes, certifique-se de dar permissões ao proprietário das permissões de tarefa para fazer backups do SQL.

       • Se estiver usando a autenticação SQL no arquivo em lotes, o usuário do SQL deverá ter as permissões necessárias para fazer backups do SQL.

    2. Ajuste outras configurações de acordo com seus requisitos.

Lembre-se do seguinte ao usar o procedimento documentado neste artigo:

• O serviço Agendador de tarefas do Windows deve estar em execução no momento em que o trabalho está agendado para ser executado. Recomendamos que você defina o tipo de inicialização desse serviço como Automático. Isso garante que o serviço seja executado mesmo que haja uma reinicialização.

• Deve haver bastante espaço na unidade em que os backups estão sendo gravados. Recomendamos que você limpe os arquivos antigos na pasta de backup regularmente para não ficar sem espaço em disco. O script não contém a lógica para limpar arquivos antigos.

Referências adicionais

Visão geral do Agendador de tarefas