Database repair series Part3: repair_allow_data_loss

Run DBCC checkdb withno_infomsgs and find the following error:

 

Table error: objectid 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type in-row data), page ). test (m_type> = data_page & m_type <= undofile_header_page) | (m_type = unknown_page & level = basic_header ))
Failed. Values are 0 and 0.

MSG 8939, level 16, state 5, line 4

Table error: objectid 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type in-row data), page ). test (m_headerversion = header_7_0) failed. values are 0 and 1.

MSG 8939, level 16, state 6, line 4

Table error: objectid 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type in-row data), page ). test (m_type> = data_page & m_type <= undofile_header_page) | (m_type = unknown_page & level = basic_header ))
Failed. Values are 0 and 0.

Repair_allow_data_loss is the minimum repairlevel for the errors found by DBCC checkdb (corrupt2008demofatalpolicuption ).

 

The minimum repair level is repair_allow_data_loss.

 

 

If we do not have database backup, can not use page restore, then we need to use repair_allow_data_loss to repair (there will be data loss, and not all are can be restored reference: http://blog.csdn.net/smithliu328/article/details/7827147

)

 

Next we will use DBCC checkdh repair_allow_data_loss to repair the damaged database.

--- Change the database status to emergency mode

Alter database corrupt2008demofatalcorruption setemergency

Go

-- Change the database to single-user access

Alter database corrupt2008demofatalcorruptionsetsingle_user

Go

-- Run repair_allow_data_loss to fix

DBCC checkdb (corrupt2008demofatalcorruption, repair_allow_data_loss)

Go

--- After the fix is completed, run DBCC checkdb to confirm there is no problem

DBCC checkdb withno_infomsgs

Go

-- Change the database to multi-user access

Alter database corrupt2008demofatalcorruptionsetmulti_user

 

If the recommended repair level is repair_rebuild, You can execute it without any data loss. This includes fast repairs (such as fixing missing rows in non-clustered indexes) and time-consuming repairs (such as re-indexing ).

 

Note:

Only use the repair option as the final method. To fix the error, we recommend that you restore it through backup. The repair operation does not consider any constraints that may exist between tables. If the specified table is related to one or more constraints, we recommend that you run DBCC checkconstraints after the repair operation. If you must use repair, run DBCC checkdb without the Repair Option to find the repair level to be used. If you use At the repair_allow_data_loss level, we recommend that you back up the database before running DBCC checkdb with this option.

 

Two steps are used to compare data loss:

1. Create a copy of the specified upt database before running repair so you can compare the prerepair and postrepair data and see what is missing. This may be tricky to do if
Database is badly used upt-you may need to use the with continue_after_error options of backup and restore to do this.
2. start an explicit transaction before running repair. it is not very well known that you can run repair inside a transaction. after repair completes, you can examine the database to see what repair did, and if you want to undo the repairs, you can simply roll
Back the explicit transaction.