数据库修复系列Part3:repair

运行 DBCC CHECKDB with NO_INFOMSGS 发现下面的错误： Table error: ObjectID 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064(type In-row data), page (1:54). Test ((m_type = DATA_PAGE m_type = UNDOFILE_HEADER_PAGE) |

运行DBCC CHECKDB withNO_INFOMSGS发现下面的错误：

Table error: ObjectID 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064(type In-row data), page (1:54). 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 (1:54). 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 (1:54). 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 (Corrupt2008DemoFatalCorruption).

最小的修复级别是repair_allow_data_loss

如果我们没有数据库备份，无法使用页面还原，那么我们就需要用repair_allow_data_loss来修复（会有数据损失，而且不一定所有的都是可以恢复的 参考：http://blog.csdn.net/smithliu328/article/details/7827147

）

下面我们就使用DBCC CHECKDH repair_allow_data_loss来修复损坏的数据库。

---将数据库状态改为紧急模式

ALTER DATABASE Corrupt2008DemoFatalCorruption SETEMERGENCY

GO

--将数据库改为单用户访问

ALTER DATABASE Corrupt2008DemoFatalCorruptionSETSINGLE_USER

GO

--运行repair_allow_data_loss修复

DBCC CHECKDB(Corrupt2008DemoFatalCorruption,repair_allow_data_loss)

Go

---修复完成后运行DBCC CHECKDB确定没有问题

DBCC CHECKDB withNO_INFOMSGS

Go

--将数据库更改为多用户访问

ALTER DATABASE Corrupt2008DemoFatalCorruptionSETMULTI_USER

两个步骤用来对比数据丢失：

1.Create a copy of the corrupt 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 the
database is badly corrupt—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.