Lendo um SQL Server transaction log

O SQL Server transaction log contém registros descrevendo alterações feitas a uma base de dados. Ele armazena informação suficiente para recuperar uma base de dados em um tempo específico no tempo, para refazer ou desfazer uma alteração. Mas, como visualizar seu conteúdo, localizar uma transação específica, ver o que aconteceu e reverter uma alteração como uma remoção de registro acidental

Para visualizar o que está armazenado em um transaction log ativo, ou em um backup de transaction log não é tão simples

Abrir o arquivo LDF e TRN em um editor binário ele exibirá registros inteligíveis impossibilitando a leitura direta. Por exemplo, isso é um trecho que deum arquivo LDF:

Usando fn_dblog

fn_dblog é uma função não documentada no SQL Server que lê a parte ativa de um transaction log

Vamos ver os passos necessários para fazer e ver o resultado apresentado

1. Execute a função fn_dblog

Select * FROM sys.fn_dblog(NULL,NULL)



A função em si retorna 129 colunas, é recomendado selecionar apenas colunas específicas e filtrar os resultados para um tipo de operação específico, se possível

2. A partir do conjunto de resultados retornados pelo fn_dblog, localize as transações que você quer visualizar

Para visualizar transações de linhas inseridas, execute:

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');



Para visualizar transações de registros apagados, execute:

SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');



3. Localize a coluna que armazena os valores inseridos ou apagados – veja o RowLog Contents 0 , RowLog Contents 1 , RowLog Contents 2 , RowLog Contents 3 , RowLog Contents 4, Description e Log Record

Dados de registros são armazenados em diferentes colunas para diferentes tipos de operação. Para ser capaz de ver exatamente o que você precisa usar a função fn_dblog, você tem que saber o conteúdo da coluna para cada tipo de transação. Como não há nenhuma documentação oficial para esta função, isso não é tão fácil

As linhas inseridas e excluídas são exibidas em valores hexadecimais. Para ser capaz de quebrá-las em campos que você tem que saber o formato que é usado, compreender os bits de status, saber o número total de colunas e assim por diante

4. Converter dados binários em dados da tabela levando em conta o tipo de coluna da tabela. Note que o mecanismo de conversão é diferente para diferente tipo de dados

fn_dbLog é excelente, poderoso e é uma função grátis, mas ele tem algumas limitações – registros de log de leitura para mudanças na estrutura objeto é complexo, pois geralmente envolve reconstruir o estado de várias tabelas do sistema, somente a parte ativa de um transaction log on-line é lido , e não há nenhuma reconstrução para UPDATE/BLOB

Como a operação UPDATE é minimamente logado no transaction log, sem valores antigos ou novos, apenas o que foi alterado no registro (por exemplo, SQL Server pode registrar que “G” foi alterado para “F”, quando na verdade o valor “GLOAT” era alterado em “FLOAT”), você tem que reconstruir manualmente o estado anterior à atualização que envolve a reconstrução de todos os estados intermediários entre a inserção original de linha na página e com a atualização que você está tentando reconstruir

Ao excluir BLOBs, o BLOB excluído não é inserido em um transaction log, então, ler o registro de log para o DELETE BLOB pode não trazer de volta o BLOB. Apenas se houver um registro de log INSERIR para o BLOB excluído, e você conseguir emparelhar estes dois, você será capaz de recuperar um BLOB excluído de um transaction log usando fn_dblog

Usando fn_dump_dblog

Para ler o backup de transaction log ou o backup nativamente compactado, mesmo sem a base de dados online, use a função fn_dump_dblog. Novamente, esta função não é documentada.

1. Execute a função fn_dump_dblog em um arquivo de backup de transaction log específico. Note que você deve especificar todos os 63 parâmetros.

SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.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);



Da mesma forma que o fn_dbLog, 129 colunas são retornadas, então selecione apenas colunas necessárias

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.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);

Novamente, você deve decifrar os valores hex para conseguir a informação que está procurando.



E você está novamente na estaca zero da mesma forma que quando usando fn_dblog – você precisa reconstruir todos os valores manualmente, você precisa reconstruir a cadeia completa para operações de UPDATE e valores BLOB

Se você não quer extrair a transação de um backup de transaction log, mas restaurar a base de dados para um ponto no tempo antes de uma operação específica ocorrer, você pode:

2. Determiner o LSN para esta transação
3. Converter o LSN para um formato usado na clausula WITH STOPBEFOREMARK = ‘<mark_name>, exemplo 00000070:00000011:0001 deve ser transformado em 112000000001700001
4. Restaurar a cadeia complete do backup de log até alcançar o momento onde a transação ocorreu. Use a clausula WITH STOPBEFOREMARK = ‘<mark_name>’ para especificar a referencia da transaçao LSN

   RESTORE LOG AdventureWorks2012
   FROM
       DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
   WITH
       STOPBEFOREMARK = 'lsn:112000000001700001',
       NORECOVERY;

Usando DBCC PAGE

Outro usual, mas também não documentado, é o comando DBCC PAGE. Use-o para ler o conteúdo de um arquivo ativo da base de dados – MDF ou LDF. A sintaxe é:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Para exibir a primeira página do arquivo ativo de transaction log da base de dados AdventureWorks2012, use:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Você terá

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

Por padrão, o retorno não é exibido. Se você quer o retorno do comando no SQL Server Management Studio, ative o trace flag 3604 primeiro

DBCC TRACEON (3604, -1)

E execute novamente

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Você terá uma quantidade grande de erros e um cabeçalho ruim e você pode ignorar isso. Ao final você terá a gloriosa saída hexadecimal do arquivo ativo LDF:

Que não é uma apresentação amigável de seus dados do banco de dados e, basicamente, não é diferente do que visualizá-lo em um editor hexadecimal (pouco mais desconfortável) mas pelo menos você tem acesso aos dados on-line

Usando ApexSQL Log

O ApexSQL Log é um leitor de transaction log do SQL Server que lê o transaction log on-line, transaction log desanexados e backups do transaction log – tanto backup nativo e backup nativamente comprimido. Quando necessário, ele também vai ler backups de banco de dados para obter dados suficientes para uma reconstrução bem-sucedida. Ele pode reproduzir mudanças de dados e objeto que afetaram um banco de dados, incluindo aqueles que tinham ocorrido antes de ser instalado. Ao contrário das funções sem documentos e sem suporte descritas acima, você poderá obter informações perfeitamente compreensível sobre o que aconteceu, em qual objeto, e o que o velho e o novo valor

1. Inicie o ApexSQL Log

2. Conecte na base de dados que você quer ler o transaction log

   

3. No passo Select SQL logs to analyze, selecione os logs que você quer ler. Tenha certeza que eles formam uma full chain

   

4. Para adicionar os backups de transaction log e arquivos LDF desanexados, use o botão Add

5. Use as opções em Filter setup para limitar as transações lidas usando o intervalo de tempo, tipo de operação, tabela e outros filtros disponíveis

   

6. Clique em Open

   Resultados totalmente compreensíveis serão exibidos na grade do ApexSQL Log

   Você será capaz de ver o horário que a operação começou e terminou, o tipo de operação, o nome do esquema e do objeto afetado, o nome do usuário que executou a operação, o computador e o aplicativo usado para executar a operação. Para UPDATES, você vai ver o valor anterior e o novo valor dos campos atualizados

   

Para evitar valores hexadecimais, funções sem documentação, conteúdo de coluna obscuro, consultas longas, etapas complexas, reconstrução incompleto de UPDATE e BLOB ao ler transaction logs do SQL Server, use ApexSQL Log. Ele irá ler os transactions logs para você e exibir os resultados de forma simples. Além disso, desfazer e refazer scripts está apenas um clique de distância

Tradução: Ricardo Leka Roveri