Sql-server – SQL Server 2005 database corruption. Advise

corruptiondbccsql server

I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the following errors

    Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2388) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:8807) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8929, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data): Errors found in off-row data with ID 1454243840 owned by data record identified by RID = (1:728:2)
Msg 8928, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:2388) could not be processed.  See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). The off-row data node at page (1:2388), slot 0, text ID 1454243840 is referenced by page (1:728), slot 2, but was not seen in the scan.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysobjvalues' (object ID 60).
Msg 8928, Level 16, State 1, Line 1
Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data): Page (1:8807) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:8807) was not seen in the scan although its parent (1:2862) and previous (1:8806) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:10128) is missing a reference from previous page (1:8807). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'reservation_packages' (object ID 393768460).
Msg 8928, Level 16, State 1, Line 1
Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data): Page (1:5211) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data), page (1:5211). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'reservation_services_log' (object ID 1513772450).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'ChJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ChJ).

Object ID 0?? index ID -1 ?? partition ID 0?? I cannot make out anything out of it

The database is in Simple Recovery mode (unfortunately) and I have a month old backup.

I must have it up an running because it contains vital data.

I would appreciate any help you could give me.

Thanx in advance!

EDIT1:Unfortunately Red Gate's Data Compare cannot register the database while it is scanning the tables… 🙁

Best Answer

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible.

  1. Shut down the applications accessing the database.
  2. BCP export the data, table by table to files. As disk corruption could be the source of your woes, export the data to a portable drive or to a fileshare on another machine.
  3. Restore your old backup to a known good server.
  4. Depending on the complexity of the database schema either truncate the data from your restored backup or script out the schema and create a new empty database.
  5. BCP the data back in.

The data may contain inaccuracies, it may be missing rows or contain spurious values. But with any luck you'll end up with more than was in the month old backup.

Related Question