五餅二魚工作室

SQL Server 從 2005 版本開始就有提供「還原頁面」的功能，但如果要來實作一下，該怎麼來進行呢 ? 過程有點小麻煩，因此我把相關的步驟，包含製作出損毀的頁面，都整理一下，這樣以後要做 Lab 的時候就會容易一點了。

 

首先我們先用下面的語法建立一個測試用的資料庫，這段語法包含建立資料庫 DEMO2016，並且產生一個測試用的資料表 T1，在資料表內放入一些測試資料。

USE [master]
GO

IF DB_ID('DEMO2016') IS NOT NULL 
	DROP DATABASE DEMO2016;

CREATE DATABASE DEMO2016;
GO

ALTER DATABASE DEMO2016 SET RECOVERY FULL;
GO

USE [DEMO2016]
GO

CREATE TABLE T1( A1 INT PRIMARY KEY, A2 CHAR(128) )
GO

INSERT INTO T1( A1 , A2 ) VALUES
	( 1, REPLICATE( 'A' , 128 )),
	( 2, REPLICATE( 'B' , 128 )),
	( 3, REPLICATE( 'C' , 128 )),
	( 4, REPLICATE( 'D' , 128 )),
	( 5, REPLICATE( 'E' , 128 )),
	( 6, REPLICATE( 'F' , 128 ))
GO

 

接著我們透過兩個指令 DBCC IND 和 DBCC PAGE 來做查看：

-- dbcc IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
DBCC IND(DEMO2016, T1, 1)
GO

DBCC TRACEON(3604);
GO

-- dbcc PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC PAGE(DEMO2016, 1, 94, 1);
GO

DBCC TRACEOFF(3604);
GO

 

使用 DBCC IND 主要是用來查 DEMO2016 資料庫中的 T1 資料表的 PAGE 的使用狀況：

透過回傳的資料，我們可以先找 PageType 是 1 的資料 ( 1 – data page、2 – index page、10 – IAM page ) ，關於 PageType 可以參考這個網址內的說明，因此找到他的 Page 編號是  94；

 

接著我們就可以再透過 DBCC PAGE 的指令去看 Page 內的資訊，而當要使用 DBCC PAGE 的時候，則需要啟動旗標 3604 ；這裡我們指定去查 DEMO2016 裡面 File ID : 1 和 Page ID : 94 內的資料，從看到的訊息中我們看到這個 Page 內的 Slot 0 ，是在這個頁面內偏移 96 Bytes ( 0x60 是十六進位，因此換算為十進位是 96 )

在我們開始修改頁面資料之前，因為我們是要實作「還原頁面」，因此這裡我們先針對這個資料庫進行完整備份和交易紀錄備份。這個部分為了方便實作處理，因此我們將備份資料都存在同一個檔案內。

BACKUP DATABASE DEMO2016 TO DISK=N'D:\DEMO\DEMO2016.BAK' WITH INIT;
GO

BACKUP LOG DEMO2016 TO DISK=N'D:\DEMO\DEMO2016.BAK';
GO

 

接著我們就可以透過指令來修改 PAGE 的內容，用來模擬資料頁面內有損毀的狀況發生。因為都我們要用 DBCC WRITEPAGE 的方式強迫寫入資料到 Page 內的時候，首先要先將用 ALTER 指令將欲處理資料庫，設定進入到 SINGLE USER 的模式下。完成之後我們就可以用 DBCC WRITEPAGE 的指令來將資料強迫寫入到資料業內了。這裡我們指定要寫入到 DEMO2016 資料庫內的第一個資料檔內的 Page 94 內，而在前面我們看到 Page 內，因為 Slot 內還有一些管理資訊和欄位 A1 的資料，因此如果我要改這筆資料 A2 欄位的第二碼，那麼就是在這個 Page 內的 105 的位置 ( 因為是從 0 開始計算的，加上偏移量是 96 ，因此要寫入的會是第 106 位元的地方就是 105 的位置上 )，寫入一個 Z 的資料 ( 0x5A 是 Z 的 ASCII 碼)

USE [master]
GO

ALTER DATABASE [DEMO2016] SET SINGLE_USER;
GO

-- dbcc WRITEPAGE ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
DBCC WRITEPAGE (N'DEMO2016', 1, 94, 105, 1, 0x5A, 1);
GO


ALTER DATABASE [DEMO2016] SET MULTI_USER;
GO

 

當修改完畢之後，我們再透過 DBCC PAGE 的指令查看一下，看是否真的有被修改

DBCC TRACEON(3604);
GO

-- dbcc PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC PAGE(DEMO2016, 1, 94, 1);
GO

DBCC TRACEOFF(3604);
GO

 

從下圖中可以看到當使用 DBCC WRITEPAGE 之後，果然在資料頁內被我們給竄改了，原本第一個資料的 A2 欄位中的第二碼，已經被我們修改為 Z 了。

因為 SQL 2005 之後，預設的資料庫選項中的頁面確認( Page Verify ) 預設是 CHECKSUM，因此當我們如果直接竄改 Page 的內容，會造成頁面不一致的狀況發生。所以如果這個時候我們去查看資料表的內容

USE [DEMO2016]
GO

SELECT * FROM T1
GO

 

就會出現以下的錯誤訊息了

除了透過上述系統回報的時候取得錯誤頁面的資訊之外，另外也可以透過 msdb 內的  suspect_pages 的資料表，取得相關錯誤頁面的資訊，也是一種可以用的方式。

 SELECT * FROM msdb..suspect_pages

---

 

在前面的過程中我們透過一些指令，已經模擬出一個頁面損毀的資料庫，接著我們就可以來使用相關指令來做修正了。一般在做還原頁面的時候會有 Online 和 Offline 的兩種方式，基本上 Oline 只能在 Enterprise 的版本來進行，Offline 則可以在 Standard 以上的版本來進行；以下我們先展示 Offline 的方式，從下面的指令中可以看出來，跟一般的處理沒有太大的差異，在一開始就先做結尾記錄備份，此時資料庫就進入到還原中的狀況了。後續在還原資料庫的時候要多加上 PAGE='1:94' 的資訊，表示只要還原這一頁的資料，後面就加入相關的交易紀錄還原。

BACKUP LOG DEMO2016 TO DISK=N'D:\DEMO\DEMO2016.BAK' WITH NORECOVERY;
GO

RESTORE DATABASE DEMO2016 PAGE='1:94'
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=1, NORECOVERY;
GO

RESTORE LOG DEMO2016 
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=2, NORECOVERY;
GO

RESTORE LOG DEMO2016 
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=3, NORECOVERY;
GO

RESTORE DATABASE DEMO2016 
	WITH RECOVERY;

 

而如果是 Enterprise 的版本，則我們可以做線上還原頁面的處理，因此指令碼的順序會有點不同：

RESTORE DATABASE DEMO2016 PAGE='1:94'
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=1, NORECOVERY;
GO

RESTORE LOG DEMO2016 
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=2, NORECOVERY;
GO

BACKUP LOG DEMO2016 TO DISK=N'D:\DEMO\DEMO2016.BAK' WITH NORECOVERY;
GO

RESTORE LOG DEMO2016 
	FROM DISK=N'D:\DEMO\DEMO2016.BAK'
	WITH FILE=3, NORECOVERY;
GO

RESTORE DATABASE DEMO2016 
	WITH RECOVERY;

 

從上述語法中會發現，我們一開始就先做還原，在做這個的過程中，除了 T1 的資料表不能存取外，其他的資料都可以讀取和變動。而在所有備份當還原完畢之後，才進行結尾紀錄備份，此時資料庫才進入到還原中，後面可以快速地還原該交易紀錄，並且將資料庫給復原，因此使用這樣的方式，可以讓影響線上的時間縮短到最小。

---

 

不論採用離線方式或線上方式，都可以很順利的完成還原頁面的功能，因此當完成之後，我們再透過指令查看資料表，會發現資料都可以順利讀取出來了。

USE [DEMO2016]
GO

SELECT * FROM T1
GO