SqlServer 数据页损坏还原测试

/*
RESTORE DATABASE [TestDBSubA]
FROM DISK = 'E:\DatabaseFile\Backup\TestDBSubA.bak'
WITH RECOVERY , REPLACE
GO
*/

--数据库为完整恢复模式
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', 1, 179, 100, 10, 0x65656565656565656565)

--再检查DB是否正常,发现错误!(出现问题,可以的话先隔离用户访问)
DBCC CHECKDB('TestDBSubA')

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

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

--查看该行记录,正常
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] T
WHERE GUID='65656565-6565-6565-6565-005056c00008'

Msg 8646, Level 21, State 1, Line 1
Unable to find index entry in index ID 1, of table 1019150676, in database 'TestDBSubA'. 
The indicated index is corrupt or there is a problem with the current update plan. 
Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Msg 0, Level 20, State 0, Line 0
当前命令发生了严重错误。应放弃任何可能产生的结果。

/************************最好的办法是还原该数据页************************/

--若此时发生了其他操作
DELETE TOP(10) FROM [TestDBSubA].[dbo].[Test]
WHERE GUID<>'65656565-6565-6565-6565-005056c00008' 
GO
UPDATE T SET VALUE=100 FROM [TestDBSubA].[dbo].[Test] T
GO

USE MASTER
GO
--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 NORECOVERY
GO
/*
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).
*/

--此时操作,仍然报错!~
UPDATE T SET [SID]=SUSER_SID()
FROM [TestDBSubA].[dbo].[Test] T
WHERE GUID='65656565-6565-6565-6565-005056c00008'

SELECT [GUID],[SID],[NAME],[VALUE]
FROM [TestDBSubA].[dbo].[Test]

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

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

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data), page (1:179).
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.

Msg 8928, Level 16, State 1, Line 1
Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data): Page (1:179) could not be processed.
See other errors for details.

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data).
Page (1:419) is missing a reference from previous page (1:179). Possible chain linkage problem.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data).
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.

--解决办法:
--此时再重新备份和还原一次日志即可!
USE MASTER
GO

BACKUP LOG [TestDBSubA]
TO DISK = N'E:\DatabaseFile\Backup\TestDBSub_LOG.bak'
WITH INIT,FORMAT
GO

RESTORE LOG [TestDBSubA]
FROM DISK = 'E:\DatabaseFile\Backup\TestDBSub_LOG.bak'
WITH RECOVERY;
GO

/***************************另一种修复坏页方法,可能丢失数据**************************/
USE master
GO

ALTER DATABASE [TestDBSubA] SET SINGLE_USER --WITH ROLLBACK IMMEDIATE 
GO

DBCC CHECKDB ('TestDBSubA', REPAIR_REBUILD) 
--DBCC CHECKDB ('TestDBSubA', REPAIR_ALLOW_DATA_LOSS) 

ALTER DATABASE [TestDBSubA] SET MULTI_USER
GO

参考:

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

SQL Server Page Restore

还原页 (SQL Server)