Recuperando Banco de Dados com Transaction Log Corrompido

Já publiquei um post falando de arquivo de dados corrompido e como recuperar o banco (http://sqlserver-brasil.blogspot.com.br/2009/01/recuperando-banco-de-dados-no-sql.html), vou completar o assunto escrevendo sobre Transaction Log corrompido.

Primeiro veremos como simular um arquivo de Log corrompido, depois como recuperar o banco.

Corrompendo o Transaction Log

Para corromper o arquivo de Log basta seguir os passos abaixo:

1) Primeiro vamos criar o banco de Dados “LogCorruptDB” e a tabela de “Clientes” utilizando o script abaixo:

USE master

go

IF DB_ID('LogCorruptDB') is not null

   DROP DATABASE LogCorruptDB

go

CREATE DATABASE LogCorruptDB

go

USE LogCorruptDB

go

-- Cria tabela Clientes

CREATE TABLE dbo.Clientes (

CLientesPK int not null primary key,

Nome varchar(60) not null,

Telefone varchar(20) null)

go

INSERT dbo.Clientes VALUES (1,'Jose','2121-1111')

INSERT dbo.Clientes VALUES (2,'Ana','2222-1111')

INSERT dbo.Clientes VALUES (3,'Maria','2323-1111')

go

2) Próximo passo, executar uma transação de atualização sem finaliza-la.  O comando CHECKPOINT serve para forçar a escrita dos dados no arquivo de dados, já que qualquer alteração é feita primeiramente na memória.

BEGIN TRAN

UPDATE dbo.Clientes SET Telefone = '2323-3333'

WHERE CLientesPK = 3

go

CHECKPOINT

go

3) Para corromper o arquivo de Log precisamos altera-lo com um editor Hexa (http://mh-nexus.de/en/hxd/), porém como os arquivos ficam abertos enquanto o SQL Server está ativo, precisamos para o serviço antes de editar o arquivo.  Um SHUTDOWN simples irá executar o ROLLBACK da transação antes de parar o SQL Server, para não ocorrer o ROLLBACK precisamos parar o serviço utilizando o comando abaixo:

SHUTDOWN WITH NOWAIT

4) Em seguida, vamos editar o arquivo de Log com um editor Hexa, alterando as duas primeiras linhas para corromper o arquivo.

5) Por último, vamos iniciar o SQL Server e verificar o banco corrompido no Management Studio em Databases:

Como o arquivo de Log está corrompido o SQL Server não conseguiu executar o Recovery Process, ficando o banco fora do ar e com status de “Recovery Pending.  A tentativa de acesso ao banco gera o erro abaixo:

USE LogCorruptDB

go

Erro:

Msg 945, Level 14, State 2, Line 46

Database 'LogCorruptDB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Recuperando o Banco com Transaction Log corrompido

Para visualizar o status atual do banco utilizamos a função DATABASEPROPERTYEX:

SELECT DATABASEPROPERTYEX ('LogCorruptDB', 'STATUS')

O banco está com status SUSPECT e fora do ar.

Se tentarmos fazer um Detach/Attach com Rebuild do Log, o SQL Server retorna a mensagem de erro, pois Detach não funciona com banco SUSPECT:

EXEC master.dbo.sp_detach_db @dbname = N'LogCorruptDB'

Erro:

Msg 3707, Level 16, State 2, Line 57

Cannot detach a suspect or recovery pending database.

It must be repaired or dropped.

Outra alternativa é utilizar o comando DBCC CHECKDB, mas antes precisamos alterar o status do banco, como abaixo:

ALTER DATABASE LogCorruptDB SET EMERGENCY

go

DBCC CHECKDB (LogCorruptDB, REPAIR_ALLOW_DATA_LOSS)

WITH NO_INFOMSGS, ALL_ERRORMSGS

Erro:

Msg 7919, Level 16, State 3, Line 70

Repair statement not processed. Database needs

to be in single user mode.

Repare na mensagem, que o comando DBCC CHECKDB com REPAIR_ALLOW_DATA_LOSS requer status de SINGLE USER no banco!

ALTER DATABASE LogCorruptDB SET SINGLE_USER

go

DBCC CHECKDB (LogCorruptDB, REPAIR_ALLOW_DATA_LOSS)

WITH NO_INFOMSGS, ALL_ERRORMSGS

go

A mensagem de retorno alerta que o Transaction Log foi recriado, mas podem ocorrer inconsistências no banco devido a transações inacabadas! 

Podemos acessar agora o banco de dados sem problema, mas a tabela de Clientes manteve a alteração da transação que ficou em aberto, veja:

SELECT * FROM LogCorruptDB.dbo.Clientes

O Rebuild do Transaction Log deve ser utilizado apenas quando não existe Backup para recuperar o banco no caso de Log corrompido, pois sempre existe a possibilidade de recuperar o banco num estado inconsistente!

Até o próximo post.

Saudações Tricolores,

Landry