none
recuperando mdf com banco suspect RRS feed

  • Pergunta

  • Pessoal, recebir um desafio de um cliente, onde estou tentando recuperar um BD que está com status de SUSPECT com os seguintes comandos:

    USE Master
    GO
     
    1 -- Determine the original database status
    SELECT [Name], DBID, Status FROM master.dbo.sysdatabases
    GO
     
    2 -- Enable system changes
    sp_configure 'allow updates',1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
     
    3 -- Update the database status
    UPDATE master.dbo.sysdatabases SET Status = 24 WHERE [Name] = 'YourDatabaseName'
    GO
     
    4 -- Disable system changes
    sp_configure 'allow updates',0
    GO
    RECONFIGURE WITH OVERRIDE
    GO
     
    5 -- Determine the final database status
    SELECT [Name], DBID, Status FROM master.dbo.sysdatabases
    GO
     
    6 -- Em seguida a execução do script, execute os comandos abaixo:
     
    DBCC CHECKDB - Validate the overall database integrity
    DBCC CHECKCATALOG - Validate the system catalog integrity
    DBCC CHECKTABLE - Validate the integrity for a single table

    Porém quando chega no passo 3 o SGBD retorno o erro "Mensagem 259, Nível 16, Estado 1, Linha 1 Ad hoc updates to system catalogs are not allowed". O ocorrido foi por conta de uma queda de energia, no qual corrompeu o arquivo .mdf. Preciso colocar o BD operacional novamente, mas antes precisaria colocar em modo EMERGENCY e tentar recuperar com o último Backup FULL. Ressalto que a versão do SQL Server é o 2008.
    sábado, 27 de outubro de 2012 19:23

Respostas

  • Olhá sinceramente esse comando abaixo, é usado em últimos casos, pois pode ter perda de dados, por isso é recomendável utilizar quando tem backup ou algo do gênero.

    ALTER  DATABASE  S8_REAL SET EMERGENCY;
    GO
    ALTER  DATABASE  S8_REAL SET SINGLE_USER;
    
    e execute
    
    DBCC  CHECKDB  ( S8_REAL,  REPAIR_ALLOW_DATA_LOSS )  WITH NO_INFOMSGS ,  ALL_ERRORMSGS


    []'s


    segunda-feira, 29 de outubro de 2012 16:55
  • Uma alternativa é você fazer bcp exp dessas tabelas para futuramente efetuar as importações no seu novo banco.

    []'s

    terça-feira, 30 de outubro de 2012 12:58

Todas as Respostas

  • Bom dia,

    Essa opção que está executando no 3º passo no SQL Server 2008, foi trocado por.

    ALTER DATABASE Banco SET EMERGENCY.


    []'s

    segunda-feira, 29 de outubro de 2012 11:09
  • Justamente Fernando. Usei o ALTER DATABASE banco SET EMERGENCY no SQL Server 2008 R2 Enterprise, no qual foi gerado o mdf e ldf funcionando normalmente, porém agora o sistema retorna a mensagem quando executo o comando DBCC CHECKDB (S8_Real) 'Msg 946, Level 14, State 1, Line 1 Cannot open database 'S8_Real' version 655. Upgrade the database to the latest version', inclusive postei o problema no tópico posterior a esse. Já testei de diversas formas no SQL Server 2000 Developer, 2005 Developer, 2012 Enterprise, 2008 R2 Enterprise  e ainda não conseguir resolver o problema. O problema ocorreu por uma queda de energia, onde o BD ficou em estado de suspect e depois nisso não funcionou mais.
    segunda-feira, 29 de outubro de 2012 12:05
  • Não tem como dar RESTORE em arquivos físicos gerados no SQL Server 2008 para versões anteriores, ou você usa o 2008 mesmo ou uma versão superior, no caso o SQL Server 2012.

    []'s

    segunda-feira, 29 de outubro de 2012 12:20
  • Utilizei também o 2012, além do 2008 como dito acima e só retorna essa mesma mensagem de problema na versão 655.
    segunda-feira, 29 de outubro de 2012 12:50
  • Certo, 

    Hoje como está o seu cenário? Você tem os Backups e mãos, FULL, DIFF e LOG? Como está o seu modelo de recuperação? Full, Bulk ou simple ?Primeira coisa a se fazer é você tratar com MUITO cuidado o seu arquivo de Transaction log.


    []'s

    segunda-feira, 29 de outubro de 2012 13:11
  • Verifiquei através dos comandos a versão do DB:

    DBCC TRACEON (3604) GO

    DBCC PAGE ('banco',1,9,3) GO

    DBCC TRACEOFF (3604) GO

    Identifiquei essas informações:

    dbi_dbid = 5                         dbi_status = 65544                   dbi_nextid = 236332402
    dbi_dbname = S8_Real                 dbi_maxDbTimestamp = 2249000         dbi_version = 655
    dbi_createVersion = 603              dbi_ESVersion = 0                    
    dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 2009-04-25 16:22:07.903
    dbi_filegeneration = 12              
    dbi_checkptLSN

    SQL Server Edition Database Compatibility Level Internal Database Version Number
    SQL Server 7 70 515
    SQL Server 2000 80 539
    SQL Server 2005 90 611/612
    SQL Server 2008 100 655
    SQL Server 2008 R2 105 660
    SQL Server 2012 110 706

    Na tabela acima não conseguir identificar qual será essa dbi_createVersion = 603. De qualquer forma irei testar no SQL Server 2008, pois estava testando no SQL Server 2008 R2.

    segunda-feira, 29 de outubro de 2012 13:22
  • A versão do seu banco de dados é a 655, se você estiver tentando recuperar em versões anteriores a essa o problema persistirá, caso contrário não tem que dar erro, persisto na pergunta anterior, como está o cenário?

    E outra não sei porque essa troca de versões de SQL Server, recomendo que você recupere sua base de dados na mesma versão que estava funcionando e depois se quiser fazer o upgrade, fazer um cronograma desse Upgrade, utilizando ferramentas certas tais como Upgrade Adivisor e etc...


    []'s

    segunda-feira, 29 de outubro de 2012 13:55
  • Fernando, não existe backup FULL, DIFF nem LOG. Infelizmente o responsável que estava gerenciando esse ambiente não tinha conhecimento desses cuidados. A única coisa que sobrou foram o mdf e ldf. A base estava em modo simple, pois tirei como avaliação outros Bds que tinha funcionando. Fui contactado no sábado pra resolver o problema. Infelizmente ainda desinstalaram o SSMS do SQL Server que estava. Resolveram me passar depois que efetuaram diversas alternativas para resolver o problema. Como não conseguiram. E isso tudo sem ter o mínimo de conhecimento devido. Então estou responsável de subir o BD o quanto antes usando apenas esses mdf e ldf.
    segunda-feira, 29 de outubro de 2012 14:26
  • De fato o problema estava na versão mesmo. Instalei o SQL Server 2008 e o comando DBCC CHECKDB funcionamento perfeitamente, mas o desafio agora é corrigi o problema do banco recuperando o mdf e ldf.

    Coloquei o bd em estado de emergência (ALTER DATABASE 'S8_Real' SET EMERGENCY);
    depois em modo de user exclusivo (ALTER DATABASE 'S8_Real' SET SINGLE_USER);

    Até ai tudo bem.

    Executei o comando DBCC CHECKDB (S8_Real). Retornou diversos erros.
    Decidi desanexar e anexar usando comando:

    EXEC sp_detach_db 'S8_Real'

    CREATE DATABASE S8_Real
        ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\S8_Real.mdf'),
        (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\S8_Real_Log.ldf')
        FOR ATTACH;

    Quando vou anexar dar o seguinte erro:

    Mensagem 1813, Nível 16, Estado 2, Linha 2
    Não foi possível abrir o novo banco de dados 'S8_Real'. CREATE DATABASE foi anulado.
    Mensagem 3456, Nível 21, Estado 1, Linha 2
    Não foi possível refazer o registro do log (53000:1397:4), para a ID da transação (0:17400629), na página (1:550561), banco de dados 'S8_Real' (ID do banco de dados 7). Página: LSN = (52999:365:122), tipo = 2. Log: OpCode = 2, contexto 3, PrevPageLSN: (52999:365:257). Repare o banco de dados ou restaure-o usando um backup.
    Mensagem 3313, Nível 21, Estado 2, Linha 2
    Ocorreu um erro na ID do registro de log (53000:1397:4) ao refazer uma operação registrada em log no banco de dados 'S8_Real'. Em geral, a falha específica é registrada anteriormente como um erro no serviço Log de Eventos do Windows. Repare o banco de dados ou restaure-o usando um backup completo.
    segunda-feira, 29 de outubro de 2012 15:18
  • Poderia tentar a alternativa do CREATE DATABASE com  a opção FOR ATTACH_REBUILD_LOG.



    []'s


    segunda-feira, 29 de outubro de 2012 15:32
  • Usei o FOR ATTACH_REBUILD_LOG deu o mesmo retorno de erro.
    segunda-feira, 29 de outubro de 2012 16:46
  • Olhá sinceramente esse comando abaixo, é usado em últimos casos, pois pode ter perda de dados, por isso é recomendável utilizar quando tem backup ou algo do gênero.

    ALTER  DATABASE  S8_REAL SET EMERGENCY;
    GO
    ALTER  DATABASE  S8_REAL SET SINGLE_USER;
    
    e execute
    
    DBCC  CHECKDB  ( S8_REAL,  REPAIR_ALLOW_DATA_LOSS )  WITH NO_INFOMSGS ,  ALL_ERRORMSGS


    []'s


    segunda-feira, 29 de outubro de 2012 16:55
  • Executei os comandos acima colocando no estado de emergência, porém quando executo DBCC CHECKDB dar o seguinte erro:

    Mensagem 5123, Nível 16, Estado 1, Linha 1 CREATE FILE encontrou o erro do sistema operacional 3(O sistema não pode encontrar o caminho especificado.) ao tentar abrir ou criar o arquivo físico 'C:\Shop8\S8_Real_log.LDF'.
    Mensagem 5024, Nível 16, Estado 2, Linha 1 Nenhuma entrada encontrada para o arquivo de log primário em sysfiles1. Não foi possível recriar o log.
    Mensagem 5028, Nível 16, Estado 2, Linha 1 O sistema não pôde ativar uma parte do banco de dados suficiente para recriar o log.
    Mensagem 7909, Nível 20, Estado 1, Linha 1 Falha no reparo do modo de emergência. É preciso fazer a restauração com um backup.

    O interessante é que o BD está em modo de emergência e consigo fazer consulta nas tabelas normalmente. Algo que não conseguia antes. Como no estado de emergência é impossível realizar backups Full e de Log. Qual seria a alternativa mais cabível para resolver o problema?

    segunda-feira, 29 de outubro de 2012 17:38
  • O arquivo .LDF está no diretório informado na mensagem? 


    []'s

    segunda-feira, 29 de outubro de 2012 17:53
  • Opa...não está neste diretório. O ldf está no C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\S8_Real_Log.ldf'
    segunda-feira, 29 de outubro de 2012 18:11
  • O comando DBCC CHECKDB nem o DBCC REBUILD_LOG não funcionam no SQL Server 2008 quando especificamos o caminho do arquivo de log.
    segunda-feira, 29 de outubro de 2012 18:36
  • Essa opção foi descontinuada após a versão SQL Server 2005 se não me engano, mas como está a situação atual? Você rodou o DBCC  CHECKDB  ( S8_REAL,  REPAIR_ALLOW_DATA_LOSS )  WITH NO_INFOMSGS ,  ALL_ERRORMSGS em modo emergicial e single_user? Apareceu alguma mensagem?


    []'s

    segunda-feira, 29 de outubro de 2012 18:47
  • Executando DBCC CHECKDB com os parâmetros dar a mesma mensagem de erro acima. Rodei o DBCC CHECKDB (S8_Real) sem os parâmetros e me retorna as páginas que contêm erros, porém informa que a consistência foram preservadas. Veja mensagem abaixo:

    Resultados do DBCC para 'S8_Real'.
    Mensagem do Service Broker 9675, Estado 1: Tipos de Mensagem analisados: 14.
    Mensagem do Service Broker 9676, Estado 1: Contratos de Serviço analisados: 6.
    Mensagem do Service Broker 9667, Estado 1: Serviços analisados: 3.
    Mensagem do Service Broker 9668, Estado 1: Filas de Serviço analisadas: 3.
    Mensagem do Service Broker 9669, Estado 1: Pontos de Extremidade de Conversação analisados: 0.
    Mensagem do Service Broker 9674, Estado 1: Grupos de Conversação analisados: 0.
    Mensagem do Service Broker 9670, Estado 1: Associações de Serviço Remoto analisadas: 0.
    Mensagem do Service Broker 9605, Estado 1: Prioridades de Conversação analisadas: 0.
    Mensagem 8905, Nível 16, Estado 1, Linha 1
    -----CONTINUAÇÃO

    Existem 166873 linhas em 4873 páginas para o objeto "Contas_Rec_Detalhes".
    CHECKDB encontrou 1 erros de alocação e 2 erros de consistência na tabela 'Contas_Rec_Detalhes' (ID de objeto 2115291091).
    Resultados do DBCC para 'Cnab_Cedente'.
    Existem 17 linhas em 1 páginas para o objeto "Cnab_Cedente".
    Resultados do DBCC para 'Saidas_Series'.
    Existem 0 linhas em 0 páginas para o objeto "Saidas_Series".
    Resultados do DBCC para 'Operacoes_Entrada'.
    Existem 22 linhas em 1 páginas para o objeto "Operacoes_Entrada".
    CHECKDB encontrou 28 erros de alocação e 160 erros de consistência no banco de dados 'S8_Real'.
    repair_allow_data_loss é o nível de correção mínimo dos erros encontrados por DBCC CHECKDB (S8_Real).
    A execução do DBCC foi concluída. Se o DBCC imprimiu mensagens de erro, entre em contato com o administrador do sistema.

    Retornou o resultado de todas a tabelas, porém não é foi viável postar aqui. Depois que é executado o DBCC CHECKDB (S8_Real) sem os parâmetros, consigo visualizar normalmente as tabelas no pesquisador de objetos do SQL Server, inclusive fazer consultas. Ressalto que o BD permanece no estado de emergência.


    segunda-feira, 29 de outubro de 2012 19:08
  • Qual mensagem de erro aparece quando você utiliza o comando

    DBCC  CHECKDB  ( S8_REAL,  REPAIR_ALLOW_DATA_LOSS )  WITH NO_INFOMSGS ,  ALL_ERRORMSGS

    Você disse que o comando DBCC REBUILD_LOG não funciona, está certo, porque  na versão SQL Server 2005 esse comando foi descontinuado.


    []'s

    segunda-feira, 29 de outubro de 2012 19:22
  • Seria essa abaixo:

    Mensagem 5123, Nível 16, Estado 1, Linha 1
    CREATE FILE encontrou o erro do sistema operacional 3(O sistema não pode encontrar o caminho especificado.) ao tentar abrir ou criar o arquivo físico 'C:\Shop8\S8_Real_log.LDF'.
    Mensagem 5024, Nível 16, Estado 2, Linha 1
    Nenhuma entrada encontrada para o arquivo de log primário em sysfiles1. Não foi possível recriar o log.
    Mensagem 5028, Nível 16, Estado 2, Linha 1
    O sistema não pôde ativar uma parte do banco de dados suficiente para recriar o log.
    Mensagem 7909, Nível 20, Estado 1, Linha 1
    Falha no reparo do modo de emergência. É preciso fazer a restauração com um backup.

    segunda-feira, 29 de outubro de 2012 19:26
  • Faça o seguinte, utilize o comando abaixo para obter resultado onde está registrado seu arquivo de LOG,

    select b.name,a.type_desc,a.name,physical_name from sys.master_files a, sys.databases b where b.name = 'S8_Real_Log.LDF' and a.database_id = b.database_id


    []'s


    segunda-feira, 29 de outubro de 2012 19:33
  • Não retorna resultado nenhum o script acima.

    O correto não seria isso:

    select b.name,a.type_desc,a.name,physical_name
    from sys.master_files a, sys.databases b
    where b.name = 'S8_Real' and a.database_id = b.database_id

    O resultado retornado é:

    S8_Real    ROWS    S8_Real    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\S8_Real.mdf
    S8_Real    LOG    S8_Real_log    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\S8_Real_log.ldf

    segunda-feira, 29 de outubro de 2012 19:51
  • É verdade,  erro meu, me desculpe! seria do jeito que te passei se o filtro fosse a.name = 'S8_Real_log.ldf', mas eu coloquei b.name que é da sys.databases.

    E você certificou-se se os arquivos estão nos diretórios indicados pelo resultado?


    []'s


    segunda-feira, 29 de outubro de 2012 19:53
  • Pode esquecer esse diretório, pois ele não existe. Ele pertence ao caminho antigo do Servidor que estava os arquivos mdf e ldf. Estão sim nos diretórios relacionados.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\S8_Real.mdf
    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\S8_Real_log.ldf

    Só não consegui entender ai como alterar esse caminho 'C:\Shop8\S8_Real_log.LDF' quando executo DBCC  CHECKDB  ( S8_REAL,  REPAIR_ALLOW_DATA_LOSS )  WITH NO_INFOMSGS ,  ALL_ERRORMSGS

    É possível eu recuperar os dados com o BD estando em estado de emergência??? Pois neste estado consigo realizar consultas nas tabelas.
    segunda-feira, 29 de outubro de 2012 21:29
  • Faça o seguinte,

    1- Abra seu Sql Server
    2- No SQL Server Enterprise Manager, sua instância do SQL Server com o botão direito e clique em Propriedades.
    3- Na caixa de diálogo Propriedades de SQL Server (Configurar) - < nome da instância > , clique na guia Configurações do banco de dados .
    4- Na seção novo local do banco de dados padrão , digite um caminho de pasta válido na caixa diretório de dados padrão e na caixa diretório de                 log padrão ..
    5- Clique em OK.
    6- Parar a instância de SQL Server e então reinicie a instância de SQL Server.

    --

    Insira o diretório onde está seu o arquivo.



    []'s


    terça-feira, 30 de outubro de 2012 10:08
  • Já tinha realizado esse processo anteriormente e retornou o mesmo erro. Refiz novamente mais mesmo assim continua o erro. Estou começando a achar que de fato o ldf teve algum dano irreversível. Um detalhe que tenho observado é que toda vez que reinicio a instância o BD sai do estado de emergência e volta ao estado de suspect. Ai tenho que executar o ALTER DATABASE s8_real SET EMERGENCY, SINGLE_USER...Depois DBCC CHECKDB( S8_REAL,  REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS. Após a execução desses processos que consigo visualizar novamente as tabelas e seus respectivos dados, inclusive fazendo consultas de SELECTs.

    Tentei executar o procedimento de exportar/importar do SQL Server, mas infelizmente da um erro de conexão. Estava usando o data source Native Client 10.10....Criei um DB_Teste com uma estrutura totalmente limpa, somente com as tabelas na tentativa de salvar os dados.


    terça-feira, 30 de outubro de 2012 12:24
  • Uma alternativa é você fazer bcp exp dessas tabelas para futuramente efetuar as importações no seu novo banco.

    []'s

    terça-feira, 30 de outubro de 2012 12:58
  • Resolvi usando BCP...Criei um DB limpo com as estruturas das tabelas como comentado acima e usando o script escrito num .bat exportei os dados do S8_Real para a base nova criada. Depois escrevi um script no analyser query pra conciliar as rows dos registros em ambas as tabelas dos BDs.

    Problema parcialmente resolvido. Só resta gerar um script com os CREATEs das VIEWs e FUNCTIONS para executar na base nova criada.

    Agradecido atenção Fernando.

     
    quarta-feira, 31 de outubro de 2012 01:04