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. Commented Mar 13, 2023 at 12:33

2 Answers 2

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.

0

You can do most of the fixing in SSMS if it allows the DB to be queried at the top level, if not, it's going to be some ugly coding as the other person has stated...

This is a B-Tree

TOP_LEVEL
|   ┖┐
|    |
a    b
|┖┐  |┖┐
| |  | |
c d  e f

In this case, the B tree looks up, as most B trees do... and we can route everything back to the TOP_LEVEL... so e goes to b and that goes to the TOP_LEVEL... but lets corrupt e the same way your db has?

TOP_LEVEL
|   ┖┐
|    |
a    b
|┖┐--┧┖┐
| |  | |
c d  e f

Uh oh, e has two parents, a and b.

The human friendly error message you would get for this might be.

  1. We expected e to have one parent, but it has 2, a and b.
  2. We expected e to have one parent, but it has 2, b and a.

This is what your two error messages are. They are the same message, but the machine doesn't know which one is supposed to be the real parent. It's a mindless machine, so when it scans both parents, it errors per parent.

So, use those parent ID's and remove the reference from the younger parent. This means the parent with the lower ID, see if that fixes the issue, if not, roll back, try the older ID... if that doesn't work... take the information from the page, and remove the page, and rebuild the page data in the DB.

That should fix the error...

You may get another error after it, if there are multiple children affected, and once those moles are whacked... hopefully your DB will be stable enough to run.

If your child table has non-nullable fields then you will run into a whole other problem, because the table will become a different form of corrupt.. That's the problem with chucking pages out.

New contributor
Marx Izalias is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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