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 TestDB1001GOCREATE TABLE TB001(    C1 INT IDENTITY(1,1) NOT NULL,    C2 INT)GOALTER TABLE TB001ADD CONSTRAINT PK_TB001PRIMARY KEY(c1)ON FG_DATAGOCREATE INDEX IDX_C2ON TB001(C2)ON FG_INDEXGOINSERT 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 TestDB1001TO 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 TestDB1001TO 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 TB001GROUP 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的提點。

最近很少寫部落格，也沒有收集妹子的動力，妹子品質下降，各位將就下。。。

SQL Server使用檔案組備份降低備份檔案佔用的儲存空間