資料庫備份

認識備份

資料庫的復原模式

這篇文章明明是要介紹備份的，為什麼一開始確先討論復原模式？ 因為資料庫的復原模式會與交易記錄的保存方式有關。 而交易記錄的保存方式會與資料庫執行復原時的復原點目標（Recovery Point Objective, RPO）相關。

復原點目標簡單講就是當災難發生時，最大可容忍資料遺失的程度。例如，二小時，一小時，或者都不能接受任何資料遺失，則 RPO 為零。 所以要操作資料庫的備份前，必須先認識資料庫的復原模式，才能確實作好備份工作的安排。

資料庫的復原模式有三種，簡單、完整、大量記錄，如下圖所示：

簡單（Simple）

「簡單模式」不會執行 Transaction Log 的復原，
所以備份時也僅支援「完整備份」和「差異備份」，不支援「交易記錄備份」。
所以在簡單復原模式下，如果發生資料庫損毀，可能就得承受遺失最後一次備份以後的記錄。

也就是說，若你將資料庫設定為「簡單復原模式」，那麼你只可以執行「完整備份」和「差異備份」。
若發生災難，你必須承受遺失最近一次備份後的所有交易記錄。

ALTER DATABASE [TestSB] SET Recovery Simple

完整（Full）

完整復原模式可以執行 Transaction Log 的復原，所以除了支援「完整備份」和「差異備份」，也支援「交易記錄備份」。

使用完整復原模式的資料庫，除了可以設定「完整備份」和「差異備份」外，也必須設定「交易記錄備份」，才能確保還原作業可以不遺失任何記錄，並將資料庫還原到過去任何時間點。

ALTER DATABASE [TestSB] SET Recovery Full

大量記錄（BULK_LOGGED）

大量記錄復原模式，用法類似完整復原模式。 不過，大量記錄模式有一點比較特殊， 它只會保留絕大多數的交易記錄，但是不保存 BULK 行為產生的交易記錄(如建立索引或大量異動資料)。 雖然這個復原模式支援三種備份方式，然而無法保證能將資料庫還原到過去任何時間點。

ALTER DATABASE [TestSB] SET Recovery BULK_LOGGED

另外，通常在重建索引時，也會產生許多的交易記錄，如果復原模式設定為大量記錄或簡單，就產生不會交易記錄。

備份類型

完整備份（Full database backups）

「完整備份」會備份以下內容

• 資料庫中的所有物件和資料。
• 備份期間發生的交易。

差異備份（Differential backups）

「差異備份」會備份以下內容

• 上一次完整備份到目前之間的異動資料。
• 只會備份異動的最後結果，而不會記錄異動歷程。

交易記錄備份（ransaction log backups）

「交易記錄備份」會備份以下內容

• 上一次交易記錄備份到目前之間新產生的交易記錄。
• 在預設的情況下會自動截斷交易記錄(限已committed或cancelled)

交易記錄備份的特性如下：

• 資料的「復原模式」必須設定為「完整」或「大量記錄」。
• 保存資料的完整交易記錄。
• 可以將資料庫復原至過去某一時間點。

差異備份 vs 交易記錄備

差異備份與交易記錄備份容易混淆：

• 差異備份：僅備份最終結果。還原時也只能還原最後一次差異備份。
• 交易記錄備份：備份每一次的異動記錄。還原時可以指定還原到任意一次的交易記錄備份。

其他備份選項

記錄特定選項（Log-specific Options）

「記錄特定選項」僅能搭配 BACKUP LOG 使用，不適用於 BACKUP DATABASE 。

會使用記錄特定選項，通常都是使用在容錯移轉的作業中。 當主系統發生異常，但資料庫尚可存取的狀況下，通常我們會執行結尾交易記錄備份以保留最完整的資料。 這時候就可以搭配 NORECOVERY 或 STANDBY 選項來備份交易記錄結尾。 若使用 NORECOVERY ，則備份完後主系統資料庫會處於 RESTORING 狀態，將無法再提供給使用者使用。 之後，您再將這結尾交易記錄備份套用到要取代的主要資料庫，以便向前復原這個資料庫。

若使用 STANDBY 選項，則主系統資料庫會處於 STANDBY 狀態，使用者僅可以唯讀使用資料庫。

--資料庫在執行完交易記錄備份後，狀態將處於 RESTORING ，無法再提供給使用者使用。
BACKUP LOG [TestDB] To [testdb_bk_dev1] 
    WITH NORECOVERY

檔案群組備份（File and filegroup Backups）

File and filegroup backups back up individual database files and filegroups rather than performing a full database backup. This method backs up very large databases. You must back up the transaction log when performing a file and filegroup backup. You cannot use this method if the Truncate Log On Checkpoint option is enabled.

只複制備份（Copy-only Backups）

備份時，若使用「僅複製備份」（(Copy-Only Backup)）選項，這不會影響正常的備份順序。 僅複製備份的建立與定期排程的傳統備份無關，它並不會影響資料庫的整體備份和還原程序。

SQL Server 2005 導入的僅複製備份適用於需要執行備份來達成特定用途的情況 (例如，在線上檔案還原之前備份記錄檔)。 通常，僅複製記錄備份用過一次後便會刪除。

備份裝置（backup device）

為了方便管理資料庫的備份資料，SQL Server 使用「備份裝置」物件，用來建立與實際儲存備份資料的磁碟或磁帶等設備的對應。

建立備份裝置

使用 SSMS

使用 TSQL

使用 sp_addumpdevice 將備份裝置加入至 SQL Server 的執行個體。

EXEC sp_addumpdevice 'disk', 'TestDB_BackupDevice', 'D:\Database\bak\TestDB.bak'

備份裝置優點

簡化備份程序

直接選個裝置，不用再指定檔案的存放位置。

容易查看備份內容

直接雙擊備份裝置，可以查詢此裝置中存放哪些資料，包括：備份類型、資料名稱，備份時間等。

備份壓縮（Backup Compression）

• MSDN：備份壓縮 (SQL Server)

備份壓縮會大幅增加 CPU 使用量，但對 I/O 次數或備份時間則相對減少許多。

備份壓縮使用時有以下幾點限制：

• 壓縮和未壓縮的備份無法在媒體集中並存。
• 舊版 SQL Server 無法讀取壓縮的備份。
• NTbackup 無法與壓縮的 SQL Server 備份共用磁帶。

備份壓縮的選項預設值是不啟用的，你可以在下圖中的地方變更。

也可以透過指令來變更壓縮選項的預設值。

EXEC sys.sp_configure [backup compression default], 1
GO
RECONFIGURE WITH OVERRIDE
GO

注意，它只是壓縮選項的預設值，當真正執行壓縮時，你可以明確的使用 WITH NO_COMPRESSION 或 WITH COMPRESSION 來指定是否使用壓縮。

資料庫檢查點（Database Checkpoints）

• MSDN：資料庫檢查點 (SQL Server)

Database Engine 會定期在每一個資料庫上發出檢查點 (Checkpoint)，用以將目前記憶體中已修改的頁面(Dirty Page)和交易記錄資訊從記憶體寫入磁碟上，同時也會記錄有關交易記錄的資訊。 Database Engine 會自動管理檢查點，通常每分鐘發生一次檢查點。

你也可以變更檢查點發生的頻率，如圖中的復原間隔(recovery interval)設定。 或者使用 ALTER DATABASE … TARGET_RECOVERY_TIME 指令設定。

媒體集、媒體家族與備份組

• MSDN：媒體集、媒體家族與備份組 (SQL Server)

媒體集（Media Sets）

• 所有用來進行備份的裝置統稱「媒體集」。
• 「媒體集」內的裝置都必須同一類型，例如可能是三個磁帶，或二個磁碟，不會同時包含磁帶與磁碟。
• 「媒體集」內的裝置數量是固定的，第一次備份使用幾個，往後就是幾個。除非使用 WITH FORMAT 重新定義。
• 壓縮和未壓縮的備份無法在「媒體集」中一起出現。(SQL Server 2008 Enterprise 和更新的版本才支援壓縮備份。)

範例：

1.使用二個備份裝置，進行備份作業，這二個備份裝置即為一個「媒體集」

2.若再加入一個備份裝置，會產生錯誤

3.你可以使用新的媒體集備份

媒體家族（Media Families）

在單一非鏡像裝置上或媒體集鏡像裝置組上所建立的備份，即構成一個「媒體家族」。 媒體集使用的備份裝置數量決定媒體集內的媒體家族數。 例如，如果媒體集使用兩個非鏡像的備份裝置，此媒體集即包含兩個媒體家族。

如上面範例中的媒體集即包含兩個媒體家族。

備份組（Backup Sets）

成功的備份作業會將一個「備份組」(備份內容)加入至媒體集。
這個備份組是根據備份所屬的媒體集加以描述。
如果備份媒體僅由一個媒體家族組成，則該家族就包含整個備份組。
如果備份媒體由多個媒體家族組成，則備份組會分散於其中。
在每個媒體上，備份組都會包含描述該備份組的標頭。

執行備份作業

備份作業共分成三種：完整備份、差異備份、交易記錄備份。 前二者是針對資料庫資料，後者是針對交易記錄。

在 SSMS 中，備份作業可以單獨執行，也可以設定成排程來執行。

BACKUP DATABASE 指令可用來執行整個資料庫備份，也可用來備份一個或多個檔案群組。 從 SQL Server 2012 SP1 開始，也支援備份至 Windows Azure Blob 儲存體服務。

BACKUP Log 指令則可以在「完整復原模式」或「大量記錄復原模式」下用來備份資料庫的交易記錄。

完整備份

WITH 選項

WITH 是 BACKUP DATABASE 重要的選項，可用來指定要搭配備份作業：

• INIT ：將備份覆寫到現有的備份組。（若沒這個項目，則使用附加）
• FORMAT ：將備份寫到新的媒體集，若該媒體集已存在則會被覆寫。（使用FORMAT，則不需要Init)
• COMPRESSION ：明確啟用備份壓縮。（若沒這個項目，則進行完整備份）
• DIFFERENTIAL：使用差異備份

下面範例使用二個備份裝置（稱為媒體集）來儲存，SQL Server 會運用平行處理來備份資料庫。

--完整備份 (覆寫)
Backup Database [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2] WITH Init

--完整備份 (附加)
Backup Database [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2]

--完整備份+鏡射備份 (覆寫)
Backup Database [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2]
    Mirror To [testdb_bk_dev3] ,[testdb_bk_dev4]
	WITH FORMAT, Init

差異備份

--差異備份 (附加)
BACKUP DATABASE [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2] WITH DIFFERENTIAL;

--差異備份 (覆寫)
BACKUP DATABASE [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2] WITH DIFFERENTIAL, Init;

交易記錄備份

「交易記錄備份」作業僅支「完整」或「大量記錄」復原模式。若資料庫的復原模式為「簡單」，則無法使用。

--交易記錄備份
BACKUP LOG [TestDB] 
	To [testdb_bk_dev1],[testdb_bk_dev2]

--備份交易記錄，並且建立備份副本
BACKUP LOG [TestDB] 
	To [testdb_bk_dev1]
	Mirror To [testdb_bk_dev3],[testdb_bk_dev4]
	WITH FORMAT

檢視備份記錄

可以使用 記錄檔檢視器(Application Event Log) 檢視備份的記錄。

也可以由 msdb.dbo.backupset 系統資料表查詢：

SELECT
DATABASE_NAME as '資料庫名稱',
CASE [type]   
WHEN 'D' THEN N'資料庫'  
WHEN 'I' THEN N'差異資料庫'  
WHEN 'L' THEN N'紀錄'  
WHEN 'F' THEN N'檔案或檔案群組'  
WHEN 'G' THEN N'差異檔案'  
WHEN 'P' THEN N'部分'  
WHEN 'Q' THEN N'差異部分'  
ELSE N'NULL'  
END as '備份類型', 
RECOVERY_MODEL as '還原模式',
DATABASE_BACKUP_LSN as '完整資料庫備份之LSN',
FIRST_LSN as '第一個LSN',
LAST_LSN as '下一個LSN',
DIFFERENTIAL_BASE_LSN as '差異備份的基底LSN',
backup_start_date as '備份開始時間', 
backup_finish_date as '備份完成時間',
backup_size 
FROM msdb.dbo.backupset 
WHERE DATABASE_NAME='TestDB2' 

在這個圖中，可以看到完整資料庫備份之LSN都是一樣的。而且每個交易記錄檔的LSN都是循序的。

其他備分作業

以上討論的內容都是針對整個資料庫或者交易記錄內容執行備份，另外還有以下幾項備份相關的作業。

備份「系統資料庫」（System Databases）

「系統資料庫」（System Database）是資料庫執行個體不可或缺的資料庫。 在每次重大更新之後，有幾個系統資料庫是一定要備份的，包括 msdb、master 和 model。

所以系統資料庫　都是使用「簡單」的還原模式，所以無須進行交易記錄備備。

備份「複寫資料庫」（Replicated Databases）

若備份作業備份的對象是複寫資料庫時，要記得同時備份複寫作業會使用到的系統資料庫。

• master, msdb, and publication databases at the publisher instance
• master, msdb, and distribution databases at the distributor instance
• master, msdb, and subscription databases at the subscriber instances

備份「鏡像資料庫」（Mirrored Databases）

若系統包含鏡像資料庫，它本身就像是一個備份資料庫了，無法對鏡像資料庫再設定備份。

至於如何設定「鏡像資料庫」，請看這篇。

備份「次要複本」 (AlwaysOn 可用性群組)

備份作業可能會對 I/O 和 CPU 造成相當大的壓力。 如果將備份卸載至已同步處理或正在同步處理的次要複本，可讓裝載主要複本的伺服器執行個體上的資源用於第 1 層工作負載（ tier-1 ）。

部分備份（Partial Backups）

結尾記錄備份（Tail-Log Backups）

當系統發生失敗，我們即將進行復原作業前，必須先做結尾記錄備份。

結尾記錄備份其實就是交易記錄備份，用來備份最後一次交易記錄備份後所發生的交易，以避免還原過程中，遺失了最新的資料。

不過，若沒有打算要復原到最新狀態，也就不一定要做結尾記錄備份。

• BACKUP (Transact-SQL)
• RESTORE (Transact-SQL)
• SQL Server 資料庫的備份與還原
• 備份概觀 (SQL Server)
• 完整資料庫備份 (SQL Server)
• 建立完整資料庫備份 (SQL Server)
• 復原模式 (SQL Server)