SQL Server使用文件組備份降低備份文件占用的存儲空間

對於DBA來說，備份和刷新簡歷是最重要的兩項工作，如果發生故障後，發現備份也不可用，那麼刷新簡歷的重要性就顯現出來，哇咔咔！當然備份是DBA最重要的事情(沒有之一)，在有條件的情況下，我們應該在多個服務器上保留多份完備和日志備份，甚至某些公司會要求將完備數據保留到磁帶或超大存儲上，以保證可以恢復很久之前的數據。

於是便有一個艱難的選擇：備份空間和備份保存期，磁盤再便宜也是要錢的，尤其某些吝啬的老板寧願多花幾十萬招個人也不寧願在硬件上多投資一丁點，把不得把服務器所有資源都利用起來才高興，在備份空間有限的情況下，我們如何合理設計備份策略以及“備份驗證”策略變得尤為關鍵。

在很久之前讀過一篇文章，描述某DBA為降低數據庫完備占用的存儲空間，采用如下方式：

1. 采用完整備份和日志備份將數據庫還原到特定時間點(如每天凌晨0點)

2. 刪除用戶數據庫上所有非聚集索引，然後壓縮備份

3. 將該備份進行歸檔保存。

從業務角度來說，對於很早之前的數據，即使需要恢復，也不可能將該庫恢復到特定時間點並使用恢復的新庫進行生產，因此對於很早之前的備份，我們只關心數據而不關心數據上建立的那些索引，即使處於查詢需要，也可以重新建立索引後再進行查詢。該DBA正是以此為出發點，很多數據庫上的非聚集索引能占數據庫50%甚至70%的空間(我曾經看過一個表上數十個非聚集索引，部分還是包含索引，占用空間是數據的四五倍以上)，刪除非聚集索引方式能很有效地降低備份占用的存儲空間。

=============================================================

當然上面的廢話不是今天的重點，今天的重點是文件組備份。

周末與小伙伴吃飯時，好友paddy提到一個備份策略，將數據和索引拆分到不同文件組(這策略應該很多DBA都會采用)，然後只備份“數據”文件組，這樣在保證恢復數據的需求的前提下最大限度地降低“數據備份”的占用的存儲空間。

演示Demo:

首先創建數據庫TestDB1001，並創建兩個文件組來分別存放DATA和INDEX

CREATE DATABASE [TestDB1001]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB1001', FILENAME = N'D:\SQLDATA\TestDB1001.mdf' ), 
 FILEGROUP [FG_DATA] 
( NAME = N'TestDB1001_DATA1', FILENAME = N'D:\SQLDATA\TestDB1001_DATA1.ndf' ), 
 FILEGROUP [FG_INDEX] 
( NAME = N'TestDB1001_INDEX1', FILENAME = N'D:\SQLDATA\TestDB1001_INDEX1.ndf'  )
 LOG ON 
( NAME = N'TestDB1001_log', FILENAME = N'D:\SQLDATA\TestDB1001_log.ldf')
GO

PS: 為方便演示，文件增長屬性或其他相關信息被移除，演示代碼請勿較真

然後創建表和插入數據，注意聚集索引和非聚集索引使用的不同的文件組

USE TestDB1001
GO
CREATE TABLE TB001
(
    C1 INT IDENTITY(1,1) NOT NULL,
    C2 INT
)
GO
ALTER TABLE TB001
ADD CONSTRAINT PK_TB001
PRIMARY KEY(c1)
ON FG_DATA
GO
CREATE INDEX IDX_C2
ON TB001
(
C2
)
ON FG_INDEX
GO

INSERT INTO TB001(C2)
SELECT 1 FROM sys.objects

對數據庫進行文件組備份，僅備份PRIMARY和FG_DATA兩個文件組：

BACKUP DATABASE TestDB1001 FILEGROUP = N'PRIMARY',FILEGROUP='FG_DATA' 
TO  DISK = N'D:\SQLDATA\TestDB1001_F1.bak'

對數據庫進行第一次日志備份：

BACKUP LOG TestDB1001
TO DISK = N'D:\SQLDATA\TestDB1001_L1.bak'

為演示需要，第二次插入數據：

INSERT INTO TB001(C2)
SELECT 2 FROM sys.objects

然後進行第一次差異備份

BACKUP DATABASE TestDB1001 FILEGROUP = N'PRIMARY',FILEGROUP='FG_DATA' 
TO  DISK = N'D:\SQLDATA\TestDB1001_D1.bak' WITH DIFFERENTIAL

為演示需要，第三次插入數據：

INSERT INTO TB001(C2)
SELECT 3 FROM sys.objects

然後進行第二次日志備份：

BACKUP LOG TestDB1001
TO DISK = N'D:\SQLDATA\TestDB1001_L2.bak'

備份完成後，我們來驗證備份還原的可行性，

首先進行文件組還原，注意在還原時，由於未備份FG_INDEX文件組，因此還原時不需要制定INDEX相關的文件信息

RESTORE DATABASE [TestDB1002] 
FILE = N'TestDB1001',  
FILE = N'TestDB1001_DATA1'
FROM  DISK = N'D:\SQLDATA\TestDB1001_F1.bak' 
WITH  FILE = 1,  MOVE N'TestDB1001' TO N'D:\SQLDATA\TestDB1002.mdf',  
MOVE N'TestDB1001_DATA1' TO N'D:\SQLDATA\TestDB1002_DATA1.ndf',
MOVE N'TestDB1001_log' TO N'D:\SQLDATA\TestDB1002_log.ldf',  
NOUNLOAD,  STATS = 10,NORECOVERY,PARTIAL

然後還原差異備份：

RESTORE DATABASE [TestDB1002] FROM DISK='D:\SQLDATA\TestDB1001_D1.bak' WITH NORECOVERY

最後還原日志備份：

RESTORE DATABASE [TestDB1002] FROM DISK='D:\SQLDATA\TestDB1001_L2.bak' WITH RECOVERY

驗證數據是否正常：

SELECT C2,COUNT(1) FROM TB001
GROUP BY C2

數據驗證通過，證明該方法的確可行。

========================================================

在進行文件組還原的時候，其中PARTIAL選項非常關鍵，其直接影響後面日志備份是否可用，如果未指定PARTIAL選項，則：

使用WITH RECOVERY選項還原差異備份，不報錯，數據庫仍處於“正在還原”模式下，還原信息為：

已為數據庫 'TestDB1002'，文件 'TestDB1001' (位於文件 1 上)處理了 72 頁。
已為數據庫 'TestDB1002'，文件 'TestDB1001_DATA1' (位於文件 1 上)處理了 16 頁。
已為數據庫 'TestDB1002'，文件 'TestDB1001_log' (位於文件 1 上)處理了 3 頁。
通過數據庫或文件還原操作，只還原了文件“TestDB1001_INDEX1”的一部分。必須成功還原整個文件後，才能應用此備份集。
此 RESTORE 語句成功地執行了一些操作，但由於需要一個或多個 RESTORE 步驟，無法使數據庫在線。以前的消息說明了此時無法進行恢復的原因。
RESTORE DATABASE ... FILE=<name> 成功處理了 91 頁，花費 0.059 秒(11.983 MB/秒)。

使用WITH RECOVERY選項還原日志備份，直接報錯，錯誤消息為：

消息 4320，級別 16，狀態 13，第 1 行
通過數據庫或文件還原操作，只還原了文件“TestDB1001_INDEX1”的一部分。必須成功還原整個文件後，才能應用此備份集。
消息 3119，級別 16，狀態 1，第 1 行
在計劃 RESTORE 語句時發現了問題。以前的消息提供了詳細信息。
消息 3013，級別 16，狀態 1，第 1 行
RESTORE DATABASE 正在異常終止。

因此在還原文件組備份時，請務必確保使用PARTIAL選項。

========================================================

打完收工，再次感謝paddy的提點。

最近很少寫博客，也沒有收集妹子的動力，妹子質量下降，各位將就下。。。