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

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

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

簡(jiǎn)單恢復(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ù)。讓我們用下面這些圖表來(lái)說(shuō)明這三種恢復(fù)模型之間的不同。下面這張圖是一個(gè)數(shù)據(jù)庫(kù)分別在9點(diǎn)和11點(diǎn)進(jìn)行了一次完整備份。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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