《SQL Server企業級平台管理實踐》讀書筆記——關於SQL Server資料庫的備份方式

標籤：style   blog   http   io   color   使用   sp   strong   檔案   

資料備份一直被認為資料庫的生命，也就是一個DBA所要掌握的主要技能之一，本篇就是介紹SQL Server備份原則，SQL Server資料庫分為資料檔案和記錄檔。為了使得資料庫能夠恢複一致點，備份不僅需要拷貝資料資料檔案裡的內容，還要拷貝記錄檔裡的內容。那麼根據每次備份的目標不同，我們可以將備份分為資料備份和記錄備份。

資料備份的範圍可以是完整的資料庫、部分資料庫、一組檔案或檔案組。所以根據備份下來的資料檔案的範圍，又分為了完整Database Backup、檔案備份和部份備份。

完整Database Backup

完整Database Backup就是拷貝下資料庫裡的所有資訊，通過一個單個完整備份，就能將資料庫恢複到某個時間的狀態。但是由於Database Backup是一個線上的操作。一個大的完整Database Backup可能需要一個小時甚至更長的時間。資料庫在這段時間裡還會發生變化。所以完整Database Backup還要對部分交易記錄進行備份，以便能夠恢複資料庫到一個事務一致的狀態。

完整Database Backup便於使用。它包含資料庫中的所有資料。對於可以快速備份的小資料而言，最佳方法就是使用完整Database Backup。但是，隨著資料庫的不斷增大，完整備份需要花費更多時間才能完整，並且需要更多的儲存空間。僅做完整備份可能不能滿足使用者需求。

檔案備份

檔案備份指的備份一個或多個檔案或檔案組中的所有資料。在完整復原模式下，一整套完整檔案備份加上跨所有檔案備份的記錄備份合起來等同於完整Database Backup。使用檔案備份能夠只還原損壞的檔案，而不用還原資料庫其餘的部分，從而可加速恢複速度。例如，如果資料庫由位於不同磁碟上的若干個檔案組成，在其中一個磁碟發生故障時，只需要還原這個故障磁碟上的檔案的備份，其它磁碟上的檔案無須還原。這樣會縮短還原時間。

部份備份

部份備份是SQL Server2005中的新增功能。部份備份與完整Database Backup類似，但是部份備份預設只包含資料庫可讀寫的部分，資料庫的唯讀檔案將不會備份。因為唯讀部分是不會發生變動的。總是去備份它有點浪費。所以部份備份在希望備份不包括唯讀檔案組時非常有用。

部份備份可以說是資料庫部分和檔案備份之間的一個中間類型。如果一個資料庫裡沒有唯讀檔案，那麼部份備份和Database Backup就沒有什麼差別。

資料庫檔案常常是非常巨大的。在流行資料集中的趨勢下，庫容上TB的資料庫現在已經屢見不鮮。對於這樣的一個資料庫，做Database Backup，哪怕是檔案備份都是一個非常昂貴的事情，可能不是每天能去做的。再這樣的背景之下就出現了：差異備份。

從是否拷貝所有的資料來分，資料備份有可以分為完整備份和差異備份。

差異備份基於差異，備份要求資料庫之前做過一次完整備份。差異備份僅捕獲自該次完整備份後發生更改的資料。這個完整備份被稱為差異備份的”基準“。差異備份僅包括差異基底後更改的資料。差異備份比差異基底更小且更快，便於執行頻繁備份，從而降低了資料丟失的風險。

對於完整Database Backup、檔案備份和部份備份這3種資料備份形式，SQL Server都能夠做完整備份和差異備份。所以，引出了一共6種資料備份模式：完整Database Backup、完整檔案備份和完整部份備份，差異Database Backup、差異檔案備份和差異部份備份。

資料備份集中精力資料檔案的備份。對於記錄檔，相應地有交易記錄備份。每個記錄備份都包括建立備份時處於活動狀態的部分交易記錄，以及先前記錄備份中未備份的所有日誌記錄。不間斷的記錄備份序列包含資料庫的備份（即連續不斷的）日誌鏈。在完整復原模式下（或者在大量記錄復原模式下的某些時候），連續不斷的日誌鏈讓您可以將資料庫還原到任意時間點。

SQL Server2005以後，還增添了一類新的備份模式，即僅複本備份。”僅複本備份“是獨立於常規SQL備份序列的SQL Server備份。通常，進行備份會更改資料庫並影響其後備份的還原序列。但是，有時在不影響資料庫全部備份與還原過程的情況下，為了特殊目的而進行的備份還是有用的。為此，SQL Server2005引入了下列兩種僅複本備份：

1、僅複製完整備份

僅複製完整備份也備份整個資料庫內容。它和正常的完整備份的區別是，做完了以後差異備份基準不會改變，因此不影響差異備份序列。

2、僅複製記錄備份

僅複製記錄備份只備份當前記錄檔裡的現有內容，但是不會截斷日誌，因此，下次在做正常記錄備份的時候，這些內容還原被再次備份下來，從而不影響常規記錄備份的序列。這種備份主要用在資料庫上已經有了一個備份計劃任務在運行，但是現在需要做一個記錄備份，同時不影響到原有的備份序列。

以上兩種方法SSMS不支援圖形化操作，只需要在備份語句後面加上copy only選項

現在總結SQL Server的11種主要備份方法

分級	資料備份			記錄備份
資料庫級	完整Database Backup	僅複製完整Database Backup	差異Database Backup	（一般） 記錄備份	僅複製記錄備份
檔案級	完整檔案備份	僅複製完整檔案備份	差異檔案備份
部分	完整部份備份	僅複製完整部份備份	差異部份備份

備份的方式很多種，其實我們經常用的就幾種重要的方式。

首先，僅複本備份這類方法的出現，是為了方式將要做的備份破壞現有的備份策略而生的，例如，對於一個已經建立了嚴格備份規則（例如 Log Shipping）的資料庫，現在需要做一個記錄備份到另一個檔案夾裡。普通的記錄備份會破壞現有備份檔案系統所維護的日誌鏈。僅複本備份就不會被破壞。所有這種方法僅僅在偶爾的特殊情況下去使用。不在指定備份策略的一開始考慮。

其次，在現實中，很少有資料庫專門維護一個唯讀檔案或檔案集。（這種方法維護成本較高，只會在非常巨大的資料庫上才能體現出優勢。）所以部份備份也是很少用到的。

這樣上面的備份方式就可以簡化成幾個比較傳統，也是最常用的備份方式

分級	資料備份		記錄備份
資料庫級	完整Database Backup	差異Database Backup	（一般） 記錄備份
檔案級	完整檔案備份	差異檔案備份

 當然除此之外，還存在一種暴力的備份方式那就是直接拷貝資料庫檔案，然後用檔案附加（Attach）的方式備份和恢複資料庫，這種方式在應對案例癱瘓掉的時候，萬般無奈之極是可以嘗試採取的，但是這種方式不作為標準的方式推薦。

不推薦的原因有以下幾個：

1、SQL Server在啟動並執行時候，對檔案施加了排它鎖，通過一般的方法是不能直接拷貝檔案的。除非通過一些備份軟體，否則只能停掉SQL Server服務，或者關閉資料庫，才能備份檔案。

2、SQL Server在理論上，只保證通過運行sp_detach_db語句得到資料庫檔案，才一定能被成功附加上。如果使用者是通過暫停SQL Server服務或其它方法得到檔案，SQL Server不能保證就一定能附加上。

3、有些使用者只拷貝資料檔案，不拷貝記錄檔的做法，是非常不規範的。很容易導致資料庫不能正常恢複。從而遺失資料。

 

拷貝檔案的方法也不是一定能用，筆者在做災難恢複的時候，如果資料庫不是很大，會先做一個Database Backup，在做一個檔案級的備份，以期雙保險。檔案拷貝發生在SQL Server被成功關閉之後，或者sp_detach_db後，而且所有的檔案都要做備份，包括記錄檔。

如何選擇備份策略和復原模式

SQL Server提供了足夠多的技術來做各種各樣的Database Backup。作為一個資料庫管理員，應該選擇何種備份方式，需要根據兩個問題來抉擇：

1、資料庫最多能容忍多長時間的資料丟失？

2、準備投入多少人力物力來做Database Backup與恢複策略？

其實是這樣，要想獲得最好的效果，就需要越多的投入。Database Backup策略尤其這樣。不考慮鏡像技術（包括SQL Server自己的資料庫鏡像和物理磁碟級鏡像），SQL Server不可能時時刻刻的做Database Backup，每次備份之間總要有一定的時間間隔。而這個時間間隔之間的資料變化在下一次備份之前，是沒有保護的。所以講到底，資料丟失的最大時間段，就是這兩次備份之間的時間間隔。利用備份資料恢複機制保護資料，是不可能保證資料一點都不丟失的。如果使用者提出來要求不能有任何資料丟失，則必須和使用者溝通，讓他們瞭解這樣的要求僅使用資料備份技術實現是不現實的，需要做更大的投入，引入鏡像技術。

既然資料丟失的最大時間段，就要兩次備份之間的時間間隔，也就是說備份做的越多，資料丟失量就越少。可是，做備份越頻繁，需要投入的也就越多。涉及的因素也就越多：

1、備份越多，要管理的備份檔案也越多，資料庫恢複時要恢複的檔案也越多。需要建立一個合適的制度。

2、備份雖然會阻塞資料的正常操作，但是會產生一系列的磁碟讀寫。如果伺服器本身的IO就比較頻繁，備份動作會進一步影響資料庫的效能。需要增強伺服器的硬碟的讀寫處理能力，才能避免這種問題的發生。

3、備份難免會因為種種因素失敗。備份越勤，遇到失敗的幾率就越多。管理員要及時處理錯誤，將備份任務恢複常態。這對管理員的要求也比較高。

當您對您願意投入的人力物力心中有數以後，就可以來決定採用什麼也樣的備份策略了。

使用記錄備份，可以將資料庫恢複到故障或特定的時間點。所以記錄備份在備份策略中扮演者很重要的角色。但是記錄備份只能在完整復原模式和有些大量記錄復原模式的資料庫上進行。

指定備份策略，首先要決定是否需要做記錄備份。如果需要做記錄備份，資料庫復原模式就要選成完整模式。（大容量復原模式不能保證記錄備份成功，所以一般不推薦在生產環境下使用。）如果不做記錄備份，資料庫模式就要設定成簡單。否則會遇到記錄檔無限增長問題。

一、簡單復原模式下的備份

簡單復原模式下，不能做記錄備份。所以它只支援最簡單的備份與還原模式。很容易管理。不過如果沒有記錄備份，就只能將資料庫恢複到最後一次備份的結尾。如果發生災難，資料最後一次備份之後做的修改將丟失。

顯示了簡單復原模式下最簡單的備份與還原策略，此策略僅使用包含了資料庫中所有資料的完整Database Backup。存在五個完整Database Backup，但是只需要還原最近的備份（t5時點執行的備份），還原此備份會將資料庫恢複到t5時點，由於t6框表示的所有後續更新都將丟失。

並且在簡單復原模式下，會自動截斷交易記錄，以刪除不活動的虛擬記錄檔。截斷通常發生在每個檢查點之後，但在某些情況下會延遲。

在簡單復原模式下，工作損失風險會隨時間增長而增加，直到進行下一個完整備份或者差異備份為止。因此，建議安排足夠的頻率，以避免遺失大量的資料。同時，頻率也不能太高而讓備份變得難以管理。

顯示了這種備份計劃的工作損失風險。所以這個粗略只適合用於頻繁備份的小型資料庫裡。

為了降低風險，SQL Server又引入了差異備份。

顯示了使用差異Database Backup補充資料庫完整備份來減輕工作損失風險的一種備份策略。在第一次Database Backup之後，連續建立了3此差異備份。第3個差異備份後，進行資料庫完整備份，建立新的差異基底。因為差異備份的開銷一般都比完整備份低，所以能夠比較經常的運行。這樣的備份策略可以使用在資料量稍大，能夠容忍較長時間丟失的資料庫上。

以上兩種備份策略的優勢，是不管是備份還是恢複，管理起來都比較簡單。但是不管是資料庫完整備份，還是差異備份，都不能以比較頻繁的頻率進行，一般都只能在晚間進行。如果資料庫比較龐大，或者不允許長時間的資料丟失，這樣的備份策略是不能滿足要求的。必須引入記錄備份。

二、完整復原模式下的備份

如果資料庫是完整復原模式，就可以使用記錄備份。由於記錄備份只拷貝上次記錄備份以來的所有日誌記錄，所以開銷會比Database Backup小很多。可以定義以一種很頻繁的頻率（5分鐘甚至更短）來做備份，以達到在最大限度內，防止出現故障遺失資料的目的。使用記錄備份的優點是允許您將資料庫還原到記錄備份的任何點（“時間點復原”）。假定可以在發生嚴重故障後備份活動紀錄，則可能資料庫一直還原到沒有發生資料丟失的故障點處。使用記錄備份的缺點是它們的數量很多，而且恢複備份時，需要嚴格按照備份產生的順序依次恢複。中間不能有任何備份缺失或跳躍。所以記錄備份做的越多，還原時間就越多。管理複雜性也越高。

顯示了完整復原模式下最簡單的備份策略。中已經完成了備份Db_1以及兩個例行記錄備份Log_1和Log_2。在Log_2記錄備份後的某個時間，資料庫出現故障。在還原這3個備份前，資料庫管理員必須備份活動紀錄（日誌尾部）。然後還原Db_1、Log_1和Log_2，並且不恢複資料庫。接著，資料庫管理員還原並恢複尾（Tail）記錄備份。這一步將可以把資料庫恢複到故障點。從而恢複所有資料。如果尾日誌能夠成功的備份和恢複。這次災難可能甚至不會帶來任何資料丟失。如果遭難毀壞的是記錄檔。使得尾日誌不能成功備份和恢複，那這次災難造成的資料丟失就是從Log_2以後所有的修改。

所以，在第一個完整Database Backup完成，並且常規記錄備份開始之後，潛在的工作丟失風險的時間，僅為資料庫損壞時點。到上次一次常規記錄備份的那一段時間，因此，建議經常執行記錄備份，以將工作丟失的風險限定在業務所要求所允許的範圍內。

出現故障後，可以嘗試備份“日誌尾部”（尚未備份的日誌）。如果尾部記錄備份成功，則可以通過將資料庫還原到故障點來避免任何工作丟失。所以這種備份計劃的優點也是很明顯的。

顯示了該中備份策略並且使用一系列例行記錄備份來補充完整Database Backup。使用交易記錄備份可縮短潛在的工作丟失風險存在的時間，使該風險僅在最新記錄備份之後存在。在第一個Database Backup完成後，每天會做一個差異Database Backup，而在工作時間進行若干記錄備份。

中第一個Database Backup建立之前，資料庫存在的潛在的工作丟失風險（從時間t0到時間t1）。該備份建立之後，例行記錄備份將工作丟失的風險降低為自丟失自最近記錄備份之後所做的更改（在此圖中，最近備份的時間為t14）。如果發生故障，則資料庫管理員應該立即嘗試備份的活動紀錄（日誌尾部）。如果此“尾記錄備份”成功。則資料庫可以還原到故障點。

但是，上述備份計劃存在一大缺陷，就是災難發生後需要恢複的記錄檔數目太多。假設每個小時做一次記錄備份，每周做一次Database Backup，如果災難在周五發生，就不得不恢複上百個記錄備份。這個工作量和所要花的時間是很大的。所以為了最大程度縮短還原時間，可以對相同資料庫進行一系列差異備份做補充。

三、檔案或檔案組備份

 完整檔案備份指的是備份一個或多個檔案或檔案組中的所有資料。在完整復原模式下，一整套完整檔案備份和所有檔案備份的記錄備份合起來。等同於一個完整Database Backup。使用檔案備份能夠只還原損壞的檔案，而不用還原資料庫的其餘部分，從而加快恢複速度。例如，如果資料庫由位於不同磁碟上的若干檔案組成，在其中一個磁碟發生故障時，只須還原故障磁碟上的檔案。

在SQL Server7.0和SQL Server2000中，檔案備份和差異檔案備份不包含日誌記錄。必須顯式會恢複記錄備份才能恢複其資料。因此，在這兩個版本中。只能將檔案備份與完整復原模式和大量記錄復原模式結合使用。在SQL Server 2005以後，檔案備份在預設情況下包含足夠的日誌記錄，可以將檔案前滾至備份操作的末尾。（但是在簡單復原模式下，必須一起備份所有讀/寫檔案，而不是逐個指定每個讀/寫檔案或檔案組。）

相對於Database Backup，檔案備份具有如下優點：

1、能夠更快的從隔離的媒體故障中恢複。可以迅速還原損壞的檔案。

2、與完整Database Backup（對於超大型資料庫而言，變得難以管理）相比，檔案備份增加了計劃和ApsaraVideo for Media Processing的靈活性。檔案或檔案組備份的更高靈活性對於包含具有不同更新特徵的資料的大型資料庫也很有用。

此種備份方法和完整Database Backup相比，檔案備份的主要缺點是管理比較複雜。如果某個損壞的檔案未備份，那麼媒體故障可能會導致無法恢複整個資料庫。因此，必須維護一組完整的檔案備份，對於完整/大量記錄復原模式，還必須維護一個或多個記錄備份。這些記錄備份至少涵蓋第一個完整檔案備份和最後一個完整備份之間的時間間隔。

維護和跟蹤這些完整備份是一種耗時的任務，所需空間可能會超過完整Database Backup的所需空間。所以這種備份策略在實際應用中應用的還是比較少的。而且在現在來講儲存已經變得很便利，但是這種方法在管理超大資料庫時，才能發揮出其不可代替的優勢。

在完整復原模式下，一整套完整檔案與涵蓋第一個檔案備份開始的所有檔案備份的足夠記錄備份起來等同於完整資料備份。

顯示了檔案備份的原理，如果可能最好執行完整Database Backup並在第一個檔案備份開始之前開始記錄備份。顯示了建立資料庫（在t0時間）之後立即執行完整Database Backup（在t1時間）的策略。建立了第一個Database Backup之後，便可開始執行交易記錄備份。交易記錄備份計劃按照設定間隔執行。檔案備份以最合適資料庫業務要求的間隔執行。此圖顯示了4個檔案組，每次備份其中的一個檔案組。它們的備份順序（A、C、B、A）反映了資料庫的業務要求。

在完整復原模式下，恢複一個檔案組備份，不但需要恢複檔案組備份本身，還需要依次恢複從上一次完整Database Backup後，到恢複的目標時間點為止的所有記錄備份。以確保該檔案與資料庫的其餘部分保持一致。所以要恢複的交易記錄備份數量會很多。要避免這種情況，可以考慮使用差異檔案備份。可是這樣會使整個備份計劃更加難於管理。這也是為什麼檔案備份不常使用的重要原因。但是在管理超大資料庫時，這可能是唯一的選擇。

不同的的庫設定不同的備份方案，可以自己自行選擇。

《SQL Server企業級平台管理實踐》讀書筆記——關於SQL Server資料庫的備份方式