A armadilha do Backup Diferencial – O que fazer para evitá-la ?

Boa Tarde Pessoal,

Essa semana estou finalizando uma turma do curso 2780 que trata de administração e manutenção do SQL Server 2005. Como sempre digo aos alunos, um dos capítulos mais importantes é o de Disaster Recovery que trata especificamente sobre backup. As possibilidades de backup disponíveis no SQL Server 2005 são muito interessantes. Além dos tradicionais FULL, Diferencial e Log, temos ainda os backups de Filegroup, Tail Log, Partial, etc que realmente ampliam bastante as estratégias de recuperação de dados em cenários de desastre.

Durante a aula, como é de praxe, discorro sobre o funcionamento do backup full, do backup diferencial e dos backup de log além de alguns cenários típicos. O uso do backup diferencial em conjunto com o backup de log é uma excelente combinação por permitir uma estratégia autocontigente, já que na perda de algum log, é possível avançar com o diferencial e na hipótese da perda do diferencial é possível avançar com os logs. Entretanto, especificamente no caso do backup diferencial, existem algumas armadilhas para quem utiliza apenas a estratégia FULL + Diferencial que nem sempre está tão aparente. Hoje falarei de um detalhezinho no diferencial que se não observado pode levar a impossibilidade de utilizá-lo em um processo de restauração

Como funciona o backup diferencial

Não vou entrar nos detalhes internos do backup diferencial e de como o SQL Server faz para saber o que copiar para o diferencial. O que podemos dizer é que o backup diferencial é simplesmente uma cópia de tudo o que mudou desde o último backup full realizado. Assim sendo, após a realização de um backup full, todas as páginas que tiverem sido modificadas irão compor o backup diferencial. No momento de restauração, um backup full é restaurado e logo em seguida aplica-se o diferencial para sobrescrever as páginas por uma versão mais recente. Vejamos alguns exemplos:

— Cria um banco de dados
CREATE DATABASE TesteDiferencial

— Muda o contexto do banco de dados
USE TesteDiferencial

— Cria uma tabela de Numeros
CREATE TABLE Numeros (Num INT NOT NULL)

— Insere alguns registros
INSERT INTO Numeros VALUES (01)
INSERT INTO Numeros VALUES (02)
INSERT INTO Numeros VALUES (03)
INSERT INTO Numeros VALUES (04)

— Efetua um backup full
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialF01.BAK’

— Insere mais alguns números
INSERT INTO Numeros VALUES (05)
INSERT INTO Numeros VALUES (06)
INSERT INTO Numeros VALUES (07)
INSERT INTO Numeros VALUES (08)

— Efetua um primeiro backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD01.DIF’
WITH DIFFERENTIAL

— Insere mais alguns números
INSERT INTO Numeros VALUES (09)
INSERT INTO Numeros VALUES (10)
INSERT INTO Numeros VALUES (11)
INSERT INTO Numeros VALUES (12)

— Efetua um segundo backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD02.DIF’
WITH DIFFERENTIAL

Como o backup diferencial corresponde a todas as páginas alteradas desde o último backup full, temos as seguintes distribuições em relação aos registros:

Backup Conteúdo (Números)
Full01 01, 02, 03, 04
Dif01 05, 06, 07, 08
Dif02 05, 06, 07, 08, 09, 10, 11, 12

Alguns podem perguntar se o segundo diferencial não deveria ter somente os números de 9 a 12. Lembre-se que um backup diferencial tem todos os dados desde o último backup full (e não desde o último backup diferencial). Como o backup full continha os números de 1 a 4 e os números de 5 a 12 foram inseridos entre o último backup full e o segundo diferencial, esse tem portanto os números de 5 a 12 e não de 9 a 12.

É possível confirmar as sequências através dos registros de LSN retornados pela instrução RESTORE HEADER ONLY. Ex:

— Verifica as marcas de LSN do Backup FULL
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialF01.BAK’

— Verifica as marcas de LSN do primeiro Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD01.DIF’

— Verifica as marcas de LSN do segundo Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD02.DIF’

Podemos ver o quadro esquemático conforme abaixo:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000014900001 19000000007600173 0
Dif01 19000000016100037 19000000017800001 19000000016100037 19000000007600173
Dif02 19000000018200004 19000000018500001 19000000018200004 19000000007600173

Não vou entrar no mérito das colunas FirstLSN, LastLSN e CheckpointLSN. A coluna DatabaseBackupLSN do backup full mostra o valor de 0 que significa que nenhum backup havia sido feito o que é bem natural, pois, trata-se do primeiro backup realizado no banco de dados. Se for observado a coluna CheckpointLSN do backupfull foi 19000000007600173, ou seja, no LSN 19000000007600173 foi marcado no log de transações que o backup full foi realizado. A coluna DatabaseBackupLSN dos dois backups diferenciais aponta exatamente para esse LSN, o que significa que os dois backups diferenciais são referentes ao primeiro backup full e por isso é possível restaurar o backup full e aplicar qualquer um dos dois diferenciais. A seguir, será mostrado como essas marcas se comportam após a realização de outros backups e inserções de dados.

— Insere mais alguns números
INSERT INTO Numeros VALUES (13)
INSERT INTO Numeros VALUES (14)
INSERT INTO Numeros VALUES (15)
INSERT INTO Numeros VALUES (16)

— Efetua um segundo backup full
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialF02.BAK’

— Insere mais alguns números
INSERT INTO Numeros VALUES (17)
INSERT INTO Numeros VALUES (18)
INSERT INTO Numeros VALUES (19)
INSERT INTO Numeros VALUES (20)

— Efetua um terceiro backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD03.DIF’
WITH DIFFERENTIAL

As marcas de LSN dos backups podem ser obtidas conforme os comandos abaixo:

— Verifica as marcas de LSN do segundo Backup FULL
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialF02.BAK’

— Verifica as marcas de LSN do terceiro Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD03.DIF’

As marcas são demonstradas na tabela abaixo:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full02 19000000019100042 19000000020800001 19000000019100042 19000000007600173
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

Se juntarmos todas as marcas teremos uma nova sequência:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000014900001 19000000007600173 0
Dif01 19000000016100037 19000000017800001 19000000016100037 19000000007600173
Dif02 19000000018200004 19000000018500001 19000000018200004 19000000007600173
Full02 19000000019100042 19000000020800001 19000000019100042 19000000007600173
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

Os dois primeiros backups diferenciais têm a coluna DatabaseBackupLSN apontando para o valor 19000000007600173 e como esse é o valor de Checkpoint do primeiro backup full, essa igualdade é o que permite afirmar que os dois backups diferenciais podem ser restaurados em conjunto com o primeiro backup full. O mesmo é válido para o terceiro backup diferencial e o segundo backup full já que ambos referenciam o LSN 19000000019100042. Essas referências também permitem afirmar que o terceiro backup diferencial não pode ser restaurado em conjunto com o primeiro backup full. O script abaixo demonstra que isso não é possível.

— Muda o contexto para o MASTER
USE MASTER

— Elimina o banco Teste
DROP DATABASE TesteDiferencial

— Restaura o primeiro backup full
RESTORE DATABASE TesteDiferencial
FROM DISK = ‘C:\TesteDiferencialF01.BAK’
WITH NORECOVERY

— Verifica o LSN dos arquivos
SELECT name, differential_base_lsn FROM sys.master_files
WHERE database_id = DB_ID(‘TesteDiferencial’) AND data_space_id > 0

O comando de SELECT mostra que o arquivo de dados está parado no LSN 19000000007600173 e que necessita prosseguir a partir desse LSN. A seguir, é tentado a restauração do terceiro backup diferencial.

— Restaura o terceiro backup diferencial
RESTORE DATABASE TesteDiferencial
FROM DISK = ‘C:\TesteDiferencialD03.DIF’
WITH RECOVERY

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Embora a mensagem não diga todos os detalhes, não é difícil descobrir qual é o erro com a tabela de marcas.

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000020800001 19000000007600173 0
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

O primeiro backup full termina no LSN 19000000020800001 e o terceiro diferencial inicia no LSN 19000000021900037. Nesse caso há um GAP entre os LSNs “cobertos” e não faria sentido mesmo que a restauração fosse possível. A coluna DatabaseBackupLSN confirma essa impossibilidade já que o valor dessa coluna para o terceiro diferencial é 19000000019100042 que não bate com o Checkpoint do primeiro backup full que é 19000000007600173.

Toda essa demonstração foi para dizer apenas que o backup diferencial dependerá sempre do último backup full realizado antes do backup diferencial. Caso esse backup full seja perdido, não será possível aproveitar o backup diferencial em um processo de restauração. Isso ficou evidente, pois, caso o segundo backup full fosse indisponibilizado, não seria possível utilizar o terceiro backup diferencial.

Guardar adequadamente o último backup full realizado antes do backup diferencial é imprescindível para uma estratégia que se baseie em backups diferenciais. Isso foi demonstrado e mesmo com tantas constatações, para alguns isso não passa de algo óbvio e no mínimo uma obrigação. Pode parecer até sem sentido uma possibilidade de não guardar esse backup, mas vejamos algumas situações que podem mudar isso.

Imagine que esses backups tenham sido realizados da seguinte forma:

Backup Descrição Dia de Realização
Full01 Política de backup full semanal Domingo às 23:00
Dif01 Política de backup diferencial diária Segunda-Feira às 18:00
Dif02 Política de backup diferencial diária Terça-Feira às 18:00
Full02 Situação adversa Quarta-Feira às 14:30
Dif03 Política de backup diferencial diária Quarta-Feira às 18:00

A política de backup baseia-se em FULL semanal e diferenciais diários (pode-se incluir alguns backups de log, mas não quis abordá-los). Se tudo funcionar como deve-se ser, um backup full é realizado aos domingos e mais seis diferenciais variando de segunda a sábado. Para que a restauração possa ser realizada com êxito, é necessário restaurar o backup full semanal e aplicar o último diferencial. Esse plano funcionará perfeitamente desde que não haja a presença de situações adversas como a realização de um backup full no meio da semana através de uma situação adversa, ou seja, algo fora da política.

Por que alguém irá realizar um backup full fora da política ? Pode parecer até injustificável, mas essa situação "adversa" pode sim ocorrer. Imagine que na quarta-feira em questão, o diretor de TI solicite um backup full da base para que o mesmo seja disponibilizado para uma consultoria internacional realizar um projeto de Data Mining ? Ou ainda que a equipe de desenvolvimento faça seus projetos com base em um backup da produção e necessitaram de um na quarta-feira ? Ou ainda que para resolver um problema de produção foi necessário um backup full da base em outro ambiente ? Ou que algum outro DBA efetuou um backup da produção antes de aplicar um script potencialmente duvidoso ?

Em todas essas situações, a política não estará comprometida, se esse backup full oriundo de uma situação adversa for guardado junto com os demais. O grande risco é que na maioria dessas situações isso dificilmente acontece. Ninguém se preocupará em guardar um backup full, pois, confiar-se-á no backup full da política semanal. O grande problema é que aquele backup full não poderá ser utilizado com alguns diferenciais já que haverá um full mais recente. Muito provavelmente isso só será percebido quando a necessidade de restauração vier à tona, mas nesse caso ele não funcionará. Essa é a armadilha que o backup diferencial prepara se não for corretamente utilizado.

Como evitar a armadilha do backup diferencial ?

Se o backup diferencial for utilizado para versões anteriores ao SQL Server 2005 realmente não existe saída senão guardar bem os backups fora da política. No caso do SQL Server 2005 e superiores, existe a opção pouco conhecida chamada COPY_ONLY que é perfeita para situações adversas que exijam um backup full eventual. Vejamos como ela funciona.

— Criação do banco de dados
CREATE DATABASE TesteDifCopyOnly

— Muda o contexto do banco de dados
USE TesteDifCopyOnly

— Cria uma tabela
CREATE TABLE Letras (Letra CHAR(1))

— Insere alguns valores
INSERT INTO Letras VALUES (‘A’)
INSERT INTO Letras VALUES (‘B’)
INSERT INTO Letras VALUES (‘C’)

— Efetua um backup full
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyF01.BAK’

— Insere mais valores
INSERT INTO Letras VALUES (‘D’)
INSERT INTO Letras VALUES (‘E’)
INSERT INTO Letras VALUES (‘F’)

— Faz um novo backup full com a opção COPY_ONLY
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyF02.BAK’
WITH COPY_ONLY

— Insere mais valores
INSERT INTO Letras VALUES (‘G’)
INSERT INTO Letras VALUES (‘H’)
INSERT INTO Letras VALUES (‘I’)

— Faz um backup diferencial
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyD01.DIF’
WITH DIFFERENTIAL

Os comandos abaixo demonstram as marcas de LSN desses backups.

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 20000000022800193 20000000030900001 20000000022800193 0
Full02 20000000032000037 20000000033700001 20000000032000037 20000000022800193
Dif01 20000000034000004 20000000034300001 20000000034000004 20000000022800193

O primeiro backup full foi gerado no LSN 20000000022800193 e embora tenha havido um segundo backup full entre o primeiro backup full e o primeiro diferencial, a opção COPY_ONLY não influenciou a referência do backup diferencial. Esse ainda referencia o primeiro backup full mesmo havendo um segundo backup full. A opção COPY_ONLY fez o backup "frio" e para o backup diferencial, o segundo backup full nunca existiu não alterando as referências. Assim sendo, o primeiro backup full possui as letras A,B e C e o backup diferencial possui as letras D, E, F, G, H e I e o segundo backup full possui as letras A, B, C, D, E e F. Se essa opção tivesse sido utilizada no exemplo anterior, a política de backup não seria afetada e o DIF03 poderia ser utilizado sem maiores problemas. Essa é uma forma de "escapar" da armadilha do backup diferencial. O script abaixo mostra que é plenamente possível utilizar essa estratégia:

— Muda o contexto de banco de dados
USE MASTER

— Exclui o banco de dados
DROP DATABASE TesteDifCopyOnly

— Restaura o primeiro backup full
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyF01.BAK’
WITH NORECOVERY

— Restaura o primeiro diferencial
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyD01.DIF’

— Verifica as letras cadastradas
SELECT Letra FROM TesteDifCopyOnly.dbo.Letras

A recuperação das letras de A a I mostra que a estratégia de restauração foi bem sucedida.

Algumas observações sobre a opção COPY_ONLY

O uso da opção COPY_ONLY e o backup "frio", ou seja, por debaixo dos panos pode parecer interessante, mas antes que alguém pense em utilizá-la como padrão em todos os backups, é necessário alertar para alguns efeitos colaterais. O segundo backup full pode ser restaurado sem problemas, mas não poderá ser utilizado para iniciar um processo mais completo de restauração. Ex:

— Muda o contexto de banco de dados
USE MASTER

— Exclui o banco de dados
DROP DATABASE TesteDifCopyOnly

— Restaura o segundo backup full
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyF02.BAK’
WITH NORECOVERY

— Restaura o primeiro diferencial
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyD01.DIF’

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Como o segundo backup full foi "frio", tecnicamente ele nunca existiu. Se ele nunca existiu, ele não pode ser utilizado como referência para o backup diferencial e por isso o processo de restauração não é concluído. É possível utilizar o backup full com a opção COPY_ONLY para restaurar um novo banco, mas sem aplicações de log posteriores. Mesmo com essa limitação, ela atenderia perfeitamente as demais situações adversas, pois, o que interessava para essas situações era apenas o backup full.

Alguns poderiam dizer que essa não é preocupação tão digna, pois, uma política bem planejada não iria contar apenas com backups diferenciais, mas provavelmente com logs e a realização de backups full intermediários não atrapalha a sequência de logs. Sim, de fato, isso é verdade, mas penso que as boas estratégias são aquelas que tem mais de uma possibilidade de restauração. Ter os backups diferenciais em conjunto com os backups de log permite dois caminhos de restauração, pois, na falha de algum log, o diferencial pode ser utilizado e se algum diferencial falhar, os logs podem ser utilizados. Isso só é realmente verdadeiro se as situações adversas forem contornadas com o uso do COPY_ONLY. Do contrário, já estaremos eliminando de saída a efetividade dos backups diferenciais e reduzindo as possibilidades de sucesso nos momentos em que os backups forem realmente necessários.

[ ]s,

Gustavo

5 Respostas para “A armadilha do Backup Diferencial – O que fazer para evitá-la ?

  1. Caramba!Então era por isso que eu não conseguia restaurar o backup…. :-|Muito obrigado pela explicação, foi muito útil!

  2. CaraDesculpa, mas essa noção do backup diferencial ser precedido da restauração do backup full é básica e está preconizada em todas as documentações do Produto. O Backup diferencial ele é acumulativo em relação ao Full e ele é a cópia de todas as extents que foram alteradas desde o Ultimo backup full. O título do seu artigo é pejorativo em relação à funcionalidade do mesmo, que é utilizada em uma variedade de instações de SQL Server.

  3. Realmente é uma situação que pode acontecer em qualquer ambiente.Muito bem lembrado o uso da funcionalidade do backup COPY_ONLY.Parabens,Muito bom o artigo e esclarecedor.

  4. Olá Wandenkolk,Não tenho nada contra o backup diferencial e o utilizo em vários ambientes por sua efetividade e por realmente constatar que ele funciona e que em conjunto com os logs garante uma boa segurança quando o mesmo for necessário. Quando utilizei a palavra "armadilha" não quis dizer que o backup diferencial é ruim. Como digo no início do artigo, a idéia é falar de um detalhe que no diferencial pode levar a problemas e não abandonar o backup diferencial.De fato não só as documentações, mas os próprios fóruns, webcasts, etc não cansam de repetir o que nós sabemos, que o backup full corresponde a tudo que foi alterado desde o último diferencial. A exemplo do primeiro feedback ("Então era por isso que eu não conseguia restaurar o backup") e de vários outros testemunhos nas aulas, percebe-se que situações adversas podem ocorrer. O quis demonstrar é apenas como evitá-las. Não creio que se trate de casos isolados, já que se a Microsoft colocou a opção COPY_ONLY justamente para situações como essa.Vou levar em consideração o termo "pejorativo" nos demais títulos dos próximos posts. Talvez outros também tenham tido essa impressão.[ ]s,Gustavo

  5. Eu não tive a impressão de título pejorativo, embora pense que a armadilha não esteja 100% envolvida com a estratégia de LSN que o diferencial utiliza para ser recuperado e sim com o comportamento não-calculado do DBA ao gerar um FULL sem a cláusula do COPY_ONLY.

    O artigo é excelente, e apesar de afirmar o que pra muita gente é óbvio, reforçou ainda mais a ideia de que não é só fazer um FULL e achar que tá tudo bem, prática bastante comum e na maioria das vezes, acontece por desatenção principalmente se feito numa eventualidade. E isso em momentos de desastre, acredite, pode significar o emprego do cidadão: Imagina o tiozinho que fez o backup full (em um diretório até fora do convencional) pra jogar no desenvolvimento, apaga de lá e a rotina vai gerando os diferenciais. Até chegar no outro FULL, se acontecer algum problema e essa bak não tiver em mãos…

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s