0

I have a corrupt MS SQL db.

We do have a good backup prior to the issue we had. All is now fixed. However, we are left with a corrupt database, with 2 weeks of good data in it.

The issue is we can't merge data from a corrupt database into a good backup.

  • I cannot delete or denale a corrupted table (to re-create it)

Any pointers on how I can fix this further, any out-of-the-box ideas on getting good data from a corrupt database into a good old backup?

much repairs done using >

select name, user_access_desc from sys.databases where name='mydatabase'
DBCC CHECKDB ('mydatabase', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS

1st run the txt output of repairs is some 52meg
2nd run this text field output is down to 17meg
3rd run is down to 317kb
4th run does not improve\

So I've now got 421 consistency errors that I'm looking to patch up manually >

Msg 8937, Sev 16, State 1, Line 1 : Table error: Object ID 268580045, index ID 1, partition ID 72057594062307328, alloc unit ID 72057594095075328 (type In-row data). B-tree page (1:21289893) has two parent nodes (1:21112771), slot 32 and (1:21283144), slot 458. [SQLSTATE 42000]

and

Msg 8980, Sev 16, State 1, Line 1 : Table error: Object ID 268580045, index ID 1, partition ID 72057594062307328, alloc unit ID 72057594095075328 (type In-row data). Index node page (1:21112771), slot 46 refers to child page (1:21213185) and previous child (1:21213184), but they were not encountered. [SQLSTATE 42000]

both repeated hundreds of times...

I can see the affected table by using

SELECT * FROM sys.objects WHERE object_id = 268580045;

But there I'm getting stuck .

1
  • 1
    Before you start off with any of this, have you ensured that you're doing this on a server that's been given a clean bill of health on all hardware components and not the cause of, or subject to, any further corruption? Otherwise this is going to be a bit like mopping the floor with the water still running -- your first priority should be continuity of the new setup; how you're going to get your data back is only the next step. Also note that your DB will not actually have "two weeks of good data in it" -- because it's corrupted. It could be anywhere from 14 days to 0. Mar 13 at 12:33

1 Answer 1

0

FWIW, I have done this for a client, when MSSQL refused to do SELECT. It required some client coding (I used C# and ADO.NET, you can use whatever is suitable for you and support submitting SELECT to MSSQL and reading the result).

Assuming that that IAM pages are healthy you can determine which pages/extents the table owns. Unfortunately sys.dm_db_database_page_allocations (or DBCC IND) isn't documented but there is some info out there on how to use it.

Now, based on above result, for each page that the table is using, I used DBCC PAGE with printopt 3 to get the result from the page as a result set. Your client code (again, developed with whatever language that suits you, and that supports accessing MSSQL) will insert that data into a table on a healthy SQL Server.

Perform above process for each corrupt table. The way I did it, I wrote C# client code adapted for each table. My client had three tables that were important enough to pay for this work for. Also, be prepared that the pure data processing part can take a while if you have larger datasets.

Or, as suggested, use some data recovery software out there that might save you this development effort.

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.