在今天的文章里我想談下每個(gè)DBA應(yīng)該知道的在MS SQL Server數(shù)據(jù)庫(kù)恢復(fù)工作一個(gè)重要話(huà)題：在SQL Server里如何進(jìn)行頁(yè)級(jí)別還原操作。假設(shè)在SQL Server里你有一個(gè)損壞的頁(yè)，你要從最近的數(shù)據(jù)庫(kù)備份只還原有問(wèn)題的頁(yè)，而不是還原整個(gè)數(shù)據(jù)庫(kù)。

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.

我們來(lái)破壞一個(gè)頁(yè) Let’s corrupt a page

第一步我想向你展示下如何建立表（或索引）里有個(gè)特定頁(yè)損壞的情景，這里我們會(huì)進(jìn)行一些魔術(shù)，因?yàn)殚_(kāi)箱即用（out-of-box）的SQL Server本身不會(huì)引入任何損壞的頁(yè)（如果有的話(huà)，恭喜你找到了一個(gè)BUG）。我們從創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)，往新建的表插入一些記錄開(kāi)始。

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

--創(chuàng)建一個(gè)表，每條記錄都能放在一個(gè)8kb的頁(yè)面上
-- 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

下一步我進(jìn)行完整數(shù)據(jù)庫(kù)備份。這就是說(shuō)這個(gè)備份包含了屬于Test表的所有頁(yè)。這非常重要，因?yàn)榻酉聛?lái)我們會(huì)破壞這個(gè)表的一個(gè)特定頁(yè)。為了找出屬于Test表的頁(yè)，我用DBCC IND命令來(lái)返回所有屬于這個(gè)表的頁(yè)。

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.

--執(zhí)行完整的數(shù)據(jù)庫(kù)備份
-- Perform a full database backup
BACKUP DATABASE PageLevelRestores TO DISK = N'd:\PageLevelRestores.bak'
GO

--檢索指定表的所在的數(shù)據(jù)頁(yè)
-- Retrieve the first data page for the specified table (columns PageFID and PagePID)
DBCC IND(PageLevelRestores, Test, -1)
GO

為了破壞一個(gè)特定的頁(yè)，我使用未公開(kāi)的DBCC WRITEPAGE命令。是的，在SQL Server里有個(gè)可用的DBCC WRITEPAGE命令，但請(qǐng)不要告訴任何人……

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頁(yè)面
-- 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，問(wèn)題數(shù)據(jù)庫(kù)必須設(shè)置如代碼所示的單用戶(hù)模式（Single-User mode）。這里我模擬了有個(gè)存儲(chǔ)錯(cuò)誤，寫(xiě)了一些垃圾到存儲(chǔ)的頁(yè)里（是的，這個(gè)在你工作中也會(huì)碰到?。，F(xiàn)在當(dāng)你從表再次讀取數(shù)據(jù)庫(kù)，SQL Server會(huì)返回你一個(gè)824 I/O錯(cuò)誤，因?yàn)閷?duì)損壞頁(yè)的校驗(yàn)失敗了。

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，級(jí)別 24，狀態(tài) 2，第 1 行
SQL Server 檢測(cè)到基于一致性的邏輯 I/O 錯(cuò)誤 校驗(yàn)和不正確(應(yīng)為: 0x8213ed93，但實(shí)際為: 0x2233edb1)。在文件 ‘E:\DataBase\PageLevelRestores.mdf’ 中、偏移量為 0x0000000009a000 的位置對(duì)數(shù)據(jù)庫(kù) ID 7 中的頁(yè) (1:77) 執(zhí)行 讀取 期間，發(fā)生了該錯(cuò)誤。SQL Server 錯(cuò)誤日志或系統(tǒng)事件日志中的其他消息可能提供了更詳細(xì)信息。這是一個(gè)威脅數(shù)據(jù)庫(kù)完整性的嚴(yán)重錯(cuò)誤條件，必須立即糾正。請(qǐng)執(zhí)行完整的數(shù)據(jù)庫(kù)一致性檢查(DBCC CHECKDB)。此錯(cuò)誤可以由許多因素導(dǎo)致；有關(guān)詳細(xì)信息，請(qǐng)參閱 SQL Server 聯(lián)機(jī)叢書(shū)。

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

現(xiàn)在當(dāng)你再次查詢(xún)表時(shí)，你會(huì)看到SELECT語(yǔ)句成功執(zhí)行沒(méi)有任何I/O錯(cuò)誤，在這個(gè)表里沒(méi)有丟失任何數(shù)據(jù)。還是很簡(jiǎn)單，是不是？

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?

小結(jié) Summary

Server里如何進(jìn)行頁(yè)級(jí)別恢復(fù)操作是每個(gè)DBA應(yīng)該知道的。它是你工具箱里最重要的工具之一——尤其當(dāng)你在處理很大的數(shù)據(jù)庫(kù)時(shí)。不用恢復(fù)整個(gè)數(shù)據(jù)庫(kù)，你只恢復(fù)有問(wèn)題的頁(yè)，整個(gè)恢復(fù)過(guò)程會(huì)非?？?。

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.

轉(zhuǎn)載請(qǐng)注明：成都千喜數(shù)據(jù)恢復(fù)中心 » 如何在SQL Server中執(zhí)行頁(yè)面級(jí)還原,SQL Server 檢測(cè)到基于一致性的邏輯 I/O 錯(cuò)誤 校驗(yàn)和不正確….