SQL Server Database Backup和還原認識和總結

 　　首先從資料庫【復原模式】說起，因為資料庫如果復原模式設定不正確，會導致資料無法還原。

　　SQL Server 2012對Database Backup和還原與SQL Server 2008基本沒有太大變化，僅對還原時間點選擇UI和相關還原選項做了小改動，備份與還原核心沒有做變化，這隻是個人的掌握和瞭解，僅供參考，具體還請以官方文檔為依據。以下利用SQL Server 2012 SSMS(SQL Server Management Studio)做圖例示範。

　　SQL Server 2008資料庫復原模式分為三種：完整復原模式、大量記錄復原模式、簡單復原模式。

　　◆ 完整復原模式。為預設復原模式。它會完整記錄下操作資料庫的每一個步驟。使用完整復原模式可以將整個資料庫恢複到一個特定的時間點，這個時間點可以是最近一次可用的備份、一個特定的日期和時間或標記的事務。

　　◆ 大量記錄復原模式。它是對完整復原模式的補充。簡單地說就是要對大容量操作進行最小日誌記錄，節省記錄檔的空間(如匯入資料、批次更新、SELECT INTO等操作時)。比如一次在資料庫中插入數十萬條記錄時，在完整復原模式下每一個插入記錄的動作都會記錄在日誌中，使記錄檔變得非常大，在大量記錄復原模式下，只記錄必要的操作，不記錄所有日誌，這樣一來，可以大大提高資料庫的效能，但是由於日誌不完整，一旦出現問題，資料將可能無法恢複。因此，一般只有在需要進行大量資料操作時才將復原模式改為大量記錄復原模式，資料處理完畢之後，馬上將復原模式改回完整復原模式。

　　◆ 簡單復原模式。在該模式下，資料庫會自動把不活動的日誌刪除，因此簡化了備份的還原，但因為沒有交易記錄備份，所以不能恢複到失敗的時間點。通常，此模式只用於對資料庫資料安全要求不太高的資料庫。並且在該模式下，資料庫只能做完整和差異備份。

　　瞭解了以上資料庫還原模式後，一般就應該知道將自己的資料庫設定為何種模式了。備份資料庫，SQL Server 2008提供了四種備份方式：完整備份、差異備份、交易記錄備份、檔案和檔案組備份。

　　◆ 完整備份：備份整個資料庫的所有內容，包括交易記錄。該備份類型需要比較大的儲存空間來儲存備份檔案，備份時間也比較長，在還原資料時，也只要還原一個備份檔案。

　　◆ 差異備份：差異備份是完整備份的補充，只備份上次完整備份後更改的資料。相對於完整備份分來說，差異備份的資料量比完整資料備份小，備份的速度也比完整備份要快。因此，差異備份通常作為常用的備份方式。在還原資料時，要先還原前一次做的完整備份，然後還原最後一次所做的差異備份，這樣才能讓資料庫裡的資料恢複到與最後一次差異備份時的內容相同。

　　◆ 交易記錄備份：交易記錄備份只備份交易記錄裡的內容。交易記錄記錄了上一次完整備份或交易記錄備份後資料庫的所有變動過程。交易記錄記錄的是某一段時間內的資料庫變動情況，因此在進行交易記錄備份之前，必須要進行完整備份。與差異備份類似，交易記錄備份產生的檔案較小、佔用時間較短，但是在還原資料時，除了先要還原完整備份之外，還要依次還原每個交易記錄備份，而不是只還原最後一個交易記錄備份(這是與差異備份的區別)。

　　◆ 檔案和檔案組備份。如果在建立資料庫時，為資料庫建立了多個資料庫檔案或檔案組，可以使用該備份方式。使用檔案和檔案組備份方式可以只備份資料庫中的某些檔案，該備份方式在資料庫檔案非常龐大時十分有效，由於每次只備份一個或幾個檔案或檔案組，可以分多次來備份資料庫，避免大型Database Backup的時間過長。另外，由於檔案和檔案組備份只備份其中一個或多個資料檔案，當資料庫裡的某個或某些檔案損壞時，可能只還原損壞的檔案或檔案組備份。

　　完整備份可能比較好理解。例如，在2012年1月1日早上8點進行了完整備份，那麼將來在還原時，就可以恢複到2012年1月有1日早上8點時的資料庫狀態。

　　差異備份是備份完整備份後的資料變動情況。例如，在2012年1月1日早上8點進行了完整備份後，在1月2日和1月3日又分別進行了差異備份，那麼在1月2日的差異備份裡記錄的是從1月1日到1月2日這一段時間裡的資料變動情況，而在1月3日的差異備份裡記錄的是從1月1日到1月3日這一段時間裡的資料變動情況。因此，如果要還原到1月3日的狀態，只要先還原1月1日做的完整備份，再還原1月3日做的差異備份就可以了。

　　交易記錄備份是以交易記錄檔作為備份對象，相當於將資料庫裡的每一個操作都記錄下來了。假設在2012年1月1日早上8點進行了完整備份後，到1月2日早上8點為止，資料庫裡的資料變動了100次，如果此時做了差異備份，那麼差異備份記錄的是第100次資料變動後的資料庫狀態，而如果此時做了交易記錄備份，備份的將是這100次的資料變動情況。

　　再舉一個例子，例如在2012年1月1日早上8點進行了完整備份後，在1月2日和1月3日又進行了交易記錄備份，那麼在1月2日的交易記錄備份裡記錄的是從1月1日到1月2日這一段時間裡的資料變動情況，而在1月3日的交易記錄備份裡記錄的是從1月2日到1月3日這一段時間裡的資料變動情況。因此，如果要還原到1月3日的資料，需要先還原1月1日做的完整備份，再還原1月2日做的交易記錄備份，最後還要還原1月3日所做的交易記錄備份。

　　瞭解了以上Database Backup方式後，便可以針對自己的資料庫利用以上方式來備份資料庫了。合理備份資料庫需要考慮幾方面，首先是資料安全，其次是備份檔案大小，最後是做備份與還原能承受的時間範圍。

　　例如，如果資料庫裡每天變動的資料量很小，可以每周(周日)做一次完整備份，以後的每天(下班前)做一次交易記錄備份，那麼一旦資料庫發生問題，可以將資料恢複到前一天(下班時)的狀態。

　　當然，也可以在周日時做一次完整備份，周一到周六每天下班前做一次差異備份，這樣一旦資料庫發生問題，同樣可以將資料恢複到前一天下班時的狀態。只是一周的後幾天做差異備份時，備份的時間和備份的檔案都會跟著增加。但這也有一個好處，在資料損毀時，只要恢複完整備份的資料和前一天差異備份的資料即可，不需要去恢複每一天的交易記錄備份，恢複的時間會比較短。

　　如果資料庫裡的資料變動得比較頻繁，損失一個小時的資料都是十分嚴重的損失時，用上面的辦法備份資料就不可行了，此時可以交替使用三種備份方式來備份資料庫。

　　例如，每天下班時做一次完整備份，在兩次完整備份之間每隔八小時做一次差異備份，在兩次差異備份之間每隔一小時做一次交易記錄備份。如此一來，一旦資料損毀可以將資料恢複到最近一個小時以內的狀態，同時又能減少Database Backup資料的時間和備份資料檔案的大小。

　　在前面還提到過當資料庫檔案過大不易備份時，可以分別備份資料庫檔案或檔案組，將一個資料庫分多次備份。在現實操作中，還有一種情況可以使用到資料庫檔案的備份。例如在一個資料庫中，某些表裡的資料變動得很少，而某些表裡的資料卻經常改變，那麼可以考慮將這些資料表分別儲存在不同的檔案或檔案組裡，然後通過不同的備份頻率來備份這些檔案和檔案組。但使用檔案和檔案組來進行備份，還原資料時也要分多次才能將整個資料庫還原完畢，所以除非資料庫檔案大到備份困難，否則不要使用該備份方式。

　　針對以上備份方案，能看出資料還是不完整嗎?比如昨天夜間12點做了完整備份，每隔一小時做了一次交易記錄備份，最後一次交易記錄備份是今天中午12點，現在是今天中午12點10分，探索資料庫資料遭到丟失或破壞，可最後一次交易記錄備份是今天中午12點，如果我此時將資料庫恢複到12點，那麼12點後至12點10分前沒遭到破壞的操作資料將丟失(比如資料庫有三個表，一個表的資料遭到破壞，其它兩個表的資料被其它使用者變動)。此時就要用到【尾部記錄備份】，尾部記錄備份原理是從最後一次交易記錄備份的時間點開始，將之後的所有操作進行備份，還原時便可以找到12點後操作的正確資料了。

　　註：進行尾部記錄備份時，資料庫將強制停止資料庫，此時如果不停止資料庫，還有使用者繼續操作，尾部記錄備份將失去意義。SQL Server 2012如果你最後一次備份交易記錄後，對資料進行過改動，即發生過交易記錄(也就是當前記錄檔記錄的LSN(記錄序號)大於最後一次交易記錄備份裡記錄的最大LSN，SQL Server通過LSN來區分日誌的記錄)，並尚未對尾部記錄備份，它會提示並要求你必須先做尾部備份。

　　執行個體部分：

　　◆ 首先進行完整備份【MyTest.bak】，然後再基於此備份檔案進行兩次交易記錄備份，最後一次交易記錄備份時間為【2012-8-4 23:07】

　　圖1-1：備份檔案名為【MyTest.bak】，最後一次交易記錄備份時間為【2012-8-4 23:07】

　　圖1-2：選擇備份檔案“MyTest.bak”後，此時能看到“要還原的備份組”列表裡顯示有備份檔案，分別列出了完整備份檔案和兩次交易記錄備份。

　　以上備份檔案最後一次交易記錄備份時間為【2012-8-4 23:07】，那麼，您可以還原到自首次完整備份開始後，至最後一次交易記錄備份時間期間的任何一個時間點，這就充分驗證上上文講到的交易記錄備份方式(如果後期進行過尾部記錄備份，還原時在“要還原的備份組”列表裡的最後一行會列出尾部日誌記錄)。

　　如果交易記錄備份有兩次，分別是“交易記錄1”、“交易記錄2”，那麼在還原時去掉“交易記錄1”複選框，“交易記錄2”也會自動去掉，但只去掉“交易記錄2”是允許的，這也充分驗證了上文講到的：“在還原資料時，除了先要還原完整備份之外，還要依次還原每個交易記錄備份，而不是只還原最後一個交易記錄備份”，不比差異備份可以基於完整備份，只還原最後一次差異備份即可。

　　圖1-3：我【2012-08-04 23:36:53】插入一條資料，【2012-08-04 23:37:44】又做了一次交易記錄備份3，然後再將還原時間點到【2012-08-04 23:36:00】，還原後，此時間點後的資料就沒有了。

　　【注意：在還原時會提示資料庫正在使用，一般要斷開全部串連後，才能進行還原。我一般使用指令碼將資料離線再聯機，所有串連將會全部斷開】

　　alter database MyTest set offline with ROLLBACK IMMEDIATE--離線

　　alter database MyTest set online with ROLLBACK IMMEDIATE--聯機

　　◆ 如果你最後一次是做的完整備份，基於完整備份想還原到某個時間點是不允許的。

　　圖2-1：備份檔案名為【MyTestA.bak】，備份時間為【2012-8-4 22:33】

　　圖2-2：先選擇最後一次完整備份檔案MyTestA，此時能看到“要還原的備份組”列表裡顯示有備份檔案，然後再點“時間軸”按鈕進入“備份時間軸”介面，將最後一次完整備份時間由“22:33:41”改為它之前任何一個時間點，如改為“22:32:41”，然後再確定，會看到圖2-3的效果。

　　圖2-3：只要基於完整備份再還原到某個時間點是不允許的，因為完整備份後沒做過任何差異備份或交易記錄備份。此時能看到“要還原的備份組”列表裡顯示為空白，無法進行還原。