在SQL Server中，除了系統(tǒng)數(shù)據(jù)庫外，你創(chuàng)建的每一個數(shù)據(jù)庫都有三種可供選擇的恢復(fù)模型: Simple(簡單), full(完整), bulk-logged(批量日志)。 下面這條語句可以顯示出所有在線數(shù)據(jù)庫的恢復(fù)模型:
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

SQL Server 2005及以上版本也可以使用下面這條語句來查看:
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

如果想改變數(shù)據(jù)庫的恢復(fù)模型，可以使用下面SQL語句:

簡單恢復(fù)模型:ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
完整恢復(fù)模型:ALTER DATABASE AdventureWorks SET RECOVERY FULL
批量日志恢復(fù)模型:ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

在實(shí)際情況中，你應(yīng)該選擇使用哪種恢復(fù)模型呢？答案在于你能承受丟失多少數(shù)據(jù)。讓我們用下面這些圖表來說明這三種恢復(fù)模型之間的不同。下面這張圖是一個數(shù)據(jù)庫分別在9點(diǎn)和11點(diǎn)進(jìn)行了一次完整備份。

1.簡單恢復(fù)模型

假設(shè)硬件在10:45分時壞了。 如果數(shù)據(jù)庫使用的是簡單模型的話，那你將要丟失105分鐘的數(shù)據(jù)。因?yàn)槟憧梢曰謴?fù)的最近的時間點(diǎn)是9點(diǎn)，9點(diǎn)之后的數(shù)據(jù)將全部丟失。當(dāng)然你可以使用差異備份來分段運(yùn)行，如下圖:

像這樣使用差異性備份的話，你將丟失45分鐘的數(shù)據(jù)。現(xiàn)在，假設(shè)用戶在9:50刪除了一張很重要的表，你能恢復(fù)刪除點(diǎn)之前的數(shù)據(jù)嗎？答案當(dāng)然是No。因?yàn)椴町愋詡浞輧H僅包含數(shù)據(jù)頁的修改，它不能用于恢復(fù)一個指定的時間點(diǎn)。你不得不把數(shù)據(jù)庫恢復(fù)到9點(diǎn)的狀態(tài)，然后重做后面49分鐘的事情。

2.完整恢復(fù)模型

假如在9點(diǎn)和11點(diǎn)之間沒有進(jìn)行事務(wù)日志的備份，那么你將面臨和使用簡單恢復(fù)模型一樣的情況。另外，事務(wù)日志文件會很大，因?yàn)镾QL Server不會刪除已經(jīng)提交和已經(jīng)CheckPoint的事務(wù)，直到它們被備份。

假設(shè)每30分鐘備份一次事務(wù)日志：

假如硬件在10:45分時壞了，那你只會丟失15分鐘的數(shù)據(jù)。你可以使用9點(diǎn)的完整備份及直到10:30的事務(wù)日志來恢復(fù)。假如9:50分刪除了重要數(shù)據(jù)怎么辦呢？沒關(guān)系，你可以使用在10點(diǎn)備份的事務(wù)日志，把數(shù)據(jù)庫恢復(fù)到9:49分的狀態(tài)。 因?yàn)槟慊謴?fù)時無法直接跳過9:50那次誤刪除的操作日志而恢復(fù)9:50之后的數(shù)據(jù), 所以你還必須重做誤刪除之后的操作。不過，這已經(jīng)是不錯的選擇了。

注意：市場上有一些工具，可以使用事務(wù)日志來恢復(fù)用戶誤操作而丟失的數(shù)據(jù)，就是利用了上述原理

3.批量日志恢復(fù)模型

批量日志恢復(fù)模型被定義成一種最小化事務(wù)日志的完整恢復(fù)模型。例如select into就是一種最小化事務(wù)日志，假設(shè)這種事務(wù)發(fā)生在9:40分

這個事務(wù)將被最小化的記錄下來，這就意味著SQL Server僅僅記錄由于這個事務(wù)而產(chǎn)生的數(shù)據(jù)頁的變化，它不記錄每一條插入到數(shù)據(jù)表中的數(shù)據(jù)。假如9:50時一個重要的表數(shù)據(jù)被刪除了，那意味著什么呢？意味著你不能把數(shù)據(jù)庫再恢復(fù)到9:49分的狀態(tài)了，因?yàn)槭聞?wù)日志在10點(diǎn)時被備份并且不能恢復(fù)到一個指定的時間點(diǎn)上。你只能把數(shù)據(jù)庫恢復(fù)到9:30分的狀態(tài)。你要記住，無論在什么時候，只要事務(wù)日志備份包含一個或多個最小化日志事務(wù)，那你就不能再把備份還原到一個指定的時間點(diǎn)了。

既然如此，那人們?yōu)槭裁催€要使用批量日志恢復(fù)模型呢？一個最主要的原因就是性能。讓我們以select into以例，從一個結(jié)果集來創(chuàng)建一張大表。假如你使用完整備份模型，那這張表中的每一條插入的數(shù)據(jù)都被記錄下來，事務(wù)日志會消耗很多磁盤空間。假如你使用批量日志恢復(fù)模型，那么僅僅會記錄數(shù)據(jù)頁的修改細(xì)節(jié)以達(dá)到最好的性能。就像我們剛才描述的那樣，使用事務(wù)日志的好處就是可以恢復(fù)到某一個指定的恢復(fù)點(diǎn)，但是會大大影響性能。

下面的幾種操作都會最小化日志操作:
·批量導(dǎo)入操作(例如:INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT)
·select into 操作
·使用update來更新部分的大數(shù)據(jù)值數(shù)據(jù)類型。寫入語句是插入或是追加數(shù)據(jù)，注意當(dāng)被更新的數(shù)據(jù)存在時最小化日志不會被記錄
·假如數(shù)據(jù)庫恢復(fù)模型被設(shè)置為批量恢復(fù)或是簡單恢復(fù)，那么一些索引的DDL操作會產(chǎn)生最小化日志，無論這個操作是在線還是離線被執(zhí)行
·刪除索引新建堆時

注意：當(dāng)一個數(shù)據(jù)庫的數(shù)據(jù)文件不可用時(也許是硬件壞了)，假如媒介依舊在線可用，那么你依然可以備份事務(wù)日志文件。但是你需要確定backup log命令一定要加上no_truncate選項(xiàng)。這樣你就可以備份硬件毀壞前的事務(wù)日志了，這種方法常用來備份事務(wù)日志結(jié)尾。

然則，假如你的數(shù)據(jù)庫使用批量日志恢復(fù)模型且事務(wù)日志包括最小化日志事務(wù)，那么包括被修改過的頁的數(shù)據(jù)文件一定要可用，假如數(shù)據(jù)文件不可用了，也就意味著你將不能備份事務(wù)日志結(jié)尾。這也是使用批量日志恢復(fù)模型另一個需要考慮的地方。

總之，簡單恢復(fù)模型提供了數(shù)量最少的恢復(fù)選項(xiàng)和最簡單的管理模型。完整恢復(fù)模型在恢復(fù)數(shù)據(jù)庫時允許更復(fù)雜的情況存在。批量恢復(fù)模型簡化了一些復(fù)雜性，從而得到了更好的性能。大家可以從Books Online中得到這三種恢復(fù)更細(xì)致的對比。

本文翻譯自sqlbackuprestore，更多精彩內(nèi)容請瀏覽http://www.