Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 10 '11 at 16:44

This question came from our site for professional and enthusiast programmers.

3 Answers 3

I see two options in your future.

  1. Call PSS and open a ticket. It'll take a while to get anywhere with them.
  2. Call SQLskills (Paul to be specific) and see what he can do. I know that Paul has successfully hacked a database back to life using a hex editor before. Paul isn't cheap, but he'll get the job done.

Good luck.

share|improve this answer

Call Microsoft PSS: Open a A level support case. Won't get better than that. Expect to pay. Possibly a lot.

share|improve this answer

We already went the PSS route and they were far from helpful. Just gave us a stock answer of "Backup/Restore is not designed to work that way and we don't support hacks." (Thanks MS, very helpful. I remember 12 years ago when you had a group in PSS that would help with emergencies like this.)

I was all over Pauls blog and found some info very useful, but no info on how to read items like the IAM, GAM, SGAM directly from a file. I know that if I could get to this info that I could edit the sys.sysallocunits table to repair pointers to the datapages for all partitions. I am experimenting with a recovery tool from Office Recovery that seems to be able to read the data, so the data must be recoverable. I just want to get to so

share|improve this answer
    
The GAM and SGAM are at known fixed locations aren't they? –  Martin Smith Jan 11 '11 at 23:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.