title: MSSQL-最佳實踐-資料庫恢復模式與備份的關係

author: 風移

摘要

在SQL Server備份專題分享中，前三期我們分享了三種常見的資料庫備份、備份策略的制定以及如何查詢備份鏈。本期我們將分享資料庫的三種恢復模式與備份之間的關係，SQL Server的三種資料庫恢復模式包括：
簡單恢復模式(Simple)
完全恢復模式(Full)
大容量日誌恢復模式(Bulk-logged)

SQL Server三種恢復模式

簡單恢復模式(Simple)

簡單恢復模式下的資料庫事務日誌會伴隨著Checkpoint或者Backup操作而被清理，最大限度的保證事務日誌最小化。

工作原理

按照我個人的理解，簡單恢復模式（Simple）這個名字不足以很好的描述資料庫的工作原理，準確的說法應該是”Checkpoint with truncate log”。詳細的解釋就是：所有已經提交的事務，會伴隨著資料庫的Checkpoint或者Backup操作的完成而被清理掉，僅保留少許用於例項重啟時Recovery所需必要少量日誌。這樣做的好處是，資料庫的事務日誌非常的少，空間佔用小，節約儲存開銷，不需要專職的DBA去維護和備份資料庫日誌。但是壞處也是顯而易見的，比如：
無法實現資料庫的日誌備份
基於簡單恢復模式的資料庫無法實現任意時間點恢復（point-in-time recovery）
資料最多能夠恢復到上一次的備份檔案（可以是完全或者差異備份），無法恢復到最近可用狀態

適用場景

基於以上資料庫簡單恢復模式的工作原理和缺點，因此簡單恢復模式的適用場景就非常清楚了，包括：
資料庫儲存的是非關鍵資料（比如：日誌資訊等）
資料庫在任何時間，任何場景下都沒有任意時間點恢復的需求
在資料庫災難發生時，可以接受部分資料庫丟失
資料庫中資料變化頻率非常低
資料庫在可以預見的時間內沒有高可用（HA）需求（比如：Database Mirroring, AlwaysOn, Log Shipping等）

設定簡單恢復模式

在介紹完簡單恢復模式使用場景之後，讓我們來看看如何將資料庫修改為簡單恢復模式，以下兩種方法任選其一即可。
方法一，使用SSMS IDE介面操作
右鍵點選需要修改恢復模式的資料庫名 -> Properties -> Options -> 在右側Recovery model中選擇Simple -> OK

方法二，使用語句修改
如果你覺得使用SSMS IDE修改操作繁瑣，你也可以使用ALTER DATABASE語句來修改資料庫的恢復模式為Simple，以下語句是將AdventureWorks2008R2資料修改為簡單恢復模式。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY SIMPLE WITH NO_WAIT
GO複製程式碼

應用舉例

資料庫為簡單恢復模式的應用舉例如下圖所示：

注：以上圖片來自於網路：sqlbak.com/academy/sim…

10:00和22:00資料庫進行了完全備份（Full Backup）
16:00資料庫完成了差異備份（Differential Backup）
19:00一些重要的資料庫被誤刪除
在這種情況之下，我們最多能夠找回到16:00這個差異備份檔案中的資料，而16:00 - 22:00之間的資料將會丟失而無法找回。即我們最多能夠找回異常發生時間點的前一個備份檔案中的資料，找回方法是，先還原10:00這個Full Backup，然後還原16:00這個Differential Backup。還原資料庫的方法類似於如下語句：

USE [master]
GO

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak' WITH NORECOVERY, REPLACE

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH RECOVERY複製程式碼

通過以上方法，在簡單恢復模式下的資料庫，無法找回資料刪除時間點靠前的所有資料，最多能夠找回前一個有效備份的資料。

完全恢復模式(Full)

與SQL Server資料庫簡單恢復模式相反的一種模式叫著：完全恢復模式（Full），以下會對資料庫完全恢復的工作原理、使用場景、設定以及應用舉例四個方面來了解。

工作原理

相對於簡單恢復模式而言，完全恢復模式我們可以叫著“Checkpoint without truncate log”，換句話說，SQL Server資料庫引擎本身不會自己主動截斷事務日誌，也因此，完全恢復模式下的資料庫相對於簡單恢復模式的資料庫，事務日誌檔案漲的更快，大得更多。這些資料庫日誌檔案中包含了近期所有已經提交的事務，直到事務日誌備份發生且成功結束。所以，完全恢復模式下的資料庫：
允許資料庫日誌備份
可以實現任意時間點的恢復（point-in-time recovery）
可以恢復到災難發生時間點非常近的資料，最大限度保證資料不丟失

適用場景

基於以上對完全恢復模式的介紹，讓我們來看看完全恢復模式的適用場景，包括：
資料庫中儲存的是非常關鍵的業務資料（比如：訂單資訊、支付資訊等）
對資料安全有著非常強烈的需求，需要在任何時間，任何情況下找回最多的資料
在災難發生時，僅能接受極少資料的丟失
對資料庫的高可用（HA）要求極高（比如：Database Mirroring、Alwayson等有強需求）
對資料庫有任意時間點恢復（point-in-time recovery）的能力要求
需要資料庫能夠實現頁級別的還原能力
當然，完全恢復模式下的資料庫事務日誌檔案增長速度和漲幅相對簡單模式更大，所以，也需要DBA對資料庫事務日誌做定期維護，監控和備份管理。

設定完全恢復模式

將資料庫設定為完全恢復模式，同樣也有兩種方法。
方法一、使用SSMS IDE修改資料庫為完全恢復模式，同“設定簡單恢復模式”中方法一。
方法二、使用ALTER DATABASE語句將資料庫設定為完全恢復模式，如下語句。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO複製程式碼

應用舉例

對於完全恢復模式應用舉例，如下圖所示：

注：以上圖片來自網路 sqlbak.com/academy/ful…
對於該圖的場景做如下解釋：
10:00和22:00：對資料庫做了完全備份
16:00：對資料庫做了差異備份
12:00、14:00、18:00和20:00對資料庫做了事務日誌備份
19:00災難發生，資料庫中一些關鍵資料被誤刪除
那麼，接下來的問題就是，我們如何利用資料庫的備份資訊，將19:00誤刪除的資料找回？也就是將資料庫還原到18:59:59這個時間點的狀態。根據資料庫的所有備份資訊，我們可以按照如下思路找回被誤刪除的資料。
首先，我們需要還原10:00的完全備份檔案，並且狀態為norecovery；
其次，我們還原16:00的差異備份檔案，狀態也為norecovery；
然後，還原18:00的事務日誌備份檔案，狀態依然為norecovery；
最後，還原20:00的事務日誌備份檔案，需要特別注意的是這裡需要指定還原到的時間點（使用STOPAT關鍵字）為18:59:59，並且將狀態設為recovery帶上線。
將以上的文字描述找回資料的步驟，以程式碼的形式表達出來如下：

USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\18:00_Log.trn' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\20:00_Log.trn' WITH STOPAT = '2018-02-20 18:59:59', RECOVERY複製程式碼

通過以上步驟，在資料庫完全恢復模式下，藉助於備份資訊，我們可以成功找回被誤刪除的資料，而假如資料庫是工作在簡單模式下，則不能達到此效果。

大容量日誌恢復模式(Bulk-logged)

大容量日誌恢復模式是簡單恢復模式和完全恢復模式的結合體，是工作在完全恢復模式下對Bulk Imports操作的改良和適應。

工作原理

在SQL Server資料庫系統中，有一種快速匯入資料的方法叫Bulk Imports，比如：BCP、Bulk INSERT或者INSERT INTO ...SELECT。如果這些Bulk操作發生在完全恢復模式下的資料庫，將會產生大量的日誌資訊，對SQL Server效能影響較大。大容量日誌恢復模式的存在就是為了解決這個問題的，工作在Bulk-logged模式下的資料庫在Bulk Imports的時候，會記錄少量日誌，防止事務日誌的暴漲，以保證SQL Server效能的穩定和高效。可以簡單的將Bulk-logged模式理解為：在沒有Bulk Imports操作的時候，它與完全恢復模式等價，而當存在Bulk Imports操作的時候，它與簡單恢復模式等價。所以，處於Bulk-logged模式下的資料庫無法實現任意時間點恢復（point-in-time recovery），這個缺點與Simple模式類似。

適用場景

基於大容量日誌模式的原理解釋，它的適用場景包括：
Bulk Imports操作，比如：BCP、Bulk INSERT和INSERT INTO...SELECT
SELECT INTO操作
關於索引的一些操作：CREATE/DROP INDEX、ALTER INDEX REBUILD或者DBCC DBREINDEX
Bulk-logged模式最常用的使用場景是在做Bulk操作之前切換到Bulk-logged，在Bulk操作結束之後切換回Full模式

設定大容量日誌恢復模式

將資料庫設定為大容量日誌模式，還是有兩種方法。
方法一、使用SSMS IDE修改資料庫為大容量日誌恢復模式，同“設定簡單恢復模式”中方法一。
方法二、使用ALTER DATABASE語句將資料庫設定為大容量日誌恢復模式，如下語句。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY Bulk_LOGGED WITH NO_WAIT
GO複製程式碼

應用舉例

對於大容量日誌恢復模式應用例子，參見如下圖所示：

注：以上圖片來自網路 sqlbak.com/academy/Bul…
對於該圖的場景做如下解釋：
10:00：對資料庫做了完全備份
12:00、14:00、16:00和18:00：對資料庫做了事務日誌備份，其中16:00的日誌備份（黃顏色標示）是在修改為Bulk-logged的模式後進行的
20:00：對資料庫做了差異備份
14:30：將資料庫修改為Bulk-logged模式
15:00：災難發生，重要的資料被誤刪除
那麼，接下來的問題就是，我們如何利用這些備份檔案，儘可能的找回更多的資料，使丟失的資料最少。在原理部分，我們已經知道了，處於Bulk-logged模式下的資料庫，無法實現任意時間點的恢復，因此16:00這個事務日誌備份檔案就無法使用，即使嘗試使用也會報告如下錯誤：

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

RESTORE LOG is terminating abnormally

最終，我們能夠找回的資料最多能夠使用到14:00這個事務日誌備份。找回的步驟如下：
首先：我們需要還原10:00的完全備份檔案，並且狀態為norecovery；
其次：我們還原12:00的事務日誌備份檔案，狀態也為norecovery；
最後：還原14:00的檢視日誌備份，並且將狀態設為recovery帶上線。
將以上的文字描述步驟用程式碼來表達，如下：

USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\12:00_Log.trn' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\14:00_Log.trn' WITH RECOVERY複製程式碼

通過以上步驟，工作在Bulk-logged模式下的資料庫，無法實現任意時間恢復；因此一般該模式是使用在Bulk操作的過程中。

最後總結

本期分享了SQL Server三種恢復模式的工作原理、適用場景以及典型應用舉例，以此來探討資料庫恢復模式與備份之間的關係，從中我們很清楚的理解了資料庫恢復模式與備份的協同工作，來保證我們資料的安全性，以及在災難發生的時候，可以最大限度減少資料損失。

用雲棲社群APP，舒服~

原文連結