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/

21 comentários sobre “Atachar/Recuperar arquivos MDF sem o LDF

  1. Marco Medeiros

    Depois de dois dias sofrendo com isso e tentar inúmeros sites que não colocam o passo a passo tão explicado e completo, suas dicas deram resultados. Muito obrigado!!!

  2. Bom dia!
    Sou iniciante na área e tenho uma duvida, caso seja possível gostaria de sua ajuda.
    Como faço para encontrar os arquivos .LDF e .MDF em um banco de dados SQL server 2012?
    Sera de grande ajuda se puder me responder

    1. Eduardo,

      Você pode clicar com o botão direito do mouse sobre o banco no object explorer e ir em “propriedades” ou “properties”. Depois é só ir na opção “arquivos” ou “files”.

      Você também pode executar a seguinte query dentro do banco:
      select * from sys.database_files

      Caso o banco não esteja mais disponível ou os métodos anteriores não funcionem, você pode tentar encontrá-lo no caminho padrão da sua instância. Para isso, clique com o botão direito na sua instância no object explorer, “Properties > Database Settings”.

      []’s

  3. Pingback: Retrospectiva 2016 – Comunidade SQL Server

    1. Nane

      Olá Alessandro. Fico contente por ter ajudado.
      Sempre vale lembrar que esta estratégia deve ser utilizado em último caso e muito provavelmente perderá dados.

  4. tiago frades

    sp_attach_db não funciona porque ele procura por todos os arquivos que originalmente compunham db
    a) backup do arquivo antes de ir mais longe
    b) renomeie o arquivo para filename_old
    c) crie novo banco de dados que tenha um arquivo com o mesmo nome físico e tamanho que o nosso arquivo
    (nome lógico do arquivo e nome do banco de dados pode ser diferente do original)
    d) pare o servidor SQL
    e) substitua o arquivo original pelo arquivo mdf do banco de dados recém-criado
    f) iniciar o servidor SQL
    g) nos logs do SQL Server são mensagens ‘Arquivo de log não corresponde ao arquivo principal.’,
    ‘Não é possível associar arquivos com bancos de dados diferentes.’e db é suspeito
    h) Defina o banco de dados no modo de emergência:

    sp_configure ‘allow updates’, 1
    reconfigure com override
    go
    update sysdatabases
    set status = 32768
    onde name = ‘db_name’

    sp_configure ‘permita atualizações’, 0
    reconfigure com override
    go

    Coloca o banco criado como somente leitura e OFF-LINE.
    Depois que coloca-lô como off ir até a pasta onde foi criado o novo e substituir o arquivo .MFD novo pelo banco a ser recuperado.
    alter database db_name set read_only
    alter database db_name set offline

    Colocar o mesmo como somente leitura
    alter database db_name set read_write

    Setar como EMERGENCIA
    alter database nGestao set emergency

    Alterar para usuário unico
    alter database db_name set single_user

    Apagar o arquivo .LDF que foi criado anteriormente e vamos criar um novo com o script a baixo baseado no arquivo .MFD atual (Que queremos recuperar)
    alter database nGestao rebuild log on
    (Name = nGestao_Data_log,filename=’C:\DatabaseEmergencia\db_name_Data_log.ldf’)

    Tentar alterar para ONLINE
    alter database db_name set online

    Alterar pra MULTI USUÁRIO
    alter database db_name set MULTI_USER

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s