Sql-server – Reattaching MDF files recovered from RAID 5

corruptionrecoverysql serversql-server-2005

We had been running a SQL Server 2005 server for many years at our company when just recently, we ran into some trouble. The server was configured with RAID 5 and had one hot spare disk in the array. One of the disks failed, and then a second one failed shortly thereafter. Unfortunately we did not have backups of much of this data (please spare me the "should have had backups" comments — I am aware of the importance of a solid backup plan).

So we are two disks down, we have no official DBA, and the database is inaccessible. One of our IT personnel attempted to recover the RAID array and we believe the RAID began rebuilding but he was ordered to shut it down because there was concern for potential data loss during the RAID rebuild.

We sent the entire server to a data recovery company. They spent two weeks working on the server, first by imaging all the drives and then by reconstructing the data from the RAID.

I received a hard drive that contains all the "recovered" files. This is a collection of mostly database files (MDF) and trans logs (LDF). Each database that was on the server consisted of only a single MDF and a single LDF. I do not currently have access to the original server, so I have been attempting to attach these databases to another server running SQL Server 2005. However, when I try to reattach any of the databases I run into problems. Some of the various errors I have received are shown below. I have had no luck restoring any of these files. I followed the steps outlined in this article, and even then was unable to restore any of the files.

We paid a large sum to the data recovery company for the recovery of the data from this server and I have been tasked with proving without a doubt that the data are or are not useful to us. I simply don't have the experience to know for certain, so I put the question to you, dba.stackexchange: Is there any way at all to restore these files?

SQL Server detected a logical consistency-based I/O error: incorrect
pageid (expected 1:134; actual 0:0). It occurred during a read of page
(1:134) in database ID 17 at offset 0x0000000010c000 in file
'G:\Data\Data201309.mdf'. 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.

The header for file 'G:\Data\Data201112_log.ldf' is not a valid
database file header. The PageAudit property is incorrect. (Microsoft
SQL Server, Error: 5172)

G:\Data\Data201005.mdf is not a primary database file. (Microsoft SQL
Server, Error: 5171)

An error occurred while processing the log for database 'Data201008'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe9b50f3a; actual: 0x162a0f84). It occurred during a read of page (1:511232) in database ID 17 at offset 0x000000f9a00000 in file 'G:\Data\Data200803.mdf'. 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.
Could not open new database 'Data200804'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

Best Answer

Is there any way at all to restore these files?

No.

The long answer is like this:

  • you first will have to fix the MDFs. Attempt to attach it with ATTACH_REBUILD_LOG
  • as you encounter errors (like incorrect pageid (expected 1:134; actual 0:0). It occurred during a read of page (1:134)) you have to edit the file and fix it. This, of course, requires you to know in fairly much detail the (undocumented) MDF format. Anatomy of Page is a decent starting point, but it really just skims the surface. I really hope your MDF boot page is correctly 'restored'.
  • after you fix all the errors, try to evaluate the consistency of the MDF, if possible with a CHECKDB. Fix any error, some will require again manual repair.
  • in theory you can try to attempt to see how much of the recovered LDFs can be repaired and applied. I estimate this task something 10x the complexity of fixing the MDF, due to the complexities of the LDF format and because of the LSN chain requirement. I would abandon the idea of trying to rebuild the 'restored' log, just try to 'rebuild' the restored data.

You can use OrcaMDF to attempt to browse the MDFs 'cold' and see if OrcaMDF complains about structure issues, then attempt to fix them.

It is critical that the 'restore' process kept the MDF file size, and restores 0s for bad sectors. If they simply removed the unrecoverable sectors you stand no chance.

Related Question