Agendar e automatizar backups de bancos de dados do SQL Server Express

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

Resumo

SQL Server Express não inclui SQL Server Agent, portanto, você não pode agendar trabalhos de backup ou planos de manutenção da maneira que puder em outras edições SQL Server. Para automatizar backups no SQL Server Express, combine três componentes: o procedimento armazenado sp_BackupDatabases, o utilitário de linha de comando sqlcmd e o Agendador de Tarefas do Windows. Este artigo apresenta as etapas para configurar backups completos, diferenciais e de log de transações agendados de bancos de dados do SQL Server Express. Ele também explica como confirmar se um backup agendado foi executado.

Este artigo aplica-se somente às edições SQL Server Express. Ele não se aplica a SQL Server Express LocalDB.

Por que SQL Server Express não pode agendar backups com SQL Server Agent

SQL Server Agent é o componente que executa trabalhos agendados e planos de manutenção e não está incluído no SQL Server Express. Para obter uma comparação completa do que cada edição inclui, consulte Edições e recursos compatíveis de SQL Server.

Sem SQL Server Agent, você ainda pode fazer backup de um banco de dados expresso SQL Server sob demanda usando qualquer uma das seguintes ferramentas:

Utilitário sqlcmd
SQL Server Management Studio (SSMS)
Extensão MSSQL para Visual Studio Code
Um script Transact-SQL que usa a família de comandos BACKUP (Transact-SQL)

Para executar esses backups em um agendamento em vez de sob demanda, use Windows Agendador de Tarefas como o mecanismo de agendamento, conforme descrito nas etapas a seguir. Para obter informações sobre tipos de backup e estratégia, consulte Backup e restauração de bancos de dados SQL Server.

Pré-requisitos

Antes de começar, verifique se você tem:

Uma instância em execução do SQL Server Express. Os exemplos neste artigo usam a instância nomeada padrão. .\SQLEXPRESS
Participação na função fixa de servidor sysadmin nessa instância, o que é necessário para criar um procedimento armazenado no banco de dados master.
Uma pasta ou unidade local com espaço livre suficiente para armazenar os arquivos de backup. Os exemplos usam D:\SQLBackups.
Permissão para criar tarefas agendadas no computador que executa SQL Server Express.

Etapa 1: Criar o procedimento armazenado sp_BackupDatabases

sp_BackupDatabasesé um procedimento armazenado fornecido Microsoft que executa o comando apropriado BACKUP em um banco de dados ou em cada banco de dados online na instância. Você o cria uma vez no master banco de dados.

Baixe o script de SQL_Express_Backups.sql e salve-o localmente, por exemplo, como C:\Temp\SQL_Express_Backups.sql. Em seguida, crie o procedimento armazenado usando o SSMS ou sqlcmd.

Criar o procedimento armazenado usando o SSMS

Abra o SSMS e conecte-se à instância do SQL Server Express. No nome do servidor, insira .\SQLEXPRESSe selecione Conectar.
Selecione Arquivo>Aberto>, selecione o arquivo SQL_Express_Backups.sql que você salvou e, em seguida, selecione Abrir.
Confirme se a lista de banco de dados na barra de ferramentas mostra master. O script começa com USE [master], portanto, ele direciona o banco de dados correto automaticamente.
Selecione Executar (ou pressione F5). O painel Mensagens relata Commands completed successfully.

Criar o procedimento armazenado usando sqlcmd

Execute o seguinte comando de um prompt de comando:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Confirme se o procedimento armazenado foi criado

Execute a consulta a seguir na instância. Ele retornará uma linha se o procedimento armazenado existir:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

parâmetros de sp_BackupDatabases

O procedimento armazenado aceita os seguintes parâmetros:

Parâmetro	Obrigatório	Description
`@backupLocation`	Sim	A pasta que recebe os arquivos de backup, por exemplo `D:\SQLBackups\`. A pasta já deve existir.
`@backupType`	Sim	O tipo de backup: `F` para completo, `D` para diferencial ou `L` para log de transações.
`@databaseName`	No	O banco de dados para fazer backup. Se você omitir esse parâmetro, o procedimento armazenado faz backup de todos os bancos de dados online na instância, exceto os bancos de dados excluídos pelo script.

O procedimento armazenado nunca faz backup tempdb. Ele também ignora os bancos de dados de exemplo Northwind, pubs e AdventureWorks para todos os tipos de backup, e ignora master em backups diferenciais e de log porque esses tipos de backup não são compatíveis com o banco de dados master.

Importante

Os nomes de banco de dados excluídos são codificados no script. Se você tiver um banco de dados que use um desses nomes, o procedimento armazenado o ignorará sem relatar um erro. Para fazer backup desse tipo de banco de dados, edite as listas DELETE @DBs WHERE DBNAME IN (...) no script antes de criar o procedimento armazenado.

Etapa 2: Instalar o utilitário sqlcmd

O sqlcmd utilitário permite executar instruções Transact-SQL, procedimentos armazenados do sistema e arquivos de script da linha de comando. Ele é instalado com o SSMS e também está disponível como um download autônomo para computadores que não têm SSMS. Para instalá-lo, consulte Baixar e instalar o utilitário sqlcmd.

Para confirmar se sqlcmd está instalado e disponível, execute sqlcmd -? em um prompt de comando.

A pasta que contém o sqlcmd executável geralmente é adicionada à variável de Path ambiente quando você instala SQL Server ou as ferramentas autônomas. Se sqlcmd -? informar que o comando não é reconhecido, adicione a pasta à variável Path ou especifique o caminho completo para o utilitário no arquivo em lote.

Etapa 3: Criar o arquivo em lote Sqlbackup.bat

Em um editor de texto, crie um arquivo em lote chamado Sqlbackup.bat. Copie o texto de um dos exemplos a seguir para esse arquivo, dependendo do seu cenário.

Considere os seguintes pontos antes de escolher um exemplo:

Cada exemplo usa D:\SQLBackups como espaço reservado. Altere o caminho para a unidade e a pasta de backup que você deseja usar em seu ambiente e verifique se a pasta existe.
Se você usar a Autenticação do SQL Server, a senha será armazenada em texto sem criptografia no arquivo em lote. Restrinja o acesso à pasta que contém o arquivo em lote somente para usuários autorizados.

Exemplo 1: backups completos de todos os bancos de dados usando Windows Autenticação

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

Exemplo 2: backups diferenciais de todos os bancos de dados usando a Autenticação do SQL Server

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

Observação

Para executar backups, o login deve ser membro da função de banco de dados fixa db_backupoperator ou db_owner em cada banco de dados do qual você faz backup, ou membro da função de servidor fixa sysadmin. Para obter mais informações, consulte funções no nível do banco de dados.

Exemplo 3: Backups de log de transações de todos os bancos de dados usando Windows Autenticação

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

Os backups de log de transações exigem o modelo de recuperação completo ou bulk-logged e exigem pelo menos um backup completo anterior. Para obter mais informações, confira Modelos de recuperação () (SQL Server).

Exemplo 4: backup completo de um único banco de dados usando a Autenticação Windows

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

Para fazer um backup diferencial de USERDB, altere o @backupType parâmetro para D. Para fazer um backup de log de transações, altere-o para L.

A opção -b faz com que sqlcmd seja encerrado e retorne um valor DOS ERRORLEVEL de 1 quando ocorre um erro do SQL Server com nível de severidade superior a 10. Sem -b, sqlcmd retorna 0 mesmo quando o backup falha e o Agendador de Tarefas relata a tarefa como bem-sucedida.

Antes de continuar, execute Sqlbackup.bat manualmente em um prompt de comando e confirme se os arquivos de backup aparecem na pasta de backup. Corrigir erros agora é mais fácil do que diagnosticá-los posteriormente por meio do Agendador de Tarefas.

Etapa 4: Agendar o arquivo em lote no Agendador de Tarefas Windows

Siga estas etapas para executar Sqlbackup.bat em um agendamento:

No computador que está executando o SQL Server Express, selecione Iniciar e digite Agendador de Tarefas na caixa de texto.
Em Melhor correspondência, selecione Agendador de Tarefas para iniciá-lo.
No Agendador de Tarefas, clique com o botão direito do mouse no Agendador de Tarefas (Local) e selecione Criar Tarefa Básica.
Insira um nome para a nova tarefa (por exemplo, SQLBackup) e selecione Avançar.
Selecione Diariamente para o gatilho de tarefa e selecione Avançar.
Defina a recorrência como um dia e selecione Avançar.
Selecione Iniciar um programa como a ação e selecione Avançar.
Selecione Procurar, selecione o arquivo Sqlbackup.bat que você criou na Etapa 3: Criar o arquivo em lote Sqlbackup.bat e, em seguida, selecione Abrir.
Marque a caixa de seleção Abrir a caixa de diálogo Propriedades para esta tarefa ao clicar em Concluir e, em seguida, selecione Concluir.
Na guia Geral , examine as opções de segurança e confirme o seguinte para a conta listada em Ao executar a tarefa, use a seguinte conta de usuário:
- A conta tem pelo menos permissões de leitura e execução para executar o sqlcmd utilitário.
- Se o arquivo em lote usar Windows Autenticação, a conta terá permissão para fazer backup dos bancos de dados SQL Server.
- Se o arquivo em lote usar a Autenticação do SQL Server, o login do SQL Server no arquivo em lote terá permissão para fazer backup dos bancos de dados.
Ajuste as configurações restantes para corresponder aos seus requisitos e selecione OK.

Dica

Como teste, execute Sqlbackup.bat em um prompt de comando iniciado por você com a mesma conta de usuário que é proprietária da tarefa. Esta etapa confirma que a conta tem as permissões necessárias antes da execução do agendamento.

Para obter mais informações sobre as opções de agendamento, consulte o Agendador de Tarefas.

Verificar se o backup agendado foi executado

Após a primeira execução agendada, confirme se o backup foi bem-sucedido:

Verifique a pasta de backup para novos arquivos .bak (backups completos e diferenciais) ou arquivos .trn (backups de log de transações). O procedimento armazenado adiciona um carimbo de data e hora a cada nome de arquivo.
No Agendador de Tarefas, selecione Biblioteca do Agendador de Tarefas, selecione sua tarefa e examine a coluna Resultado da Última Execução . Esta coluna mostra o código de saída retornado pelo arquivo em lote. Como os exemplos usam a opção -b, um valor de 0x1 significa que o comando de backup falhou. Para obter outros valores, consulte o erro do Agendador de Tarefas e as constantes de êxito.
Consulte o histórico de backup na instância:
```
SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
```
Essas tabelas fazem parte do histórico de backup que SQL Server mantém no msdb banco de dados. Para obter mais informações, consulte o histórico de backup e informações de cabeçalho (SQL Server).