SqlServer 数据页损坏还原测试

/*
RESTORE DATABASE [TestDBSubA]
FROM DISK = 'E:DatabaseFileBackupTestDBSubA.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:DatabaseFileBackupTestDBSubA.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')

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

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

--若此时发生了其他操作
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:DatabaseFileBackupTestDBSubA_LOG.bak'
GO

--2.还原之前的完整备份(还原单个数据页)
RESTORE DATABASE [TestDBSubA]
PAGE = '1:179'
FROM DISK = 'E:DatabaseFileBackupTestDBSubA.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:DatabaseFileBackupTestDBSubA_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],[VALUE]
FROM [TestDBSubA].[dbo].[Test]

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

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

BACKUP LOG [TestDBSubA]
TO DISK = N'E:DatabaseFileBackupTestDBSub_LOG.bak'
WITH INIT,FORMAT
GO

RESTORE LOG [TestDBSubA]
FROM DISK = 'E:DatabaseFileBackupTestDBSub_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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!