Recuperando Banco de Dados SQL Server com Transaction Log Corrompido – Dsai Consultoria skip to Main Content

Recuperando Banco de Dados SQL Server com Transaction Log Corrompido

Já publiquei um post sobre arquivo de dados corrompido e como recuperar o banco de dados, link abaixo:

https://dsai.com.br/recuperando-banco-de-dados-no-sql-server/

Vamos completar este assunto escrevendo sobre Transaction Log corrompido e como recuperar o banco de dados.

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

This Post Has One Comment

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Back To Top