在今天的文章里我想谈下每个DBA应该知道的在MS SQL Server数据库恢复工作一个重要话题：在SQL Server里如何进行页级别还原操作。假设在SQL Server里你有一个损坏的页，你要从最近的数据库备份只还原有问题的页，而不是还原整个数据库。

In today’s blog posting I want to cover a very important topic that every DBA should know: how to perform a page level restore operation in SQL Server. Imagine that you have a corrupt page in SQL Server, and instead of restoring a complete database you only want to restore the page in question from the most recent database backup.

我们来破坏一个页 Let’s corrupt a page

第一步我想向你展示下如何建立表（或索引）里有个特定页损坏的情景，这里我们会进行一些魔术，因为开箱即用（out-of-box）的SQL Server本身不会引入任何损坏的页（如果有的话，恭喜你找到了一个BUG）。我们从创建一个新的数据库，往新建的表插入一些记录开始。

In the first step I want to show you how to set up a scenario where one specific page in a table (or index) is corrupt. Of course, we have to perform some magic here because out-of-the-box SQL Server itself will not introduce any corruption (in that case you would have found a bug…). Let’s start by creating a new database and by populating a table within it with some records.

USE master
GO

CREATE DATABASE PageLevelRestores
GO

USE PageLevelRestores
GO

--创建一个表，每条记录都能放在一个8kb的页面上
-- Create a table where every record fits onto 1 page of 8kb
CREATE TABLE Test
(
	Filler CHAR(8000)
)
GO

--插入4条记录
-- Insert 4 records
INSERT INTO Test VALUES (REPLICATE('A', 8000))
INSERT INTO Test VALUES (REPLICATE('B', 8000))
INSERT INTO Test VALUES (REPLICATE('C', 8000))
INSERT INTO Test VALUES (REPLICATE('D', 8000))
GO

--检索记录
-- Retrieve the selected records
SELECT * FROM Test
GO

下一步我进行完整数据库备份。这就是说这个备份包含了属于Test表的所有页。这非常重要，因为接下来我们会破坏这个表的一个特定页。为了找出属于Test表的页，我用DBCC IND命令来返回所有属于这个表的页。

In the next step I perform a full database backup. This means that this backup includes all the pages which belong to the table Test. This is very important, because in the next step we will corrupt one specific page of this table. To find out which pages belong to the table Test, I’m using the DBCC IND command that returns all pages for a specific table.

--执行完整的数据库备份
-- Perform a full database backup
BACKUP DATABASE PageLevelRestores TO DISK = N'd:\PageLevelRestores.bak'
GO

--检索指定表的所在的数据页
-- Retrieve the first data page for the specified table (columns PageFID and PagePID)
DBCC IND(PageLevelRestores, Test, -1)
GO

为了破坏一个特定的页，我使用未公开的DBCC WRITEPAGE命令。是的，在SQL Server里有个可用的DBCC WRITEPAGE命令，但请不要告诉任何人……

To corrupt an actual page, I’m using the undocumented command DBCC WRITEPAGE. Yes, there is a DBCC WRITEPAGE available in SQL Server, but please don’t tell this to anyone…

ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- 让我们破坏77页面
-- Let's corrupt page 77...
DBCC WRITEPAGE(PageLevelRestores, 1, 77, 0, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 77, 1, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 77, 2, 1, 0x41, 1)
GO

ALTER DATABASE PageLevelRestores SET MULTI_USER
GO

为了使用DBCC WRITEPAGE，问题数据库必须设置如代码所示的单用户模式（Single-User mode）。这里我模拟了有个存储错误，写了一些垃圾到存储的页里（是的，这个在你工作中也会碰到！）。现在当你从表再次读取数据库，SQL Server会返回你一个824 I/O错误，因为对损坏页的校验失败了。

To use DBCC WRITEPAGE the database in question must be also set to Single-User mode as the code shows. I’m just simulating here some storage error, where the storage just writes some garbage to a page (yes, this can and even WILL happen sometimes in your career!) Now when you read from the table again, SQL Server will return an 824 I/O error, because the checksum validation for the corrupted page failed.

That is one good reason why you should never ever disable checksum validation in your databases (which is on by default since SQL Server 2005). Without checksum validation you can’t know immediately if a specific page got corrupted…

-- Retrieve the selected records
 SELECT * FROM Test
 GO

消息 824，级别 24，状态 2，第 1 行
SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确(应为: 0x8213ed93，但实际为: 0x2233edb1)。在文件 ‘E:\DataBase\PageLevelRestores.mdf’ 中、偏移量为 0x0000000009a000 的位置对数据库 ID 7 中的页 (1:77) 执行 读取 期间，发生了该错误。SQL Server 错误日志或系统事件日志中的其他消息可能提供了更详细信息。这是一个威胁数据库完整性的严重错误条件，必须立即纠正。请执行完整的数据库一致性检查(DBCC CHECKDB)。此错误可以由许多因素导致；有关详细信息，请参阅 SQL Server 联机丛书。

Msg 824, Level 24, State 2, Line 70
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x8213ed93; actual: 0x2233edb1). It occurred during a read of page (1:77) in database ID 6 at offset 0x0000000025e000 in file ‘E:\DataBase\PageLevelRestores.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that
threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

-- Retrieve the selected records
 SELECT * FROM Test
 GO

现在当你再次查询表时，你会看到SELECT语句成功执行没有任何I/O错误，在这个表里没有丢失任何数据。还是很简单，是不是？

When you now query the table again, you can see that the SELECT statement succeeds without any I/O errors, and that you haven’t lost any data in this table. Almost easy, isn’t it?

小结 Summary

Server里如何进行页级别恢复操作是每个DBA应该知道的。它是你工具箱里最重要的工具之一——尤其当你在处理很大的数据库时。不用恢复整个数据库，你只恢复有问题的页，整个恢复过程会非常快。

How to perform page level restore operation in SQL Server is something that every DBA should know. It is one of the most important things in your toolset – especially when you work with quite large databases. Instead of restoring the complete database, you just restore the page in question, and the whole recovery process will be finished quite fast.

转载请注明：成都千喜数据恢复中心 » 如何在SQL Server中执行页面级还原,SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确….