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

Olá! Este é mais um post da série que tem o intuito de mostrar uma, das várias maneiras, que você pode tentar para recuperar dados que você deletou do seu SQL Server e não tem o famoso backup FULL!

No último e primeiro post da série eu criei um cenário de exemplo e apresentei a função: fn_dump_dblog! Como um dos meus objetivos com este blog é explicar os porquês, e não apenas dar soluções prontas, vou mostrar mais alguns detalhes desta função para que você entenda um pouco mais como ela ajuda nesta história e vamos utilizar ela para encontrar nossos registros deletados! Vamos nessa!

A Barreira de ler o backup do log foi superada e agora é hora de achar nosso DELETE.  Existe tanta informação na fn_dump_dblog, que eu precisaria de um série de posts para explicar cada coluna, mas estou assumindo que você já fez o dever de casa e tem algum conhecimento internals sobre isso.

Das colunas que ela retorna, as que nos interessa são estas:

• Current LSN
  É o LSN (Log Sequence Number) daquele registro no log. Na verdade, nem precisamos dela. Mas gosto de deixá-la apenas porque isso seria como a chave primária desta tabela.
• Operation
  Indica a operação a que esse registro se refere. Geralmente começa com “LOP_” + alguma coisa que podemos deduzir o que significa. LOP provavelmente vem de “Log OPeration”. Nada confirmado, isto é só um achismo meu.  No nosso caso, o que estamos procurando é pela operation LOP_DELETE_ROWS (advinha o que signfica?)
  
• Context
  Pelo nome, essa nos dá um pouco mais de detalhes de onde a operação que foi feita, ou as circunstâncias. Neste caso de um DELETE de uma tabela com a chave primária criada como um índice cluster, estamos interessados em LCX_MARK_AS_GHOST e LCX_CLUSTERED , que pelos nomes indicam se o delete foi feito, de fato, removendo os registro da tabela, ou apenas marcando como um ghost record. E de novo, um achismo meu: “LCX” parece vir de “Log ConteXt”.
• AllocUnitId
  Uma tabela pode ter um ou mais allocation units, dependendo do particionamento, tipo de dados, índices, etc. Este é o campo que vamos usar para achar somente os registros da tabela que queremos. Para encontrar o Allocation Unit da sua tabela:

  SELECT
      AU.allocation_unit_id
  FROM
      sys.partitions P
      JOIN
      sys.allocation_units AU ON AU.container_id = P.hobt_id
  WHERE
      P.object_id = OBJECT_ID('dbo.Cliente') AND P.index_id <= 1

• RowLog Contents 0
  Este é a nossa linha, registro, seja lá como você chama aí. É aqui que estão todos os valores das colunas, porém, em formato binário! Por isso, ele inicia com 0x, que é um indicativo da representação hexadecimal de um tipo de dados binários. E lembre-se, que a cada dois dígitos hexadecimais, temos 1 byte. Por exemplo, 0xFF é o byte FF (255). 0x01FF é o byte 0x01 e 0xFF, e por aí vai.

Conforme eu já informei, dependendo do seu backup, esta função pode trazer milhares de linhas. E como já deve ter percebido o SELECT nela não é rápido. Por isso, é interessante separar somente os registros com os quais queria trabalhar. Neste script eu já faço tudo isso:

Aqui fica um outro ALERTA: Executar isso em produção sem saber exatamente o que está fazendo, pode causar extrema atividade de disco e CPU, impactando em suas queries! Tente usar uma outra instância, se possível (uma de teste, no Azure, etc.)! Execute por sua própria conta e risco!

USE TheSqlTimes
GO

DROP TABLE IF EXISTS dbo.LogDeletes

SELECT
     Lsn = [Current LSN]
    ,Operation
    ,Context
    ,AllocUnitId
    ,Registro = [RowLog Contents 0]
INTO
    LogDeletes
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) T
WHERE
    AllocUnitId IN (
        SELECT
            AU.allocation_unit_id
        FROM
            sys.partitions P
            JOIN
            sys.allocation_units AU ON AU.container_id = P.hobt_id
        WHERE
            P.object_id = OBJECT_ID('dbo.Cliente') AND P.index_id <= 1
    )
    AND
    Operation = 'LOP_DELETE_ROWS'
    AND
    Context  IN ('LCX_CLUSTERED','LCX_MARK_AS_GHOST')

Em nosso cenário, esta execução me trouxe exatamente 10 mil linhas! Coincidência (lembre-se que no último post que deletamos 10 mil linhas)? No Recovery FULL, o comando DELETE gera registros no log para cada linha que está sendo deletada (ao contrário do TRUNCATE TABLE, que loga cada página). Com os filtros corretos, eu conseguir achar exatamente estas linhas:

Resultado do mesmo script acima

Aqui é importante lembrar que, em um cenário real, você pode ter além das linhas que deletou, logs de outras operações, por exemplo, de outros DELETEs. Você vai precisar analisar cada caso para tomar as ações corretas. O importante aqui é: Sem o Backup FULL já cheguei nos meus registros e o número de linhas me deu mais esperança!

Então, desde que deletamos os dados, já conseguimos, sem o Backup FULL, recuperar a mesma quantidade de linhas deletadas! Será que estamos corretos até aqui? No próximo post, vamos ver como transformamos isso de volta para o que conhecemos normalmente, que são as colunas desses registros!

Spoiler: Aproveite e revise o material do Paul Randal sobre a anatomia de um registro. Você vai precisar dessa base. Os links estão no primeiro post da série.

Rodrigo Ribeiro Gomes

DBA Team Leader na Power Tuning