quinta-feira, 15 de janeiro de 2009

Recuperando Banco de Dados no SQL Server 2005 e 2008

Desculpem o silêncio neste final de 2008.... após as férias de Dezembro, fiquei atolado de trabalho nestas duas semanas iniciais de Janeiro! Vamos iniciar 2009 com um artigo sobre recuperação de banco de dados...

Os scripts deste artigo funcionam tanto no SQL Server 2005 quanto no SQL Server 2008!

Primeiro vamos criar um banco de dados para teste, o script abaixo cria o banco TesteDB contendo uma tabela de Clientes:

USE master
go
CREATE DATABASE TesteDB
go

USE TesteDB
go
CREATE TABLE Clientes (
ClientesID int not null,
Nome varchar(40) not null,
Telefone varchar(20) null)
go

INSERT Clientes VALUES (1,'Jose','(21) 2521-2252')
INSERT Clientes VALUES (2,'Maria','(21) 2521-1151')
go


Agora para corromper o banco TesteDB, basta simular uma queda de energia:

1) Na mesma conexão utilizada no script anterior, execute o código abaixo e mantenha a conexão aberta:
-- Abre a transacao e atualiza tabela
BEGIN TRAN
UPDATE Clientes SET Telefone = '(21) 2521-1151'
WHERE ClientesID = 2
go
CHECKPOINT
go

2) Abra uma nova conexão e execute o comando “SHUTDOWN WITH NOWAIT”.

O SQL Server para o serviço com o arquivo de dados atualizado pela transação que ficou em aberto, mantendo no arquivo de log o registro desta transação incompleta. Em uma situação normal, O SQL Server resolveria o problema ao iniciar, executando um ROLLBACK da transação que ficou em aberto, utilizando as informações do arquivo de log.

Para corromper o arquivo de log basta abri-lo no NOTEPAD, apagar alguns caracteres no seu início e salvar. Após corromper o arquivo de log inicie o SQL Server e tente entrar no banco TesteDB, teremos a mensagem de erro abaixo:

USE TesteDB

Resultado:
Msg 945, Level 14, State 2, Line 1
Database 'TesteDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Se você pesquisar no log do serviço (Management Studio – Object Explorer – Management – SQl Server Logs) veremos as mensagens abaixo:

- File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
- The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The PageAudit property is incorrect.

Para verificar o status do banco TesteDB:
SELECT databasepropertyex ('TesteDB', 'STATUS');
Resultado: SUSPECT


Vamos tentar resolver o problema com Detach e depois Attach:

EXEC sp_detach_db 'TesteDB'

Resultado:
Msg 947, Level 16, State 1, Line 1 Error while closing database 'TesteDB'. Check for previous additional errors and retry the operation.

O Detach ocorreu, apesar do erro alertando o estado inconsistente do banco de dados. Verifique com o comando abaixo:
SELECT * FROM sys.databases WHERE NAME = 'TesteDB'

Se você tentar realizar um Attach utilizando sp_attach_db a operação irá falhar, pois o arquivo de log foi corrompido.

EXEC sp_attach_db @dbname = 'TesteDB',
@filename1 = 'F:\sql2005\TesteDB.mdf',
@filename2 = 'F:\sql2005\TesteDB_log.LDF'

Resultado:
Msg 5172, Level 16, State 15, Line 1 The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The FILE SIZE property is incorrect.

Outra opção, que também não irá funcionar, seria tentar criar um novo arquivo de log utilizando a opção ATTACH_REBUILD_LOG do comando CREATE DATABASE.

CREATE DATABASE TesteDB ON
(NAME = TesteDB, FILENAME = 'F:\sql2005\TesteDB.mdf')
FOR ATTACH_REBUILD_LOG

Resultado:
File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'TesteDB'. CREATE DATABASE is aborted.

Para resolver o problema, basta seguir os passos abaixo:

1) Mover (copiar para outra pasta e apagar) os arquivos de dados e log do banco TesteDB para outro local.

2) Criar um novo banco com o mesmo nome (TesteDB) e parar o serviço do SQL Server.
CREATE DATABASE TesteDB
SHUTDOWN

3) Apagar o arquivo de log e copiar o arquivo de dados que você salvou no item 1, por cima do arquivo novo criado no item 2 acima.

4) Executar o script abaixo.
ALTER DATABASE TesteDB SET EMERGENCY
ALTER DATABASE TesteDB SET SINGLE_USER
go
DBCC CHECKDB (TesteDB, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS
go
ALTER DATABASE TesteDB SET read_write
ALTER DATABASE TesteDB SET multi_user
go

Repare que foi gerado um novo arquivo de log e o banco de dados foi recuperado, verifique rodando o script abaixo:
USE TesteDB
go
select * from TesteDB.dbo.Clientes

Até o próximo post e feliz 2009,
Landry.

22 comentários:

Anônimo disse...

Show de bola! Fiz este passo a passo e funcionou! \o/

esta é uma informação bastante util
valeu Landry

att Gilson Dias.

FAM disse...

Bom dia

Landry,

ja tem previsão do Inicio dos curso do MSSQL 2008

Landry disse...

Olá Gilson,

Fico feliz por ajudar.

[]'s,
Landry

Landry disse...

Ola Flu...lemar,

A Allen deve oferecer os cursos de SQL 2008 só em março, devido a grande demanda de cursos de SQL 2005.

Landry.

Davi Andrade disse...

Landry, eu estou com algumas tabelas corrompidas, esse processo não recupera estas tabelas também não? após a execução do último script estou tendo a seguinte mensagem:
Possible schema corruption. Run DBCC CHECKCATALOG.

Unknown disse...

Cara, meus parabéns pelo post, passei dias procurando uma solução pro meu problema e nada... hoje resolvi procurar em portugues mesmo, de cara... eu tinha tentando exatamente tudo até o último passo....

muitíssimo obrigado!! :D agora posso durmir tranquilo!!!

will disse...

Parabéns!! Quase perco meu emprego.. kkkk

Manoel Junior Costa disse...

Nesse caso, é somente quando o LDF está corrompido?
E quando é o MDF que está corrompido?

Anônimo disse...

Parabéns,
Tive um problema com banco suspect, e o antigo jeito que eu fazia com o comando para recuperar o log, não funcionou, e com este funcionou certinho, valew cara, parabéns e obrigado por compartilhar conhecimento...

Att. João Gonzales

Anônimo disse...

Cara, tu salvou minha vida. Muito obrigado!

Anônimo disse...

Parabéns cara, muito boa a informação.
Funcionou pra mim

SergioJr. disse...

Parabens! tem me ajuda muito

Anônimo disse...

Gostaria de saber pois um cliente teve um pic de energia e parece der dados pal no banco SQL por isso realmente foi esse PIC de energia que vfez isso?

Landry disse...

Olá Antonio,

Provavelmente foi o pic de luz que danificou o banco, por isso recomenda-se o uso de nobreaks para servidores de banco de dados.

Att,
Landry

Anônimo disse...

mano .. certin ... meu banco era de 70 GB e depois de um pic ele zuou ...

usei o passo a passo e salvei meu emprego

VAleu ..
Ass Tim Oliveira ( Orkut )

Landry disse...

Olá Tim Oliveira,

Fico feliz em ajudar...
Att,
Landry

Aníbal Monteiro disse...

Excelente ... funcionou perfeitamente.
Obrigado

Anônimo disse...

Prezado Landry,
Seguindo teu roteiro, simples e objetivo, recuperei um banco com sucesso.
Grato por compartilhares teu conhecimento.
abraço
PedroAurelioZabaleta

Anônimo disse...

Valeu pela dica, funcionou perfeito !

Unknown disse...

Cara isso salvou minha vida!

Unknown disse...

Normally this type of SQL error 945 originates because of memory fault like improper allocation of SQL object variables. To resolve this type of error the users must use SQL database repair tool. Read more and try from here:- http://www.sqlrecoverysoftware.net/blog/sql-error-926-and-945.html

Freitas disse...

Post de 2009, quebrou um galhasso rs
Muito obrigado!