IT工程師數位筆記本

數據備份一直被認為數據庫的生命，也就是一個DBA所要掌握的主要技能之一，本篇就是介紹SQL Server備份原則，SQL Server數據庫分為數據文件和日志文件。為了使得數據庫能夠恢復一致點，備份不僅需要拷貝數據數據文件里的內容，還要拷貝日志文件里的內容。那么根據每次備份的目標不同，我們可以將備份分為數據備份和日志備份。

數據備份的范圍可以是完整的數據庫、部分數據庫、一組文件或文件組。所以根據備份下來的數據文件的范圍，又分為了完整數據庫備份、文件備份和部分備份。

完整數據庫備份

完整數據庫備份就是拷貝下數據庫里的所有信息，通過一個單個完整備份，就能將數據庫恢復到某個時間的狀態。但是由于數據庫備份是一個在線的操作。一個大的完整數據庫備份可能需要一個小時甚至更長的時間。數據庫在這段時間里還會發生變化。所以完整數據庫備份還要對部分事務日志進行備份，以便能夠恢復數據庫到一個事務一致的狀態。

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

文件備份

文件備份指的備份一個或多個文件或文件組中的所有數據。在完整恢復模式下，一整套完整文件備份加上跨所有文件備份的日志備份合起來等同于完整數據庫備份。使用文件備份能夠只還原損壞的文件，而不用還原數據庫其余的部分，從而可加速恢復速度。例如，如果數據庫由位于不同磁盤上的若干個文件組成，在其中一個磁盤發生故障時，只需要還原這個故障磁盤上的文件的備份，其它磁盤上的文件無須還原。這樣會縮短還原時間。

部分備份

部分備份是SQL Server2005中的新增功能。部分備份與完整數據庫備份類似，但是部分備份默認只包含數據庫可讀寫的部分，數據庫的只讀文件將不會備份。因為只讀部分是不會發生變動的。總是去備份它有點浪費。所以部分備份在希望備份不包括只讀文件組時非常有用。

部分備份可以說是數據庫部分和文件備份之間的一個中間類型。如果一個數據庫里沒有只讀文件，那么部分備份和數據庫備份就沒有什么差別。

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

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

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

對于完整數據庫備份、文件備份和部分備份這3種數據備份形式，SQL Server都能夠做完整備份和差異備份。所以，引出了一共6種數據備份模式：完整數據庫備份、完整文件備份和完整部分備份，差異數據庫備份、差異文件備份和差異部分備份。

數據備份集中精力數據文件的備份。對于日志文件，相應地有事務日志備份。每個日志備份都包括創建備份時處于活動狀態的部分事務日志，以及先前日志備份中未備份的所有日志記錄。不間斷的日志備份序列包含數據庫的備份（即連續不斷的）日志鏈。在完整恢復模式下（或者在大容量日志恢復模式下的某些時候），連續不斷的日志鏈讓您可以將數據庫還原到任意時間點。

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

1、僅復制完整備份

僅復制完整備份也備份整個數據庫內容。它和正常的完整備份的區別是，做完了以后差異備份基準不會改變，因此不影響差異備份序列。

2、僅復制日志備份

僅復制日志備份只備份當前日志文件里的現有內容，但是不會截斷日志，因此，下次在做正常日志備份的時候，這些內容還原被再次備份下來，從而不影響常規日志備份的序列。這種備份主要用在數據庫上已經有了一個備份計劃任務在運行，但是現在需要做一個日志備份，同時不影響到原有的備份序列。

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

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

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

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

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

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

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

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

1、SQL Server在運行的時候，對文件施加了排它鎖，通過一般的方法是不能直接拷貝文件的。除非通過一些備份軟件，否則只能停掉SQL Server服務，或者關閉數據庫，才能備份文件。

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

3、有些用戶只拷貝數據文件，不拷貝日志文件的做法，是非常不規范的。很容易導致數據庫不能正常恢復。從而丟失數據。

拷貝文件的方法也不是一定能用，筆者在做災難恢復的時候，如果數據庫不是很大，會先做一個數據庫備份，在做一個文件級的備份，以期雙保險。文件拷貝發生在SQL Server被成功關閉之后，或者sp_detach_db后，而且所有的文件都要做備份，包括日志文件。

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

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

1、數據庫最多能容忍多長時間的數據丟失？

2、準備投入多少人力物力來做數據庫備份與恢復策略？

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

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

1、備份越多，要管理的備份文件也越多，數據庫恢復時要恢復的文件也越多。需要建立一個合適的制度。

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

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

當您對您愿意投入的人力物力心中有數以后，就可以來決定采用什么也樣的備份策略了。

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

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

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

簡單恢復模式下，不能做日志備份。所以它只支持最簡單的備份和還原模式。很容易管理。不過如果沒有日志備份，就只能將數據庫恢復到最后一次備份的結尾。如果發生災難，數據最后一次備份之后做的修改將丟失。

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

并且在簡單恢復模式下，會自動截斷事務日志，以刪除不活動的虛擬日志文件。截斷通常發生在每個檢查點之后，但在某些情況下會延遲。

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

上圖顯示了這種備份計劃的工作損失風險。所以這個粗略只適合用于頻繁備份的小型數據庫里。

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

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

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

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

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

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

所以，在第一個完整數據庫備份完成，并且常規日志備份開始之后，潛在的工作丟失風險的時間，僅為數據庫損壞時點。到上次一次常規日志備份的那一段時間，因此，建議經常執行日志備份，以將工作丟失的風險限定在業務所要求所允許的范圍內。

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

上圖顯示了該中備份策略并且使用一系列例行日志備份來補充完整數據庫備份。使用事務日志備份可縮短潛在的工作丟失風險存在的時間，使該風險僅在最新日志備份之后存在。在第一個數據庫備份完成后，每天會做一個差異數據庫備份，而在工作時間進行若干日志備份。

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

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

三、文件或文件組備份

 完整文件備份指的是備份一個或多個文件或文件組中的所有數據。在完整恢復模式下，一整套完整文件備份和所有文件備份的日志備份合起來。等同于一個完整數據庫備份。使用文件備份能夠只還原損壞的文件，而不用還原數據庫的其余部分，從而加快恢復速度。例如，如果數據庫由位于不同磁盤上的若干文件組成，在其中一個磁盤發生故障時，只須還原故障磁盤上的文件。

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

相對于數據庫備份，文件備份具有如下優點：

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

2、與完整數據庫備份（對于超大型數據庫而言，變得難以管理）相比，文件備份增加了計劃和媒體處理的靈活性。文件或文件組備份的更高靈活性對于包含具有不同更新特征的數據的大型數據庫也很有用。

此種備份方法和完整數據庫備份相比，文件備份的主要缺點是管理比較復雜。如果某個損壞的文件未備份，那么媒體故障可能會導致無法恢復整個數據庫。因此，必須維護一組完整的文件備份，對于完整/大容量日志恢復模式，還必須維護一個或多個日志備份。這些日志備份至少涵蓋第一個完整文件備份和最后一個完整備份之間的時間間隔。

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

在完整恢復模式下，一整套完整文件與涵蓋第一個文件備份開始的所有文件備份的足夠日志備份起來等同于完整數據備份。

上圖顯示了文件備份的原理，如果可能最好執行完整數據庫備份并在第一個文件備份開始之前開始日志備份。上圖顯示了創建數據庫（在t0時間）之后立即執行完整數據庫備份（在t1時間）的策略。創建了第一個數據庫備份之后，便可開始執行事務日志備份。事務日志備份計劃按照設置間隔執行。文件備份以最合適數據庫業務要求的間隔執行。此圖顯示了4個文件組，每次備份其中的一個文件組。它們的備份順序（A、C、B、A）反映了數據庫的業務要求。

在完整恢復模式下，恢復一個文件組備份，不但需要恢復文件組備份本身，還需要依次恢復從上一次完整數據庫備份后，到恢復的目標時間點為止的所有日志備份。以確保該文件與數據庫的其余部分保持一致。所以要恢復的事務日志備份數量會很多。要避免這種情況，可以考慮使用差異文件備份。可是這樣會使整個備份計劃更加難于管理。這也是為什么文件備份不常使用的重要原因。但是在管理超大數據庫時，這可能是唯一的選擇。

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