http://www.cnblogs.com/CareySon/category/518741.html
如果我们存在完善的备份的话,我们可以通过备份进行页还原(在此再次强调一下对于DBA来说,有”备”无患),一个简单的页还原代码如代码清单1所示。
USE [master]
RESTORE DATABASE [Corrupt_DB] PAGE='1:155'
FROM DISK = N'C:\xxx.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5从备份中还原文件ID1中的第155页
http://www.cnblogs.com/nzperfect/archive/2009/09/27/1575102.html
当你从sql server log里或是在程序查询数据库或是定期通过DBCC CHECKDB为数据库做体检的时候,出现了页损坏或校验和出错信息时,如:
--
-------------------------------------------------------------------------------------------------------------------------------
M8928sg , Level 16 , State 1 , Line 1
Object ID 2088535921 , index ID 0 , partition ID 72345201021503994 , alloc unit ID 72345201051571606 (type In- row data): Page ( 1 : 94299 ) could not be processed. See other errors for details.
Msg 8939 , Level 16 , State 98 , Line 1
Table error: Object ID 2088535921 , index ID 0 , partition ID 72345201021503994 , alloc unit ID 72345201051571606 (type In- row data), page ( 1 : 94299 ). Test (IS_OFF (BUF_IOERR, pBUF -> bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ' yourtable ' (object ID 2088535921 ).
CHECKDB found 0 allocation errors and 2 consistency errors in database ' yourdb ' .
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (yourdb).
-- -------------------------------------------------------------------------------------------------------------------------------
M8928sg , Level 16 , State 1 , Line 1
Object ID 2088535921 , index ID 0 , partition ID 72345201021503994 , alloc unit ID 72345201051571606 (type In- row data): Page ( 1 : 94299 ) could not be processed. See other errors for details.
Msg 8939 , Level 16 , State 98 , Line 1
Table error: Object ID 2088535921 , index ID 0 , partition ID 72345201021503994 , alloc unit ID 72345201051571606 (type In- row data), page ( 1 : 94299 ). Test (IS_OFF (BUF_IOERR, pBUF -> bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ' yourtable ' (object ID 2088535921 ).
CHECKDB found 0 allocation errors and 2 consistency errors in database ' yourdb ' .
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (yourdb).
-- -------------------------------------------------------------------------------------------------------------------------------
现在我们应该如何做?
1.通过上面的提示,告诉我们:对象 2088535921出错,它是一个表,页面为1:94299
2.接下来,我们判断损坏的页在堆上还是聚集索引还是非聚集索引,sql server方法为:
dbcc
traceon (
3604
,
-
1
)
go
dbcc page( ' yourdb ' , 1 , 94299 , 3 )
go
go
dbcc page( ' yourdb ' , 1 , 94299 , 3 )
go
在输出的结果里(会报错,但可以看到页头信息),可以看到
Metadata: IndexId
=
n
如果n是0而表示是堆,1表示是聚集索引,>1是表示非聚集索引
ps:其实从提示信息的Object ID 2088535921, index ID 0 ,也可以简单判断是堆.
3.根据上面的第2步,我们知道这个页面是堆,这对我们来讲,不是好消息,因为如果是>1,我们可以删除该非聚集索引,再重建索引,不会丢失数据,而0或1则是元数据受损,这意味着有丢失元数据的可能性。
那么如何仅仅修复这个数据页呢,这里我们假设该库是full模式,并且有良好的备份策略,有全备和日志备份。
那么我们可以进行页面级还原操作,步骤如下:
a.首先进行一次日志备份,如果你不放心,还可以再做一个全备;
backup log yourdb to disk = ' D:\DBBak\yourdb_a.trn '
b.通过完整备份来恢复该page. (yourdb.bak是一个全备。);
restore database yourdb page = ' 1:94299 ' from disk = ' D:\DBBak\yourdb.bak ' with norecovery
c.恢复这个全备之后的差异(假设有差异yourdb.dif),如果没有差异备,直接到d步骤;
restore database yourdb from disk = ' d:\DBBak\yourdb.dif ' with norecovery
d.恢复之后的log备份,可能有多个(假设为yourdb_1.trn,yourdb_2.trn);
restore log yourdb from disk = ' d:\DBBak\yourdb_1.trn ' with norecovery
restore log yourdb from disk = ' d:\DBBak\yourdb_2.trn ' with norecovery
restore log yourdb from disk = ' d:\DBBak\yourdb_a.trn ' with norecovery
e.做一个最新的日志备;
backup log yourdb to disk = ' D:\DBBak\yourdb_e.trn '
f.还原最后的(e步骤)日志备份;
restore log yourdb from disk = ' d:\DBBak\yourdb_e.trn ' with recovery
g.结束
4 .经过步骤三之后,我们再来检查一下该表是否还有错,从提示信息Object ID 2088535921里,我们查出表名tbname;
tbname: select object_name ( 2088535921 )
然后 dbcc checktable( ' yourtable ' )检测,如果没有报错,则表示修复完成
5 .最后,对整个库再做一次dbcc checkdb检查;
backup log yourdb to disk = ' D:\DBBak\yourdb_a.trn '
b.通过完整备份来恢复该page. (yourdb.bak是一个全备。);
restore database yourdb page = ' 1:94299 ' from disk = ' D:\DBBak\yourdb.bak ' with norecovery
c.恢复这个全备之后的差异(假设有差异yourdb.dif),如果没有差异备,直接到d步骤;
restore database yourdb from disk = ' d:\DBBak\yourdb.dif ' with norecovery
d.恢复之后的log备份,可能有多个(假设为yourdb_1.trn,yourdb_2.trn);
restore log yourdb from disk = ' d:\DBBak\yourdb_1.trn ' with norecovery
restore log yourdb from disk = ' d:\DBBak\yourdb_2.trn ' with norecovery
restore log yourdb from disk = ' d:\DBBak\yourdb_a.trn ' with norecovery
e.做一个最新的日志备;
backup log yourdb to disk = ' D:\DBBak\yourdb_e.trn '
f.还原最后的(e步骤)日志备份;
restore log yourdb from disk = ' d:\DBBak\yourdb_e.trn ' with recovery
g.结束
4 .经过步骤三之后,我们再来检查一下该表是否还有错,从提示信息Object ID 2088535921里,我们查出表名tbname;
tbname: select object_name ( 2088535921 )
然后 dbcc checktable( ' yourtable ' )检测,如果没有报错,则表示修复完成
5 .最后,对整个库再做一次dbcc checkdb检查;
ps:需要注意的是,sql server 的page级恢复在企业版和开发版中,支持联机恢复page数据,在标准版只能脱机修复;
在dbcc checkdb修复选项里,用repair_rebuild修复数据,联机文档称是不丢失数据,但在某些环境下可能也会丢失数据,不过,我没遇到过:)
用repair_allow_data_loss选项时,联机文档称可能会丢失数据,而对于堆或聚集索引的页损坏,sql server 会释放该页面,造成数据的丢失,但repair_allow_data_loss选项有两种情况是不会丢失数据,一种是非聚集索引上的页错误,另外是lob页数据错误。