Need help recovering a corrupt SQL database

[Daniel A Torres]

I have a very special case that I have been working on for several days. I have a very large SQL Server 2008 database (about 2 TB) that contains 500 filegroups to support very large partitioned tables. Recently we had a catastophic failure on one of the drive and lost several filegroups and the database became in-accessible.

We have been doing filegroup backups on a daily basis, but due to other issues, we lost our most recent backup of the log and the primary filegroup. We have all the data backed up but the primary filegroup backup is old.

There have been no schema changes since the primary filegroup backup, but the lsn's are now all out of sync and we cannot recover the data.

I have tried everything I could think of (and have tried just about every trick and hack I could google) but I still end up at the same point where I get messages saying that the files for filegroup x do not match the primary filegroup. I am now at the point of trying to edit the system tables (we have a separate temporary environment to do this so we are not worried about corrupting any production databases). I have tried updated sys.sysdbreg, sys.sysbrickfiles, and sys.sysprufiles to try to trick SQL into thinking all the files are online, but a "Select * From OPENROWSET(TABLE DBPROP, 5)" shows a different database state from what I see in sys.sysdbreg.

I am now thinking I need to somehow edit the headers of the actual data files to try to line up the lsn's with the primary.

I appreciate any help anyone can give me here, but please do not respond with things like "you are not supposed to do edit mdf, ndf files...." or "see msdn article....", etc. This is an advanced emergency case and I need a real hack so we can just get to the data in this corrupt database and export to a fresh new database. I know there is a way to do this, but not knowing what the DBPROP system functions does (i.e. does it look at system tables or does it actually open the file) is keeping me from trying to figure out how to fool SQL into allowing me to read these files.

Thanks for any help.

[ck9663]

The safest way for you is to RESTORE from your last backup and work your way from there.

Good Luck!!!

~~ CK

[Daniel A Torres]

The most recent copy of the Primary filegroup backup is not available so we have an inconsistent restore. That's the reason I am posting for help. If all I needed to do was perform a restore there would be no point in this post. This is an Emergency situation that requires a hack to recover data.

I am currently trying a demo version of Office Recovery's SQL Server Recovery. It appears to be successful in reading the data from the secondary filegroups. What would be really great is if I had a way to directly read the file headers and the IAM, GAM, and SGAM of each secondary file so I could update the sys.sysallocunits system table. If there are tools out there that can read the files in order to recover the data, there has to be a way to perform these low level tasks, they are just closely held.

Anyone out there know how to read the IAM and GAM of a SQL file (perhaps using a hex editor)?