Atachar/Recuperar arquivos MDF sem o LDF

Quem já precisou atachar ou recuperar um arquivo MDF sem o LDF? Pode ser em diversos casos, mesmo sendo um “Backup” de um cliente que ele enviou para você analisar algum erro no seu banco ou até mesmo em algum ambiente (Desenvolvimento. Obs.: não executar em produção, pois esse método perde a integridade dos dados) que por “engano” apagaram o LDF (para que você consiga apagar o arquivo LDF, a base deverá estar offline). Como em um ambiente de desenvolvimento, o desenvolvedor é o “dono” do ambiente, ele pode (e faz) tudo.

Um caso que peguei foi o seguinte:

  • De tempos em tempos, o desenvolvedor faz uma limpa no ambiente para liberar espaço;
  • Coloca a base offline e espera alguém se manifestar;
  • Caso ninguém se manifeste a base é excluída. Como a base estava offline os arquivos mdf e ldf continuam no disco até que alguém vai e os deletam diretamente;
  • Caso alguém se manifeste, a base é colocada online novamente.

Um belo dia uma pessoa reclamou que não estava conseguindo acessar a base dela. O desenvolvedor rapidamente deu o comando para a base ficar online novamente, porém estava dando erro… O desenvolvedor entrou em contato com a equipe de DBA para verificar o motivo pelo qual a base não ficava online e em uma análise mais profunda foi identificado que o arquivo LDF não existia mais. E agora, perdemos a base? Não! Vamos desatachar a base e “brincar” um pouco.

Aqui iremos realizar alguns processos simples para atachar o arquivo. Temos três opções, qualquer uma pode ser realizada.

  1. Attach arquivo MDF:
USE [master]
GO
-- Método 1:
EXEC sp_attach_single_file_db @dbname= TestDb,
@physname=N'E:\Data1\TestDb.mdf'
GO
-- Método 2:
CREATE DATABASE TestDb ON
(FILENAME = N'E:\Data1\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO
-- Método 3:
CREATE DATABASE TestDb ON
(FILENAME =N'E:\Data1\TestDb.mdf')


Todos os métodos anteriores funcionam quando o arquivo primário aceita escrita. Caso o banco de dados, antes de ser colocado como offline, estivesse com o status de read_only o SQL Server retornará o seguinte erro:

“Log file ‘F:\Log1\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.The log cannot be rebuilt when the primary file is read-only.”

Como podemos observar, o arquivo primário não aceita escrita e com isso o log não pode ser recriado. Quando ocorre esse cenário, os seguintes métodos podem ser tentados.

  1. Recuperando um arquivo MDF quando o mesmo está como read_only.

 

2.1.  Criar um banco de dados “falso”, contendo o mesmo nome tanto do banco quanto dos arquivos. Os arquivos de dados e LOG deverão ser criados no mesmo caminho do banco original.

CREATE DATABASE TestDb

ON

(NAME = TestDb_dat,

FILENAME = 'E:\Data1\TestDb.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

(NAME = TestDb_log,

FILENAME = 'F:\Log1\TestDb.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB ) ;

2.2.  Apontar o novo banco de dados para read_only e em seguida para offline:

alter database TestDb set read_only

alter database TestDb set offline

2.3.  Substituir o arquivo MDF “falso” pelo original.

2.4.  Apontar a base como online:

alter database TestDb set online

O SQL Server retornará o seguinte erro:

“Msg 5173, Level 16, State 1, Line 1

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file ‘F:\Log1\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

The log cannot be rebuilt when the primary file is read-only.

Msg 945, Level 14, State 2, Line 1

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

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.”

2.5.  Apontar a base como read_write:

alter database TestDb set read_write

O SQL Server retornará o seguinte erro:

“Msg 5173, Level 16, State 1, Line 1

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file ‘F:\Log3\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Msg 945, Level 14, State 2, Line 1

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

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.”

Como podemos observar pelas mensagens, o arquivo de LOG ainda não foi incluído na base e para que a mesma fique online é necessitando recriá-lo.

  1. Recriando o arquivo de LOG.

Para recriar o arquivo de log, deveremos saber o nome do banco, nome do arquivo e o caminho do arquivo.

3.1.  Mudar o status do banco para emergency:

alter database TestDb set emergency

3.2.  Mudar o status do banco para single_user:

alter database TestDb set single_user

3.3.  Excluir o arquivo de Log do banco “falso”;

3.4.  Recriar o LOG:

alter database TestDb rebuild log on

(Name= TestDb_log,filename='F:\Log1\TestDb.ldf')

3.5.  Mudar o status do bando para online:

alter database TestDb set online

O SQL Server retornará a seguinte mensagem:

“The Service Broker in database “TestDb” will be disabled because the Service Broker GUID in the database (50132F87-D4CE-4A8B-9C3A-EB50D6F7C707) does not match the one in sys.databases (D1B68542-A698-4EDE-9B1D-8DF13611D7E1).

Warning: The log for database ‘TestDb’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.”

3.6.  Mudar o status do banco para multi_user:

alter database TestDb set MULTI_USER

Aqui felizmente conseguimos restaurar a base com “sucesso”. Digo “sucesso”, pois obviamente os dados que estavam no arquivo de log foram perdidos.

Enfim, como podemos observar, esse método não é recomendado realizar em produção, pois dados podem ser perdidos. Caso isso ocorra em produção, realmente espero que haja outros tipos de recovery (replicação de storage, backup…).

Referências

 

  1. http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
  2. http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/