SQL Server

Todos os posts com a tag SQL Server

Como recuperar um banco de dados apagado acidentalmente sem ter o backup

Postado por Mauricio Moura em 4 de abril de 2013
Publicado em: Backup, Disaster Recover, SQL Server, T-SQL, Windows. Marcado: , , . 4 Comentários

Você acaba de apagar um banco de dados acidentalmente em uma instância do SQL Server. Para muitos DBAs o banco está perdido para sempre, a menos que você possua um backup.

Este é o conhecimento comum de muitos DBAs que trabalham com o Microsoft SQL Server, porém, não é completamente verdadeira.

No entanto este caso é como um paciente em um hospital que teve uma parada cardíaca. Quanto mais demorar pra tomar os procedimentos de recuperação dos arquivos, com mais sequelas estes podem ficar, ou até mesmo se tornar o procedimento irreversível. Em suma, não há garantias de que o procedimento funcionará e nem de que o banco será 100% recuperado, em muitos casos recuperar alguma parte do que foi perdido pode ser muito melhor do que não se recuperar nada.

Neste artigo iremos utilizar algumas ferramentas gratuitas como o FreeUndelete da OfficeRecovery.com

FreeUndelete – File Undelete Software – http://www.officerecovery.com/

FreeUndelete restores deleted files, including those removed from Windows Recycle Bin. In case of accidental removal of files on a NTFS (1.0 and 2.0) (default for Windows Vista, XP, 2000 and NT), FAT32, FAT16 or FAT12 file systems this is the utility to help.

Supported file systems:

NTFS 1.0, NTFS 2.0, FAT12, FAT16 and FAT32

The recovered files are saved into a new user-selected location to prevent overwriting the deleted files on the original media.

Você pode ir ao WebSite da OfficeRecovery.com e fazer download de uma cópia do FreeUndelete.

* Muito Importante *
Durante todo o processo tente reduzir ao máximo a escrita no disco que contém os arquivos do banco que devem ser restaurados, pois isto evita que os arquivos sejam sobrescritos enquanto não são restaurados.
Também leve em consideração que o conteúdo deste artigo server somente para aqueles casos em que não há outra forma de recuperar o banco. Lembre-se que é muito importante ter backups de seus bancos de dados.

 

Primeiro passo – Recuperar os arquivos excluídos

Muitas pessoas não sabem como funciona o sistema de arquivos no sistema operacional. Até o Windows Server 2008 R2 e Windows 7, o sistema de arquivos padrão do sistema operacional é o NTFS. O NTFS utiliza uma área do disco para guardar o seu “banco de dados” do sistema de arquivos chamado de MFT (Master File Table), que é uma versão aprimorada do velho FAT (File Allocation Table, também chamado de FAT16 e FAT32 de acordo com suas capacidades de mapeamentos para nomes de arquivos).  O MFT contém todos os metadados referentes aos arquivos e como o conteúdo deles estão distribuídos pelo volume de disco.

Por este motivo podemos considerar o MFT como um índice que indica ao sistema operacional onde fica cada segmento do início ao fim do arquivo lógico no volume de disco.

Agora sabemos que o MFT é o único arquivo (sim é um arquivo) que o sistema operacional utiliza para manter o controle de como e onde os arquivos e pastas estão distribuídos no volume de disco (Exatamente como faz o sistema FAT).

Quando excluímos um arquivo do disco, o arquivo não é realmente apagado fisicamente, ele somente é marcado  como uma área livre no MFT e ficará disponível enquanto outro arquivo não sobrescreva o seu conteúdo.

Então, se quisermos recuperar um arquivo excluído, ele pode ainda estar intacto no disco, porem pode ser sobrescrito (por completo ou por partes) por outro arquivo ou diretório, corrompendo assim a integridade do Datafile, porem ainda pode ser possível recuperar parte dos dados do mesmo caso isto venha a acontecer.

Existem vários utilitários que recuperam os arquivos do disco fazendo a operação chamada de UNDELETE. Neste artigo utilizaremos o FreeUndelete que já deve ter sido baixado anteriormente.

clip_image001

Usando o aplicativo FreeUndelete, recupere todos os datafiles do banco que foi excluído, pelo menos temos que restaurar todos os MDF e NDF, se conseguir restaurar o arquivo LDF será ótimo, caso o mesmo não esteja corrompido. Para o artigo vamos assumir que o log não foi restaurado. Quanto antes você recuperar os arquivos, menor as chances dos mesmos terem sidos corrompidos (sobrescritos no disco) por outros arquivos.

Preferencialmente recupere os arquivos para outro disco que não seja o mesmo onde eles forem removidos para evitar que a recuperação sobrescreva os arquivos ainda não recuperados.

Quando recuperar todos os datafiles do banco, passe pro próximo passo. Caso não consiga recuperar todos, não será possível restaurar o banco de dados com este processo.

Segundo passo – O processo de recovery

O processo de recovery é na verdade um procedimento que nos permitirá recuperar o máximo de dados contidos dentro dos datafiles, mesmo que eles estejam danificados (se tiverem algum de seus setores sobrescritos por outro arquivo, por exemplo).

Se você conseguir anexar os arquivos recuperados sem erros, então tudo o que você precisa fazer agora é rodar um DBCC CHECKDB e tentar reparar o banco (caso alguma parte esteja corrompida). Porém para a maioria dos casos, você receberá um erro informando que o arquivo não pode ser anexado. Então faremos alguns procedimentos para anexar o banco ao SQL server.

Abaixo iremos trabalhar com um Banco chamado CSMB. Ele foi apagado acidentalmente de uma instância de SQL Server 2008 R2 e os seus datafiles foram recuperados somente no dia seguinte. Como não podíamos ficar reiniciando o serviço do SQL Server na máquina onde os arquivos foram recuperados, copiei os arquivos para a minha máquina local, o que ajudou também executar todos os processos mais rapidamente.

Arquivo: CSMB.mdf

Caminho Original do Arquivo: V:\MSSQL\Data\CSMB.MDF

Arquivo: CSMB_log.ldf

Caminho Original do Arquivo: S:\MSSQL\Tlog\CSMB_Log.LDF

Database Log Mode: Simple

Abaixo segue o passo a passo da tentativa de recuperar este banco de dados:

   1: USE [master]

   2: GO

   3:

   4: CREATE DATABASE [CSMB] ON

   5: ( FILENAME = N'C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB.mdf' ),

   6: ( FILENAME = N'C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB_log.ldf' )

   7: FOR ATTACH

   8: GO

Ao executar o código acima para restaurar o banco, recebemos vários erros sobre a consistência do tlog, informando que precisamos reconstruir o arquivo de log.

Msg 824, Level 24, State 6, Line 1

SQL Server detected a logical consistency-based I/O error: restore pending. It occurred during a read of page (0:0) in database ID 0 at offset 0000000000000000 in file ‘C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB_log.ldf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Então, seguindo a lógica, tentaremos fazer o ATTACH com a opção de Rebuild LOG:

   1: USE [master]

   2: GO

   3:

   4: CREATE DATABASE [CSMB] ON

   5: ( FILENAME = N'C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB.mdf' )

   6: FOR ATTACH_REBUILD_LOG

   7: GO

Como não estamos restaurando o banco em seu servidor original, o comando acima irá retornar um erro informando que o caminho para o arquivo de log não existe (pois ele tem a referência do caminho original do TLOG, neste caso ‘S:\MSSQL\Tlog\CSMB_Log.LDF’.

File activation failure. The physical file name “S:\MSSQL\Tlog\CSMB_log.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘CSMB’. CREATE DATABASE is aborted.

Para contornar este problema, verifique este artigo e crie um disco virtual atribuindo a letra de unidade S:, crie a estrutura de diretórios necessária e copie o arquivo recuperado para ficar igual ao caminho original:

clip_image003

Após a estrutura estar corretamente criada e o arquivo no seu caminho original, tentaremos anexar o banco novamente:

   1: USE [master]

   2: GO

   3:

   4: CREATE DATABASE [CSMB] ON

   5: ( FILENAME = N'C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB.mdf' )

   6: FOR ATTACH_REBUILD_LOG

   7: GO

Em algum casos isto seria o suficiente para restaurar o banco, porem neste caso não é possível reconstruir o log, pois quando o banco foi excluído existiam muitas transações pendentes. Recebemos então um novo erro informando que o datafile precisa ser recuperado a partir do seu LOG original e mesmo o ATTACH_REBUILD_LOG não pode ajudar neste caso.

File activation failure. The physical file name “S:\MSSQL\Tlog\CSMB_log.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘CSMB’. CREATE DATABASE is aborted.

A partir deste ponto, para muitos DBAs acreditam que sem o backup não há mais como recuperar o banco. Mas nós utilizaremos uma outra forma para poder forçar o banco a ser anexado e assim conseguindo recuperar o seu conteúdo.

Para este método, iremos criar um banco de dados novo com o mesmo nome do banco que queremos recuperar:

   1: CREATE DATABASE [CSMB]

   2: ON PRIMARY

   3: ( NAME = N'CSMB', FILENAME = N'C:\MSSQL\DATA\CSMB.mdf' , SIZE = 3072KB ,

   4: FILEGROWTH = 1024KB )

   5: LOG ON

   6: ( NAME = N'CSMB_log', FILENAME = N'C:\MSSQL\DATA\CSMB_log.ldf' ,

   7: SIZE = 1024KB , FILEGROWTH = 10%)

   8: GO

Assim que criarmos o banco, devemos parar o serviço do MSSQLServer e copiar os datafiles recuperado (C:\Users\Mauricio_Moura\Desktop\Recover_CMDB\CSMB.mdf) por cima dos arquivos recem criados (C:\MSSQL\DATA\CSMB.mdf) e logo após reiniciar o serviço de MSSQLServer novamente:

clip_image004

Agora, conectamos na instancia e verificamos o banco CSMB. Ele estará marcado como suspeito (suspect):

clip_image005

Com o banco anexado, vamos colocá-lo em modo de emergência (emergency mode) e em single_user para podermos executar um checkdb e fazer rebuild do LOG:

   1: ALTER DATABASE CSMB SET EMERGENCY

   2: GO

   3: ALTER DATABASE CSMB SET SINGLE_USER

   4: GO

Agora que colocamos o banco em modo de emergência, podemos abrir o banco e executar o DBCC CHECKDB com o parâmetro REPAIR_ALLOW_DATA_LOSS, assim recuperando o máximo de dados possíveis do datafile danificado:

   1: DBCC CHECKDB(CSMB, REPAIR_ALLOW_DATA_LOSS)

   2: GO

Agora aguardamos a execução do processo que recuperará o máximo de dados possíveis neste banco. Note que podem haver dados e tabelas corrompidas que podem não ser mais recuperáveis, o banco será recuperado, mas estes dados poderão ser perdidos.

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 ‘C:\MSSQL\DATA\CSMB_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

The Service Broker in database “CSMB” will be disabled because the Service Broker GUID in the database (0692E020-8223-4152-BF2A-352F9FE64E42) does not match the one in sys.databases (0E1EA231-658C-4DBA-902B-AA2CB291310B).

Warning: The log for database ‘CSMB’ 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.

DBCC results for ‘CSMB’.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

Service Broker Msg 9668, State 1: Service Queues analyzed: 3.

Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.

Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.

Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.

DBCC results for ‘sys.sysrscols’.

There are 870 rows in 9 pages for object “sys.sysrscols”.

DBCC results for ‘sys.sysrowsets’.

There are 101 rows in 1 pages for object “sys.sysrowsets”.

DBCC results for ‘sys.sysallocunits’.

There are 116 rows in 2 pages for object “sys.sysallocunits”.

DBCC results for ‘sys.sysfiles1’.

There are 2 rows in 1 pages for object “sys.sysfiles1”.

DBCC results for ‘sys.syspriorities’.

There are 0 rows in 0 pages for object “sys.syspriorities”.

DBCC results for ‘sys.sysfgfrag’.

There are 2 rows in 1 pages for object “sys.sysfgfrag”.

DBCC results for ‘sys.sysphfg’.

There are 1 rows in 1 pages for object “sys.sysphfg”.

DBCC results for ‘sys.sysprufiles’.

There are 2 rows in 1 pages for object “sys.sysprufiles”.

DBCC results for ‘sys.sysftinds’.

There are 0 rows in 0 pages for object “sys.sysftinds”.

DBCC results for ‘sys.sysowners’.

There are 20 rows in 1 pages for object “sys.sysowners”.

DBCC results for ‘sys.sysprivs’.

There are 136 rows in 1 pages for object “sys.sysprivs”.

DBCC results for ‘sys.sysschobjs’.

There are 65 rows in 1 pages for object “sys.sysschobjs”.

DBCC results for ‘sys.syscolpars’.

There are 736 rows in 12 pages for object “sys.syscolpars”.

DBCC results for ‘sys.sysnsobjs’.

There are 1 rows in 1 pages for object “sys.sysnsobjs”.

DBCC results for ‘sys.syscerts’.

There are 0 rows in 0 pages for object “sys.syscerts”.

DBCC results for ‘sys.sysxprops’.

There are 4 rows in 2 pages for object “sys.sysxprops”.

DBCC results for ‘sys.sysscalartypes’.

There are 34 rows in 1 pages for object “sys.sysscalartypes”.

DBCC results for ‘sys.systypedsubobjs’.

There are 0 rows in 0 pages for object “sys.systypedsubobjs”.

DBCC results for ‘sys.sysidxstats’.

There are 218 rows in 6 pages for object “sys.sysidxstats”.

DBCC results for ‘sys.sysiscols’.

There are 360 rows in 3 pages for object “sys.sysiscols”.

DBCC results for ‘sys.sysbinobjs’.

There are 23 rows in 1 pages for object “sys.sysbinobjs”.

DBCC results for ‘sys.sysaudacts’.

There are 0 rows in 0 pages for object “sys.sysaudacts”.

DBCC results for ‘sys.sysobjvalues’.

There are 212 rows in 49 pages for object “sys.sysobjvalues”.

DBCC results for ‘sys.sysclsobjs’.

There are 16 rows in 1 pages for object “sys.sysclsobjs”.

DBCC results for ‘sys.sysrowsetrefs’.

There are 0 rows in 0 pages for object “sys.sysrowsetrefs”.

DBCC results for ‘sys.sysremsvcbinds’.

There are 0 rows in 0 pages for object “sys.sysremsvcbinds”.

DBCC results for ‘sys.sysxmitqueue’.

There are 0 rows in 0 pages for object “sys.sysxmitqueue”.

DBCC results for ‘sys.sysrts’.

There are 1 rows in 1 pages for object “sys.sysrts”.

DBCC results for ‘sys.sysconvgroup’.

There are 0 rows in 0 pages for object “sys.sysconvgroup”.

DBCC results for ‘sys.sysdesend’.

There are 0 rows in 0 pages for object “sys.sysdesend”.

DBCC results for ‘sys.sysdercv’.

There are 0 rows in 0 pages for object “sys.sysdercv”.

DBCC results for ‘sys.syssingleobjrefs’.

There are 146 rows in 1 pages for object “sys.syssingleobjrefs”.

DBCC results for ‘sys.sysmultiobjrefs’.

There are 192 rows in 1 pages for object “sys.sysmultiobjrefs”.

DBCC results for ‘sys.sysguidrefs’.

There are 0 rows in 0 pages for object “sys.sysguidrefs”.

DBCC results for ‘sys.syscompfragments’.

There are 0 rows in 0 pages for object “sys.syscompfragments”.

DBCC results for ‘sys.sysftstops’.

There are 0 rows in 0 pages for object “sys.sysftstops”.

DBCC results for ‘sys.sysqnames’.

There are 97 rows in 1 pages for object “sys.sysqnames”.

DBCC results for ‘sys.sysxmlcomponent’.

There are 99 rows in 1 pages for object “sys.sysxmlcomponent”.

DBCC results for ‘sys.sysxmlfacet’.

There are 112 rows in 1 pages for object “sys.sysxmlfacet”.

DBCC results for ‘sys.sysxmlplacement’.

There are 18 rows in 1 pages for object “sys.sysxmlplacement”.

DBCC results for ‘sys.sysobjkeycrypts’.

There are 0 rows in 0 pages for object “sys.sysobjkeycrypts”.

DBCC results for ‘sys.sysasymkeys’.

There are 0 rows in 0 pages for object “sys.sysasymkeys”.

DBCC results for ‘sys.syssqlguides’.

There are 0 rows in 0 pages for object “sys.syssqlguides”.

DBCC results for ‘sys.sysbinsubobjs’.

There are 3 rows in 1 pages for object “sys.sysbinsubobjs”.

DBCC results for ‘sys.syssoftobjrefs’.

There are 2 rows in 1 pages for object “sys.syssoftobjrefs”.

DBCC results for ‘Archive_Tbl_Oracle_Acct_Owner_Status’.

There are 361471 rows in 4726 pages for object “Archive_Tbl_Oracle_Acct_Owner_Status”.

DBCC results for ‘Archive_Acct_ID_W_Badge’.

There are 922345 rows in 9809 pages for object “Archive_Acct_ID_W_Badge”.

DBCC results for ‘RM_Emp’.

There are 2531 rows in 1691 pages for object “RM_Emp”.

DBCC results for ‘RM_Org’.

There are 1177 rows in 2455 pages for object “RM_Org”.

DBCC results for ‘Archive_RM_Org’.

There are 1141 rows in 187 pages for object “Archive_RM_Org”.

DBCC results for ‘acct_cust’.

There are 696111 rows in 64843 pages for object “acct_cust”.

DBCC results for ‘GEO_ORG’.

There are 41613 rows in 5115 pages for object “GEO_ORG”.

DBCC results for ‘sys.queue_messages_1977058079’.

There are 0 rows in 0 pages for object “sys.queue_messages_1977058079”.

DBCC results for ‘sys.queue_messages_2009058193’.

There are 0 rows in 0 pages for object “sys.queue_messages_2009058193”.

DBCC results for ‘sys.queue_messages_2041058307’.

There are 0 rows in 0 pages for object “sys.queue_messages_2041058307”.

DBCC results for ‘sys.filestream_tombstone_2073058421’.

There are 0 rows in 0 pages for object “sys.filestream_tombstone_2073058421”.

DBCC results for ‘sys.syscommittab’.

There are 0 rows in 0 pages for object “sys.syscommittab”.

DBCC results for ‘Acct_ID_W_Badge’.

There are 1028861 rows in 184654 pages for object “Acct_ID_W_Badge”.

DBCC results for ‘Tbl_Oracle_Acct_Owner_Status’.

There are 370732 rows in 17365 pages for object “Tbl_Oracle_Acct_Owner_Status”.

DBCC results for ‘Acct_ID_W_Name’.

There are 332980 rows in 6730 pages for object “Acct_ID_W_Name”.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘CSMB’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Agora que recuperamos o máximo de dados possíveis do que muitos considerariam uma perda total, colocaremos o banco de volta em MULTI_USER, e claro, faremos um backup do mesmo e restauraremos este backup no servidor original, disponibilizando novamente o acesso aos usuários e aplicações:

   1: ALTER DATABASE CSMB SET MULTI_USER;

   2: GO

   3: BACKUP DATABASE CSMB TO DISK='C:\CSMB.bak' WITH COMPRESS;

   4: GO

Pronto! Banco Recuperado!

No meu caso, o usuário testou e encontrou todos os dados e estruturas que ele necessitava, logo a perda de dados foi mínima ou inexistente. Porém podem haver casos em que uma tabela inteira seja perdida.

Obrigado!