五餅二魚工作室

最近一週收到兩個不同朋友詢問有關於差異備份的問題，雖然兩個人發生的原因都不相同，但都有同樣的現象，就是差異備份幾乎跟完整備份是一樣大，甚至有差異備份還比完整備份檔案大的狀況發生，因此就整理一下這樣的問題，當成一個案例來記錄一下囉。

---

首先我先建立一個範例資料表來做個實驗

-- 建立資料表
CREATE TABLE [BigTable]
(
    A1    INT PRIMARY KEY,
    A2    NVARCHAR(10),
    A3    VARCHAR(10),
    A4    NCHAR(200),
)
GO
 
DECLARE @I INT;
DECLARE @J INT;
DECLARE @K INT;
 
SET @J = 0;
 
-- 產生百萬筆的資料，為了避免交易記錄檔過大，每一萬筆資料放在一個 Transaction 內
SET NOCOUNT ON;
WHILE @J < 100
BEGIN
    SET @I = 0;
    BEGIN TRAN
    WHILE @I < 10000
    BEGIN
        SET @K = @J*10000+@I ;
        INSERT INTO [BigTable] ( A1,A2,A3,A4 ) VALUES ( @K, RIGHT('0000000000'+LTRIM(STR(@K)),10), RIGHT('0000000000'+LTRIM(STR(@K)),10), NEWID())
        SET @I += 1; 
    END    
    COMMIT
    SET @J += 1; 
END
GO

因為要做差異備份的實驗，因此在這個資料庫我先將復原模式設定為「簡單模式」，然後利用指令來進行完整備份

BACKUP DATABASE DEMO TO DISK='D:\Temp\Backup\Demo_Full.BAK' WITH NO_COMPRESSION
GO

從下面的訊息中可以看出來，資料資料稍微多了一點，因此整個備份檔案備份了 56146 頁

接下來我們用一段 SQL 來模擬資料修改

-- 第一次修改 10 筆記錄
DECLARE @I INT = 0
DECLARE @ROW INT ;

WHILE ( @I < 10 )
BEGIN
	SET @ROW = CAST( RAND()*1000000 AS INT ) ;
	SET @I += 1 ;
	UPDATE BigTable SET A4 = NEWID() WHERE A1 = @ROW ;
END

當我們完成修改之後，一樣使用指令來做差異備份，這裡我們刻意不做壓縮，避免實際檔案大小因為壓縮而有所失真。

-- 第一次差異備份 
BACKUP DATABASE DEMO TO DISK='D:\Temp\Backup\Demo_DF1.BAK' WITH NO_COMPRESSION,DIFFERENTIAL
GO

而從下面的訊息中我們可以看到，雖然我們只有更改 10 筆資料，就算一筆資料占用一個 Page，那應該也只有 10 頁有影響，但看起來實際上並不是這樣，包含其他的 Metadata 有修改，因此在這次做差異備份，他會顯示資料頁上有 160 頁有被異動給記錄下來了，雖然是這樣，看起來也比實際 56144 頁來的小很多，因此差異備份是有達到我們所設想要的。

為了觀察異動資料對備份的影響，我們再做第二次的修改來觀察變化

-- 第二次修改 10 筆記錄
DECLARE @I INT = 0
DECLARE @ROW INT ;

WHILE ( @I < 10 )
BEGIN
	SET @ROW = CAST( RAND()*1000000 AS INT ) ;
	SET @I += 1 ;
	UPDATE BigTable SET A4 = NEWID() WHERE A1 = @ROW ;
END
GO

-- 第二次差異備份 
BACKUP DATABASE DEMO TO DISK='D:\Temp\Backup\Demo_DF2.BAK' WITH NO_COMPRESSION,DIFFERENTIAL
GO

在第二次差異備份的時候 , 這個時候更動的資料頁比之前多了 80 頁 , 來到了 240 頁資料的異動，因此我們也成功地完成備份

接著我們做最後一次的修改和備份

-- 第三次修改 10 筆記錄
DECLARE @I INT = 0
DECLARE @ROW INT ;

WHILE ( @I < 10 )
BEGIN
	SET @ROW = CAST( RAND()*1000000 AS INT ) ;
	SET @I += 1 ;
	UPDATE BigTable SET A4 = NEWID() WHERE A1 = @ROW ;
END
GO

-- 第三次差異備份 
BACKUP DATABASE DEMO TO DISK='D:\Temp\Backup\Demo_DF2.BAK' WITH NO_COMPRESSION,DIFFERENTIAL
GO

而這一次也是比前次多了 80 頁的異動 , 更改的資料頁來到了 32 頁

而如果我們同時查看目錄，可以看到除了完整備份的那個檔案比較大，其他的就針對有異動的資料量有些成長，當然第一次差異備份和第二次差異備份雖然異動資料筆數和頁數是有增加，但實體檔案卻沒有差異，整個測試下來看起來差異備份還是按照我們的期望去成長。

---

此時我模擬一下我們所遇到的問題，因為一開始規劃是每週進行完整的備份，而每天來進行差異備份，在這樣的狀況下運行一段時間都沒有問題，後來因為發現有些統計資料逾時或者是資料頁破碎的狀況發生，因此在每天的差異備份之前，在維護計畫內多增加重建索引和更新統計資料的處理，希望透過這樣的方式來讓索引比較快找到資料，但也因為這樣造成了一些問題。

這裡為了方便說明，因此我就手動執行一段指令，來模擬維護計畫內的重建索引

-- 重建 BigTable 的索引
ALTER INDEX ALL ON BigTable REBUILD
GO

當我們如上述指令去重建索引之後，接下來我們再做一次差異備份

-- 第四次差異備份
BACKUP DATABASE DEMO TO DISK='D:\Temp\Backup\Demo_DF4.BAK' WITH NO_COMPRESSION,DIFFERENTIAL
GO

從下面的訊息中可以看到，當我們重建索引之後，會發現在第四次和第三次差異備份之間，其實我們沒有做任何資料修改，但因為重建索引會造成 SQL Server 認為該資料庫有 55928 頁的資料被修改，因此造成 SQL Server 會花較多的時間將這些有「異動」的資料頁給備份起來。

而我們看一下實際的備份檔案大小，會發現這一次的差異備份的檔案居然來到了  448MB，跟完整備份的檔案來說幾乎要一樣大了。

在上述的範例中，我們可以看到因為透過重建索引，雖然資料表的索引沒有任何變動，但對差異備份來說等於該資料表所有資料頁都變動了。也因此在這樣的狀況下，造成差異備份幾乎和完整備份是一樣大小的。因此如果要避免這樣的狀況，建議非必要的情況下，儘量不要去做重建索引的處理，而改用更新統計資料來維護索引的正確性，而如果需要重建索引的時候，就接著做完整備份，避免後續的差異備份檔過大的狀況發生。