SqlServer 数据页损坏还原测试

概述/*RESTORE DATABASE [TestDBSubA]FROM DISK = 'E:\DatabaseFile\Backup\TestDBSubA.bak'WITH RECOVERY , REPLACEGO*/--数据库为完整恢复模式ALTER DATABASE [TestDBSubA] SET RECOVERY FULL WITH NO_WAIT--检查DB是否正常

/*RESTORE DATABASE [TestDBSubA]FROM disK = 'E:\Databasefile\Backup\TestDBSubA.bak'WITH RECOVERY,REPLACEGO*/--数据库为完整恢复模式ALTER DATABASE [TestDBSubA] SET RECOVERY FulL WITH NO_WAIT--检查DB是否正常DBCC CHECKDB('TestDBSubA')--备份数据库!BACKUP DATABASE [TestDBSubA]TO disK = N'E:\Databasefile\Backup\TestDBSubA.bak'--找一个数据页DBCC TRACEON(3604,-1)DBCC IND(TestDBSubA,Test,-1)DBCC PAGE('TestDBSubA',1,179,3)--破坏该数据页DBCC WRITEPAGE('TestDBSubA',100,10,0x65656565656565656565)--再检查DB是否正常,发现错误!(出现问题,可以的话先隔离用户访问)DBCC CHECKDB('TestDBSubA')

DBCC results for 'TestDBSubA'.@H_404_11@ …………（此处省略）@H_404_11@ DBCC results for 'sys.syssoftobjrefs'.@H_404_11@ There are 4 rows in 1 pages for object "sys.syssoftobjrefs".@H_404_11@Msg 8933,Level 16,State 1,line 1@H_404_11@ table error: Object ID 1019150676,index ID 1,partition ID 72057594051100672,alloc unit ID 72057594059948032 (type In-row data). @H_404_11@ The low key value on page (1:179) (level 0) is not >= the key value in the parent (1:431) slot 6.@H_404_11@ …………（此处省略）@H_404_11@ CHECKDB found 0 allocation errors and 1 consistency errors in database 'TestDBSubA'.@H_404_11@ repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (TestDBSubA).@H_404_11@ DBCC execution completed. If DBCC printed error messages,contact your system administrator. @H_404_11@@H_404_11@

--继续查看该数据页情况.writepage 更改的偏移量为100(96页头+4个数据字符),替换了5个字符 (如图)@H_404_11@ DBCC PAGE('TestDBSubA',3)@H_404_11@

@H_404_11@

@H_404_11@

--查看该行记录,正常SELECT [GUID],[SID],[name],[VALUE]FROM [TestDBSubA].[dbo].[Test]WHERE GUID='65656565-6565-6565-6565-005056c00008'--第一列[SID]被writepage更改了,所以此时更改[SID]将报错!UPDATE T SET [SID]=SUSER_SID()FROM [TestDBSubA].[dbo].[Test] TWHERE GUID='65656565-6565-6565-6565-005056c00008'

@H_404_11@Msg 8646,Level 21,line 1@H_404_11@ Unable to find index entry in index ID 1,of table 1019150676,in database 'TestDBSubA'. @H_404_11@ The indicated index is corrupt or there is a problem with the current update plan. @H_404_11@ Run DBCC CHECKDB or DBCC CHECKtable. If the problem persists,contact product support.@H_404_11@@H_404_11@ Msg 0,Level 20,State 0,line 0@H_404_11@ 当前命令发生了严重错误。应放弃任何可能产生的结果。 @H_404_11@

/************************最好的办法是还原该数据页************************/--若此时发生了其他 *** 作DELETE top(10) FROM [TestDBSubA].[dbo].[Test]WHERE GUID<>'65656565-6565-6565-6565-005056c00008' GOUPDATE T SET VALUE=100 FROM [TestDBSubA].[dbo].[Test] TGOUSE MASTERGO--1. 备份当前日志BACKUP LOG [TestDBSubA]TO disK = N'E:\Databasefile\Backup\TestDBSubA_LOG.bak'GO--2.还原之前的完整备份(还原单个数据页)RESTORE DATABASE [TestDBSubA]PAGE = '1:179'FROM disK = 'E:\Databasefile\Backup\TestDBSubA.bak'WITH norECOVERYGO/*Processed 1 pages for database 'TestDBSubA',file 'TestPub' on file 1.RESTORE DATABASE ... file=<name> successfully processed 1 pages in 0.072 seconds (0.108 MB/sec).*/--3.将日志还原,前滚恢复到日志备份的时刻RESTORE LOG [TestDBSubA]FROM disK = 'E:\Databasefile\Backup\TestDBSubA_LOG.bak'WITH RECOVERY;GO/*Processed 0 pages for database 'TestDBSubA',file 'TestPub' on file 1.The roll forward start point is Now at log sequence number (LSN) 597000000036800001. Additional roll forward past LSN 597000000038800001 is required to complete the restore sequence.RESTORE LOG successfully processed 0 pages in 0.035 seconds (0.000 MB/sec).*/

@H_404_11@

--此时 *** 作,仍然保存!~UPDATE T SET [SID]=SUSER_SID()FROM [TestDBSubA].[dbo].[Test] TWHERE GUID='65656565-6565-6565-6565-005056c00008'SELECT [GUID],[VALUE]FROM [TestDBSubA].[dbo].[Test]

以上俩语句均报错! @H_404_11@Msg 829,line 1@H_404_11@ Database ID 7,Page (1:179) is marked RestorePending,which may indicate disk corruption. @H_404_11@ To recover from this state,perform a restore. @H_404_11@@H_404_11@

--再检查一边数据库DBCC CHECKDB('TestDBSubA')

Msg 8939,State 98,alloc unit ID 72057594059948032 (type In-row data),page (1:179).@H_404_11@ Test (IS_OFF (BUF_IOERR,pBUF->bstat)) Failed. Values are 133129 and -6.@H_404_11@@H_404_11@ Msg 8928,line 1@H_404_11@ Object ID 1019150676,alloc unit ID 72057594059948032 (type In-row data): Page (1:179) Could not be processed.@H_404_11@ See other errors for details.@H_404_11@@H_404_11@ Msg 8978,alloc unit ID 72057594059948032 (type In-row data).@H_404_11@ Page (1:419) is missing a reference from prevIoUs page (1:179). Possible chain linkage problem.@H_404_11@@H_404_11@ Msg 8976,alloc unit ID 72057594059948032 (type In-row data).@H_404_11@ Page (1:179) was not seen in the scan although its parent (1:431) and prevIoUs (1:420) refer to it. Check any prevIoUs errors. @H_404_11@@H_404_11@

--解决办法:--此时再重新备份和还原一次日志即可!USE MASTERGOBACKUP LOG [TestDBSubA]TO disK = N'E:\Databasefile\Backup\TestDBSub_LOG.bak'WITH INIT,FORMATGORESTORE LOG [TestDBSubA]FROM disK = 'E:\Databasefile\Backup\TestDBSub_LOG.bak'WITH RECOVERY;GO

@H_404_11@@H_404_11@

@H_404_11@

/***************************另一种修复坏页方法,可能丢失数据**************************/USE masterGOALTER DATABASE [TestDBSubA] SET SINGLE_USER --WITH RolLBACK IMMEDIATE GODBCC CHECKDB ('TestDBSubA',REPAIR_REBUILD) --DBCC CHECKDB ('TestDBSubA',REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE [TestDBSubA] SET MulTI_USERGO

@H_404_11@@H_404_11@

参考:

在SQL Server里如何进行页级别的恢复？@H_404_11@

SQL Server Page Restore

还原页 (SQL Server)

总结

以上是内存溢出为你收集整理的SqlServer 数据页损坏还原测试全部内容，希望文章能够帮你解决SqlServer 数据页损坏还原测试所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。