在SQL Server中，除了系統數據庫外，你創(chuàng )建的每一個(gè)數據庫都有三種可供選擇的恢復模型: Simple(簡(jiǎn)單), full(完整), bulk-logged(批量日志)。 下面這條語(yǔ)句可以顯示出所有在線(xiàn)數據庫的恢復模型:
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

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

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

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

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

假設硬件在10:45分時(shí)壞了。 如果數據庫使用的是簡(jiǎn)單模型的話(huà)，那你將要丟失105分鐘的數據。因為你可以恢復的最近的時(shí)間點(diǎn)是9點(diǎn)，9點(diǎn)之后的數據將全部丟失。當然你可以使用差異備份來(lái)分段運行，如下圖:

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

2.完整恢復模型

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

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

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

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

3.批量日志恢復模型

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

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

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

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

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

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

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

本文翻譯自sqlbackuprestore，更多精彩內容請瀏覽http://www.sqlbackuprestore.com