Utilizando Full Backups no gerenciamento de Logs

Quando as coisas estão indo bem, não há necessidade de estar particularmente consciente do que o log de transações faz ou mesmo como ele funciona. Quando as coisas vão mal, uma compreensão do log de transações é importante para a tomada de ações corretivas, principalmente quando uma restauração pontual de um banco de dados é necessária... urgente!

Neste artigo iremos realizar uma análise de como e por que devemos fazer backups de log quando trabalhamos em modo de recuperação completa (Full backup), e como devemos executar uma restauração do banco de dados usando esses arquivos de backup do log, em conjunto com um backup completo. O modo de recuperação completa aceita restauração de bases de dados a qualquer momento dentro de um backup de log que esteja disponível e, supondo que um backup do log de cauda (ou tail log backup) possa ser feito, até o momento da última transação confirmada, antes da ocorrência da falha.

O que é registrado nos logs?

No modo de recuperação completa, todas as operações são registradas, inclusive as instruções de INSERT, UPDATE e DELETE. Isso significa que para cada linha alterada, haverá um registro de log que irá descrever tudo o que ocorreu, indo do ID da transação que realizou a instrução até o momento de início e termino dela, quais páginas foram alteradas, quais as alterações de dados que foram feitas, dentre outras informações importantes que devem ser mantidas.

As operações que podem ser minimamente registadas são as do tipo SELECT INTO, INSERT BULK e CREATE INDEX, pois estas ainda estão sendo processadas em modo de recuperação completa, mas o backup é realizado de uma forma um pouco diferente nesses casos. As linhas afetadas por estas operações não são registradas individualmente, ao invés disso, apenas as páginas de banco de dados são registradas. Isso reduz o logging de tais operações, assegurando ao mesmo tempo que as informações ainda existam para a necessidade de realização de uma reversão, restauração e com isso, refazer o ponto no tempo que for preciso. As diferenças no registro das operações registradas minimamente, quando se trabalha em modo BULK_LOGGED, serão discutidos posteriormente.

Por que o backup do log de transações?

No modo de recuperação completa, apenas um backup do log pode causar o truncamento do log, devido a isso, o log de transações irá realizar um registro total e completo das operações realizadas desde a última vez que o log de transações foi realizado do backup. Uma vez que todas as operações são totalmente conectadas, o arquivo de log pode crescer consideravelmente, além de muito rapidamente, em sistemas ocupados (que são os sistemas em uso contínuo, como os que estão em produção). Portanto, quando se trabalha em modo de recuperação completa, é vital que realizemos backups do log de transações regularmente, além de backups completos e, opcionalmente, backups diferenciais. Quando realizamos backups completos das nossas bases de dados, devemos fazer também o backup dos logs de transações, pois só assim teremos uma maior probabilidade de realizarmos uma restauração mais concisa. Caso não o façamos, como resultado, o log de transações não será truncado, e ele passará a crescer a ponto de ficar sem espaço em disco, fazendo com que o SQL Server pare de trabalhar.

O truncamento do log ocorrerá assim que o backup do log for feito, assumindo que um checkpoint tenha ocorrido desde o backup anterior e que não há outros fatores que estejam atrasando o truncamento, como por exemplo, um backup de dados ou uma operação de restauração.

Backups COPY_ONLY do log de transações

Os backups de log de transações do tipo COPY_ONLY não truncam o log de transações, este é o tipo de backup que existe "independentemente" do esquema de backups de log normal, ele não quebra a cadeia de backup de log. Em suma, os backups do log de transações executam o objetivo duplo que é o de permitir a restauração e recuperação para um ponto anterior no tempo, bem como controlar o tamanho do log das transações. Provavelmente, a causa mais comum de questões relacionadas com o log de transações está funcionando em modo de recuperação completa e simplesmente não fazem backups de log, ou não fazem backups de log com frequência suficiente para controlar o tamanho do arquivo de log de transações. Caso não tenhamos certeza se devemos ou não fazer os backups do log de transações em um determinado banco de dados, então podemos simplesmente interrogar a tabela backupset no banco de dados MSDB, usando uma consulta semelhante a apresentada pela Listagem 1.

Listagem 1. Questionamento se um backup deve ser realizado.

Na coluna [type], um backup de banco de dados é representado pela letra D, já um backup de log ou um diferencial é representado pela letra L. Notem que uma vez que os dados da tabela backupset são manipulados sem afetar o backup e restauram o comportamento, podemos verificar suas “descobertas” a partir desta consulta, por meio da consulta realizada com a instrução sys.database_recovery_status para que possamos ver o valor do último backup com last_log_backup_lsn, ou mesmo os sys.databases, que são tabelas para ver o valor do log_reuse_wait_desc.

Fazendo backup do log de transações

Como dito anteriormente, realizar um backup do log de transações sem criarmos pelo menos um backup completo, não é possível, na verdade, se temos um banco de dados que está em modo de recuperação total, mas que nunca foi feito um backup, então ele realmente não vai estar trabalhando em modo de recuperação total. O banco de dados estará no modo de auto-truncado até que o primeiro backup completo seja realizado.

Todos os backups de banco de dados, sendo este completo ou de outra forma, são realizados utilizando o comando BACKUP. No entanto, em sua forma mais básica, que é muitas vezes como ela é usada, o comando para executar um backup completo no disco é realizado de acordo com o apresentado pela Listagem 2.

Listagem 2. Realização de um backup completo.

No caso deste ser o primeiro backup a ser executada, o arquivo DatabaseName.bak será criado no diretório especificado, no caso dele já existir, então o comportamento padrão será o de acrescentar backups subsequentes para esse arquivo. Para substituirmos esse comportamento, e estipularmos que qualquer arquivo existente deva ser substituído, podemos usar a opção INIT, como apresentado pela Listagem 3.

Listagem 3. Substituição de backups.

Após cada backup completo regular, irão haver backups de log frequentes, onde o comando de base é muito semelhante, como apresentado pela Listagem 4.

Listagem 4. Realização de backups.

É evidente que o backup dos dados e dos arquivos de log não devem ser armazenados na mesma unidade que hospedamos os arquivos em uso real. Se essa unidade sofre falhas de hardwares, todas as suas cópias serão perdidas juntamente com os arquivos originais, e os backups não terão valia alguma. Os arquivos devem ser copiados para um dispositivo separado, ou o backup em uma unidade local, mas de forma espelhada.

Como tratado desde o início da série de artigos, os backups de logs podem ser realizados com bastante frequência, dependendo do caso, em tais casos, a fim de evitar a necessidade de restaurarmos um grande número de arquivos de log de transações, podemos optar por adotarmos um esquema de backup que consiste em backups completos intercalados com backups diferenciais, intercalados com backups do log de transações. Na realidade, o esquema de backups é muitas vezes mais que um compromisso entre o ideal e a prática, entre uma avaliação do verdadeiro risco de perda de dados, e qual será o custo da empresa, e os custos envolvidos na redução desse risco. A frequência de backups do log também pode ser ditada pelo número de transações a qual o banco de dados está sujeito. Para bancos de dados com grande carga de informações, pode ser necessário fazer o backup com uma maior frequência, a fim de controlar o tamanho do log.

A cadeia de log e como podemos quebrá-la

A fim de recuperarmos um banco de dados para um ponto no tempo, seja para o final de um backup do log particular ou para um ponto no tempo dentro de um backup de um log particular, deve existir uma cadeia ininterrupta contendo os registros de log, a partir do primeiro backup do log criado depois de um backup completo (ou de um diferencial), até o ponto da falha. Esta é conhecida como a cadeia de logs. Há muitas maneiras consideráveis de quebrar uma cadeia de log, e se ocorrer, isso significa que só seremos capazes de recuperar o banco de dados para o momento do backup do log criado antes do evento onde a cadeia foi quebrada. Em suma, quebrar a cadeia não é uma boa ideia, caso estejamos nos preocupando com a capacidade de restaurarmos nossos dados. Dentre as formas de quebrar uma cadeia de logs, duas são mais comuns, que são:

• Perda ou corrupção de um arquivo de backup do log de transações - só seremos capazes de recuperar o último backup do log anterior que esteja bom. A cadeia de log então começa de novo no próximo backup completo ou diferencial que esteja em bom estado.
• Alternar para o modo de recuperação simples – caso nunca mudemos o modo de recuperação de integral para o modo SIMPLE, isso irá quebrar a cadeia de log como um ponto de verificação e o log de transações poderá ser imediatamente truncado. Quando e se retornarmos ao modo completo, teremos que tomar outro backup completo para reiniciarmos a cadeia de log.

Cauda Log Backups (Tail log)

Contanto que tenhamos um backup completo recente e uma cadeia de log completos, podemos recuperar nosso banco de dados para o estado em que existiu no final do backup do log antes de qualquer falha. Entretanto, suponhamos que tomemos o backup do log de transações a cada hora, e que uma falha ocorre às 2:30. Poderíamos perder 30 minutos no valor dos dados, e, na verdade, se a falha for tão crítica que o log de transações em execução fique irrecuperável, então, esta é a quantidade de dados que você vai perder. No entanto, por vezes, o log de transações em execução ainda pode estar disponível, mesmo que os arquivos de dados não estejam, especialmente se o log de transações estiver contido numa unidade dedicada à parte. Se este for o caso, devemos fazer backups do log de transações em execução ou seja, realizarmos um backup final dos registros de log gerados desde o último backup do log. Isso irá capturar os registros restantes no arquivo de registro em execução, até o ponto da ocorrência da falha. Este evento é chamado de backup do log de cauda e é a última ação que devemos realizar antes de iniciarmos as operações de restauração e recuperação.

Backups do log de cauda e operações mínimas registradas

Se os arquivos de dados não estiverem mais disponíveis, como resultado da falha de banco de dados, e o final do log contiver operações minimamente registradas, então não será possível fazer um backup do log de cauda, pois isso exigiria o acesso às extensões dos dados alterados no arquivo de dados. Se o banco de dados que desejamos restaurar estiver on-line, em seguida, a cauda do log é realizada de acordo com a apresentada pela Listagem 5.

Listagem 5. Operação de recuperação por calda de log.

A opção NORECOVERY coloca o banco de dados em um estado de restauração e assume que a próxima ação a ser executada é uma restauração. Se tivermos certeza de que o arquivo de log está danificado, a documentação sugere que, como último recurso, tentemos fazer um backup do log de cauda com a seguinte instrução, a qual apresentamos de acordo com a Listagem 6.

Listagem 6. Recuperação de log de calda como último recurso.

Se os arquivos de banco de dados e os dados estiverem danificados, mas os registros estiverem disponíveis, a Microsoft recomenda a reconstrução da base de dados principal e, em seguida, a realização do backup do último log ativo.

Executando restauração e recuperação

Tendo realizado um backup do log de cauda, o próximo passo será restaurar o último backup completo, em seguida, restaurarmos a sequência completa de backup de arquivos de log, incluindo o backup do log de cauda. A sintaxe básica para essa sequência de operações de restauração é apresentada através da Listagem 7.

Listagem 7. Sintaxe básica para restauração.

Se ao restaurarmos a base, omitirmos a opção WITH NORECOVERY, por padrão, o comando irá proceder apenas como RESTAURAR. Em outras palavras, o SQL Server tentará reconciliar os dados e arquivos, adiantando as transações concluídas e, em seguida, a reversão de transações não concluídas. Ao especificarmos WITH NORECOVERY, estamos instruindo o SQL Server a entrar numa sequência de restauração e que demais operações devem ser passadas adiante, antes que qualquer reversão possa ser realizada. Depois de restaurarmos o último backup na sequência de restauração, o banco de dados poderá ser recuperado como com a instrução de acordo com a Listagem 8.

Listagem 8. Recuperação da base de dados.

Restauração Completa em ponto de falha

Partindo do princípio de que o log de transações em execução pode ser alcançado após uma falha do banco de dados, talvez causada por uma falha de hardware, então, em teoria, deve ser possível a sua restauração e recuperação do seu banco de dados até o ponto de falha, usando as seguintes etapas:

• Façamos o backup do final do log;
• Restauremos o backup completo mais recente (junto com o diferencial, caso tenha);
• Restaurem, cada um dos backups do log de transações que forem tomados após o backup completo e concluídos antes do momento da falha;
• Restaurem o backup do log de cauda;
• Recuperem o banco de dados;

No momento seguiremos com um simples exemplo, apresentado pela Listagem 9, onde utilizamos um conjunto de backups que consiste em um backup completo e um backup do log de transações, e mostramos como realizar uma restauração completa. Para executarmos este código, primeiro precisaremos recriar o banco de dados TesteDB e em seguida, inserir algumas linhas de amostra de dados. Além disso, precisaremos criar um diretório chamado "Backups" no diretório local C.

Listagem 9. Fazer o backup e restaurar a partir de um conjunto de backups.

Mesmo que tenhamos apresentado este exemplo, como segue pela Listagem 9, ao realizarmos backup em disco, é uma má ideia usarmos este esquema, porque, o arquivo de backup irá crescer rapidamente. Na prática, é muito comum que cada arquivo de backup e log de transações de backups completos sejam nomeados individualmente e, provavelmente, marcados com a data e hora na qual o backup foi feito. Apresentaremos a seguir, de acordo com a Listagem 10, um exemplo de melhor utilização dos backups full.

Listagem 10. Realizando backup e restauração a partir de arquivos de backup com nomes exclusivos.

Ponto de restauração com o Log do backup

Às vezes, infelizmente, pode não ser possível realizarmos uma restauração completa, como por exemplo, se o log de transações principal estiver disponível como resultado de uma falha. Neste caso, será necessário restaurarmos apenas o final do backup de log mais recente. É a necessidade de se preparar para esta eventualidade ou seja, uma falha da unidade que contém o log de transações, que determina quantas vezes são tomados os logs dos backups. Se obtermos backups a cada 15 minutos, então seremos expostos ao risco da perda de dados em 15 minutos. Imaginem que tenhamos realizado uma sequência de backups, como apresentada pela Listagem 11. Por causa deste demo, estamos substituindo os arquivos de backup anteriores, e a sequência de backup é, obviamente, muito reduzido do que seria na realidade.

Listagem 11. Série de backups do log.

Se uma falha ocorreu logo após às 2:30, talvez seja necessário restaurar o banco de dados para o estado no qual ele se encontrava no final do backup do log 2, realizado às 2:30 da manhã. A sequência de restauração, num exemplo como este é muito semelhante ao que vimos anteriormente, na Listagem 10, mas uma vez que um backup do final não seja possível e que só vai ser capaz de restaurar a um certo ponto, nós precisamos usar a opção STOPAT, como mostrado na Listagem 12.

Listagem 12. Restaurando a um ponto no tempo, usando STOPAT.

Uma vez que tivermos especificado um tempo STOPAT no futuro, este código vai avançar com todas as transações concluídas até o final do segundo log de transações. Como alternativa, é possível especificarmos um tempo STOPAT que esteja dentro do intervalo de tempo das transações registradas em um arquivo de log específico. Neste caso, o banco de dados será restaurado até a última transação confirmada no horário especificado. Isso é útil quando sabemos o tempo ao qual desejamos restaurar, mas não sabemos exatamente o que o log de backup contém nesse tempo. É também possível para restaurar uma transação marcada específica. Isso é útil quando, por exemplo, precisamos restaurar vários bancos de dados, acessados por uma determinada aplicação, a um ponto logicamente consistente.

Restauração após uma "Bad operation"

Fora do contexto de qualquer falha de banco de dados, pode ser necessário restaurarmos um backup de banco de dados, além de logs de transação, a fim de devolvermos um banco de dados para um determinado ponto no tempo, pouco antes de uma modificação errada dos dados tenha sido feita, como por exemplo, o truncamento de uma tabela. A nossa resposta a uma situação desse tipo dependerá da natureza do problema, se possível, podemos desconectar todos os usuários do banco de dados (após notificá-los, é claro!), e avaliarmos as implicações do que ocorreu com a base de dados. Em alguns casos, pode ser necessário para estimar o tempo que o problema ocorreu e, em seguida, fazer uma recuperação completa do banco de dados e usar os logs de um ponto no tempo para restauração. Uma vez que a restauração tenha sido feita, teríamos que notificar os usuários de que algumas operações podem ter sido perdidas, caso realmente ocorra.

É claro que, muitas vezes, não somos capazes de interromper o funcionamento normal de negócios desta forma, no caso de uma perda acidental de dados. Uma vez que o banco de dados principal esteja ainda funcionando e sendo acessado, podemos tentar restaurar um backup do banco de dados em modo de espera. Isso permite que mais backups de log sejam restaurados, mas ao contrário de quando se usa o NORECOVERY, o banco de dados ainda está legível. O esquema de restauração pode ser algo como se segue:

• Restauração de um backup do banco de dados, em modo de espera, ao lado do banco de dados principal;
• Avançar com os logs para o ponto logo antes da ocorrência da transação com falha, e a perda dos dados;
• Copie os dados perdidos em toda a base de dados principal e, em seguida, liberar a cópia restaurada.

Naturalmente, este processo não é necessariamente fácil, além do fato de poder ser muito demorado. A menos que tenhamos comprado um registro de uma ferramenta de leitura especializada, para podermos “interrogar” o backup do log diretamente, revirando todos os logs o que pode significar uma série de etapas que envolvem a restauração de um meticuloso registro, verificando os seus dados, restaurando um pouco mais, e assim por diante, até que tenhamos trabalhado até onde ocorreu exatamente o erro na transação. O Passo 3 pode ser difícil também, já que seria necessário a introdução de dados no sistema principal que não é necessariamente consistente com o estado atual do banco de dados, de modo que poderiam ocorrer problemas de integridade referencial. Vamos dar uma olhada em um exemplo que implementa os passos 1 e 2 acima. Primeiro, vamos começar do zero, executando o script TesteDB.sql para recriarmos o banco de dados TesteDB, e inserir 10 linhas de dados de teste para uma nova tabela LogTest. Na Listagem 13, nós simplesmente fazemos um backup completo.

Listagem 13. Backup completo do TesteDB.

Em seguida, iremos inserir uma nova linha de dados na tabela de LogTest, como podemos apresentar de acordo com a Listagem 14.

Listagem 14. Inserindo uma nova em TesteDB.

Agora temos um banco de dados TesteDB principal com 11 linhas na tabela de LogTest, e uma versão de backup com 10 linhas. Agora vamos capturar a modificação adicional num backup de log, como mostrado pela Listagem 15.

Listagem 15. Um backup do log de TesteDB.

Agora, iremos simular uma falsa "bad transaction", simplesmente por excluir a tabela de LogTest, após o que nós fazemos um backup do log final, como podemos ver pela Listagem 16.

Listagem 16. Criando falha, excluindo tabela de log.

A fim de tentarmos recuperar os dados perdidos, sem interromper o funcionamento normal da empresa, nós estamos tentando restaurar uma cópia do banco de dados TesteDB em modo de espera. Os dados e os arquivos de log para o banco de dados de espera, chamados como NovoTesteDB, são movidos para um diretório "Standby", diretório este que precisamos criar antecipadamente, vejamos então de acordo com a Listagem 17 como seria a restauração.

Listagem 17. Restaurar uma cópia de TesteDB no modo STANDBY.

Temos agora um novo banco de dados, chamado NovoTesteDB, o qual está em modo "Standby/read-only", como poderemos observar nos bancos de dados criados. A consulta realizada na tabela LogTest no banco de dados NovoTesteDB irá nos apresentar 10 linhas. No entanto, gostaríamos de obter a tabela de volta para o estado em que estávamos antes deste ser erroneamente descartado. Portanto, o próximo passo é a realização da restauração do backup do log para o banco de dados standby, como apresentado pela Listagem 18.

Listagem 18. Restaurando um backup do log para o banco de dados NovoTesteDB quando em espera.

Neste ponto, uma consulta contra NovoTesteDB nos mostra 11 linhas e podemos agora para copiar os dados para o outro lado no banco de dados principal. Se foi um passo além e restaurado o segundo backup do log, nós percebemos que tinha ido longe demais e a tabela estaria faltando no banco de dados de espera também.

Independente da vontade dos DBA’s, os desenvolvedores geralmente precisam ter acesso às bases de dados de produção para executar cargas de dados ad-hoc e mudanças necessárias. Neste ponto, é de responsabilidade conjunta do DBA e do desenvolvedor a garantia de que estas modificações decorram da melhor forma possível, e que assim não causem problemas que irão exigir o tipo de ação que acabamos de descrever. É claro que, a natureza exata da ação reparadora requerida depende da natureza do problema na transação.

Com isso chegamos ao fim deste artigo, onde cobrimos o básico com relação a backups e restaurações dos arquivos de log para bancos de dados que operam em modo de recuperação completa, que será a norma para muitos bancos de dados de produção. Para muitos dos DBA’s, a necessidade de realizar um point-in-time de restauração é um evento muitas vezes raro, mas é uma daquelas tarefas que, se for necessária, é absolutamente crítica e deve ser bem feita, onde estará em risco também a reputação do DBA. Se o log de transações não estiver disponível, ou se está restaurando a fim de reverter para algum ponto no tempo antes de uma "bad transaction" ocorrer, então, a situação torna-se mais complicada, mas esperamos que algumas das técnicas abordadas neste artigo possam ajudar. Até a próxima!

Links

Truncamentodas VLF’s
http://msdn.microsoft.com/en-gb/library/ms345414.aspx

Reconstrução da base dos dados
http://msdn.microsoft.com/en-us/library/ms190952.aspx