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.


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.


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


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

  • The GAM and SGAM are at known fixed locations aren't they? – Martin Smith Jan 11 '11 at 23:09

Yes, restoring the db from updated backup is the best way to get all the data back. If you do not have any updated backup then, you can go with the emergency repair option.

Two months ago, I have a discussion with Grant Fritchey, who is Microsoft Data Platform MVP about the emergency repair of SQL database. He reviewed a SQL repair software and post the review on his blog.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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