Recuperando dados deletados do SQL Server sem Backup Full – Parte 1

06/04/2020

0 Comments

Post 1/6. Este post é parte da série: Recuperando dados deletados sem Backup Full

Recuperando dados deletados do SQL Server sem Backup Full – Parte 1
Recuperando dados deletados do SQL Server sem Backup Full – Parte 2
Recuperando dados deletados do SQL Server sem Backup Full – Parte 3
Recuperando dados deletados do SQL Server sem Backup Full – Parte 4
Recuperando dados deletados do SQL Server sem Backup Full – Parte 5
Recuperando dados deletados do SQL Server sem Backup Full – Parte 6

Tempo de leitura estimado: 4 minutos

Não é de hoje que eu me deparo com situações onde há uma necessidade de recuperação de dados e não há backup FULL. Não existe uma receita de bolo pra isso. Você precisa ter cartas na manga e analisar cada situação.

Nesta série eu vou mostrar uma das minhas cartas para essa situação:

Deletei dados usando o comando DELETE FROM sem querer! E não tenho backup FULL! O que eu faço?

Tudo que eu vou falar aqui já foi aplicado pelo menos duas vezes por mim em cenários de produção.

E cabe aqui ressaltar: Em todas as técnicas que irei mostrar, eu sabia exatamente o que estava fazendo e apliquei muita teoria! Portanto, sempre faça por sua conta e risco. Além disso, tudo isso se aplica em um ambiente SEM COMPRESSÃO de linha, ou de página, do SQL Server. Por isso, tenha atenção!

Irei mostrar algumas técnicas que dependem de um conhecimento sólido do internals do SQL Server, especialmente, estes assuntos:

Anatomia de um registro
E é claro que a melhor referência para este assunto é o Paul Randal, que além do SQL Server Internals, um livro que recomendo muito, você pode conferir neste post TOP que ele fez pra explicar! Pra quem não conhece ele, ele só foi quem escreveu diversas partes do SQL Server…
Arquitetura Básica do Log de Transação e Recovery Model
Aqui, siga com a documentação oficial para aprender mais.
Internals do Log de Transação
Este você vai encontrar uma gama muito boa de detalhes nos posts do Paul Randal também, além de um capítulo só de log de transação no livro do SQL Server Internals.

Os testes foram realizados em um SQL Server 2017, mas para as versões anteriores, o procedimento é o mesmo. Caso eu note alguma peculiaridade, eu irei avisar.

Dito as premissas, vamos iniciar explicando o cenário e preparar o ambiente de testes…

O cenário

A situação era a seguinte: Alguém deletou alguns registros de uma tabela importante por volta das 16h25. Não tinha Backup Full, e nem os logs anteriores a este FULL (eles foram apagados incorretamente). Os backups de logs estavam sendo feitos normalmente a cada 10 minutos.

Se eu tivesse o backup FULL, era fácil! Era só restaurar FULL + logs até as 16h24 e pegar os dados!

Você pode utilizar este script para criar uma estrutura de teste, inserir 10 mil linhas em uma tabela de testes com diversos dados aleatórios, “perder” o backup full e fazer um backup de log (que também não é necessário).

Então suponha que seja 16h25 e alguém tenha deletado todos os 10 mil registros:

E suponha, que 5 minutos depois o backup de log tenha sido executado pelas rotinas:

Foi exatamente o que ocorreu! A diferença foi que até que essa situação chegasse até mim, já tinha se passado horas. Então, já haviam muitos outros backups. Após descobrir o horário em que foi feito eu determinei o backup correto e copiei ele para outro local para que eu pudesse iniciar as tentativas. No nosso cenário de exemplo, vou utilizar o próprio arquivo T:\Backup16h30.trn

E por que este backup de log? Ora, como o banco está em Recovery FULL, O SQL Server sempre loga cada linha deletada no log de transações, gerando “Log Records” que contém os dados dessa linha. E ainda, ainda devido ao recovery model, ele só vai liberar ou reusar este espaço, quando um backup de log destes registros forem feitos. Ou seja, no próximo backup de log!

OK, você tem o arquivo! E agora!? O backup de log é um arquivo binário, se abrir, mesmo num editor hexadecimal, as coisas não fazem muito sentido:

E aí é que entra a nossa primeira arma secreta: fn_dump_dblog. E advinha onde eu aprendi há muito tempo sobre ela? Isso mesmo, neste post do Paul Randal!

Basicamente, esta função me permite especificar um arquivo de backup de log, e então ela monta em um resultset com toda a estrutura do log de transação. Uma linha para cada “Log Record”. Tem uma função que faz a mesma coisa, a fn_dblog, só que ao invés de pegar do backup, ela pega do log atual do banco em que você está conectado.

ATENÇÃO: Tenha cuidado! Esta função vai ler todo o backup de log! Isto é, ela pode trazer centenas de milhares de linhas.

USE TheSqlTimes
GO

SELECT
   *
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'T:\Backup16h30.trn',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

USE TheSqlTimes

SELECT

FROM

fn_dump_dblog (

NULL, NULL, N'DISK', 1, N'T:\Backup16h30.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

A sintaxe é auto explicativa, bem como boa parte das colunas. Você precisa apenas trocar o caminho. Os detalhes, deixo com as referências que coloquei.

O Paul Randal adverte sobre o uso destas duas funções em seus posts. Antes de usá-las, entenda estes riscos!

Agora que você tem o log em uma estrutura que já é acostumado a usar (ou deveria ser), superamos o primeiro obstáculo! No próximo post, eu conto como podemos seguir a partir daí!

Fontes e links úteis: