Backup do transaction log do SQL Server
O SQL Server armazena registros de todas as alterações do banco de dados a fim de minimizar a perda de dados caso ocorra um desastre ou uma corrupção do banco de dados. Esse tipo de registro é chamado de transaction log ou T-log e pode ser usado para backup e recuperação. Nesse artigo, discutiremos como criar e gerenciar seus backups de transaction log no Microsoft SQL Server.
O que é um transaction log?
Também chamado de T-log, é uma lista em execução de transações que alteram os dados no banco de dados ou a estrutura do banco de dados. Cada modificação do banco de dados é uma transação. Como as alterações são feitas em um banco de dados, os registros de log são adicionados ao arquivo de transaction log.
É um componente essencial do banco de dados do SQL Server, se houver uma falha no sistema, é necessário o T-log para retornar o banco de dados a um estado consistente antes do ponto de falha.
O que é o backup do transaction log?
É um backup de todas as transações que ocorreram no banco de dados desde o seu último backup. Os backups do T-log podem ser considerados como um backup incremental, são o tipo mais granular de backup do SQL Server permitindo restaurações pontuais e fornecendo proteção máxima dos dados.
No caso de um desastre de banco de dados, pode-se recuperar o backup completo do banco de dados, o último backup diferencial e o backup do transaction log criado após o backup diferencial até o ponto necessário.
Backup e truncamento de T-log em diferentes modelos de recuperação
Os registros no transaction log tendem a crescer com o tempo e podem ocupar todo o disco, impedindo novas modificações no banco de dados.
O truncamento de T-log limpa o log marcando o espaço no arquivo de log como reutilizável, para que novas transações possam ser gravadas no arquivo de log. Porém ele não libera espaço em disco, ao invés disso disponibiliza o espaço de log usado anteriormente para novas transações. Deve-se evitar encolher os arquivos de log físico, pois esse processo pode causar problemas de desempenho do banco de dados.
Depois do backup, o transaction log é limpo e o espaço fica disponível para novas transações, sem backups deles, os arquivos de log continuarão a crescer até que a unidade fique sem espaço.
Backups do T-log mais frequentes reduzem a chance de perder dados.
Um modelo de log em massa pode não permitir uma recuperação pontual se uma operação minimamente registrada tiver ocorrido. É por isso que, na maioria dos casos, não é recomendado usar um modelo de log em massa.
Para um banco de dados no modelo de recuperação simples, o SQL Server trunca automaticamente o log em pontos de verificação. Em termos de gerenciamento de log, esse modelo é de fato o mais simples. A desvantagem no entanto, é que a restauração do banco de dados só pode ser realizada até o momento do último backup de banco de dados completo ou diferencial. Se os dados mudarem com frequência, isso pode resultar em perda de dados irreversível.
No entanto, em um caso incomum, pode-se ter um banco de dados no modelo de recuperação completa executando backups de T-log a cada 4 horas e um banco de dados no modelo de recuperação simples executando backups diferenciais a cada hora. Sob essas circunstâncias, o banco de dados no modelo de recuperação simples pode sofrer menos perda de dados.
Recomendações para backup do T-log
Uma estratégia de backup e recuperação do SQL Server deve minimizar o potencial de perda de dados e maximizar a disponibilidade de dados. Ao mesmo tempo, deve refletir requisitos de negócios específicos e ser capaz de se adaptar de acordo com a disponibilidade de recursos.
Primeiro, começar com a criação de um backup completo do banco de dados. Depois, entender que os backups do transaction log devem ser usados em conjunto com backups completos e diferenciais do SQL Server (o backup diferencial é opcional, mas altamente recomendável). O backup completo serve como ponto de partida para backups de T-log e diferenciais subsequentes. Não é possível criar backups de T-log e diferenciais se não houver o backup completo do banco de dados.
A frequência apropriada para executar backups de log dependerá do RTO e RPO necessários, essas métricas refletem a quantidade de perda de dados que a empresa pode tolerar sem sofrer graves danos ao negócio. Eles também devem refletir quantos backups de log podem ser armazenados, gerenciados e restaurados, de acordo com os recursos financeiros e tecnológicos disponíveis.
Se ocorrer um desastre, primeiro recuperará o backup completo do banco de dados, em seguida o último backup diferencial. Depois disso, restaurar os backups necessários do transaction log em sequência até o momento necessário .
Mesmo que um backup de transaction log compreenda 30 minutos de atividade (por exemplo, 15:00 às 15:30), é possível informar ao SQL Server a restauração apenas das transações até às 15:22. Esse é um recurso importante para restaurações point-in-time, pois permite que o administrador restaure um banco de dados até um ponto antes da ocorrência do problema.
Para limitar o número de backups de log a serem restaurados, é necessário fazer o backup de seus dados rotineiramente. Por exemplo, agendando um backup de banco de dados completo semanal e backups de banco de dados diferenciais diários.
Se estiver executando um banco de dados usando um modelo de recuperação que não trunca automaticamente os logs de transação (modelos completos ou em massa), será necessário executar backups do transaction log. Caso contrário, as transações antigas nunca serão limpas e a quantidade de dados no transaction log continuará a crescer. Eventualmente, ficando sem espaço em disco impedindo novas modificações no banco de dados.
Recomenda-se também não armazenar backups do transaction log do SQL Server no mesmo local que um banco de dados de produção.
Backup do transaction log com o Microsoft SQL Server
O Microsoft SQL Server fornece um conjunto de opções que permitem executar backups do T-log, porém requer programação via linha de comando e o monitoramento é básico:
- T-SQL: a linguagem de script do SQL Server que fornece comandos BACKUP que podem ser executados por script e executados a partir de sua ferramenta de consulta.
- SQL Server Agent: serviço de agendamento do MS SQL Server que permite executar scripts no agendamento, disponível para todas as edições do SQL Server, exceto Express.
- SQL Server Management Studio: um console de gerenciamento gráfico para o MS SQL Server que fornece uma interface fácil de usar para gerenciar e gerar ações de script, como backups, para seus bancos de dados.
Backup do transaction log com a nossa solução
A nossa solução permite fazer backup do Microsoft SQL Server diretamente em datacenter Microsoft em nuvem e automatizar backups completos, diferenciais e de T-log do SQL Server, conseguimos:
- Executar backups locais, em nuvem ou híbridos (local + nuvem).
- Conectar-se diretamente à instância do SQL Server.
- Selecionar os bancos de dados para backup (todos os bancos de dados, todos os bancos de dados de usuários ou uma seleção personalizada de bancos de dados).
- Especificar políticas de compactação, criptografia e retenção.
- Agendamentos recorrentes com tempos personalizados completos e diferenciais para cada rotina, incluindo backup do transaction log.
Conclusão
Ao proteger seus bancos de dados contra perda de dados e procurar gerenciar seus objetivos de tempo de recuperação, considere a possibilidade de realizar backups regulares. O cronograma e o modelo de recuperação de banco de dados dependerão dos requisitos de negócios da sua empresa, como a quantidade de perda de dados que a empresa pode tolerar.
Se o banco de dados for executado no modelo de recuperação completo ou em log em massa, a realização de backups do T-log com mais frequência reduzirá a possível perda de dados e evitará o excesso de espaço de armazenamento pelo crescente arquivo de log. No entanto, o modo de recuperação em log em massa não pode fornecer restauração pontual se operações de log em massa tiverem ocorrido desde o backup de log mais recente.
O Microsoft SQL Server permite automatizar tarefas de backup usando o SQL Server Agent, mas se você não quiser criar e gerenciar scripts T-SQL, necessitar backup dos bancos de dados em nuvem, vale considerar usar a nossa solução de backup, que fornece backup mais simples e flexível do SQL Server, além de gerenciamento e monitoramento. Inicie uma avaliação gratuita e veja como podemos melhorar muito a sua experiência de backup do SQL Server.
Referências
- Recovery Models (SQL Server) – SQL Server – Microsoft Docs. Acessado em 15/04/2019.