備份與還原觀念介紹與策略規畫

        資料庫的備份工作非常的重要，是每一位 DBA 首要工作，這也是許多的首次接任 DBA 面對的問題之一，反正不管是面對什麼不同的資料庫，反正就是先備份就對了，因為當問題發生時，只要還原你的備份即可，但是隨著資料庫越來越大的時候，備份的時間會越來越長，而且備份的過程中，因為有大量的 I/O ，所以反而造成資料庫的問題。

SQL Server主要分成二種檔案類型，一個是資料檔 (MDF or NDF)，而另一個是交易紀錄檔(LDF)，我常遇到的一個情況，那就是資料檔可能不到1G，但是交易紀錄檔已經成長到10G左右，最後造成磁碟空間不足，這個也是因為備份之後沒有截斷交易紀錄檔的關係，解決方法，我們稍後再來說明。

上述的問題主要都是在備份方式的選擇，所以現在我們來介紹如何規畫備份與還原計畫，而在介紹之前，我們先來說明一下資料庫的復原模式。復原模式簡單的說，就是當你的資料庫毀損時，你希望可以還原到特定的時間點藉以減少資料損失。

完整備份是最好的備份方案，但是相對的在每次有 Insert、Update、Delete的時候，都需要進行交易紀錄檔的抄寫，所以也會影響到效能的部份，如果你有需要一次 Import 大量的資料時，你可以將復原模式先切換成 [大量記錄] ，如果就可以加快 Import 的交易速度，通常復原模式的選擇，取決與資料異動的頻率，如果某些資料庫上的資料很少異動，其實你就可以選擇簡單模式即可。

復原 模式	描述	工作損失風險	復原至時間點
簡單	無記錄備份。 自動收回記錄空間，使空間需求保持在最低，實際消弭管理交易記錄空間的需求。	最近一次備份之後所做的變更並未受到保護。如果發生損毀事件，則必須重做這些變更。	只能復原至備份結束時。
完整	需要記錄備份。 不因損失或損毀資料檔而失去任何工作。可復原至任意時間點(例如，應用程式或使用者錯誤前)。	通常沒有。 如果記錄結尾損毀，必須重做最近一次記錄備份後的變更。	可以復原至特定時間點(假設您已完成至該時間點的備份)。
大量記錄	需要記錄備份。 完整復原模式的輔助，允許執行高效能的大量複製作業。針對大多數的大量作業使用最少記錄，以減少記錄空間的使用量。	如果記錄損毀，或在最近一次記錄備份後進行過大量記錄作業的話，必須重做最近一次備份後的變更。否則不會損失任何工作。	可復原至任何備份結束時。不支援時間點復原。

在來我們來介紹一下備份的種類，在SQL Server上總共提供五種備份方式(如下表)，最常用的是前三種，請參考下列說明。

備份類型	描述
完整	整個資料庫的完整備份。
差異	這個備份僅包含每個檔案自最近資料庫備份後修改過的資料範圍。
交易檔	每個記錄備份都會涵蓋建立備份當時正在進行中的交易記錄部分，而且也包含上一次記錄備份未備份到的所有記錄。
檔案 / 檔案群組	這是一或多個檔案或檔案群組中所有資料的完整備份。
差異檔案	這是一或多個檔案的備份，其中包含自從每個檔案最近完整備份後變更過的資料範圍。

再來另一個使用復原模式為完整時常遇的到問題，那就是交易檔(LDF)過大，常常造成磁碟空間不足的問題，因為交易檔本身只有當你進行交易檔備份的時候才會清除，其中的完整與差異都不會，為了證明這個情況，我作了以下的實驗。

交易檔的連結，本身是透過 LSN 進行連結，如下圖所示。

底下我總共作了13次的備份，分別為 差異 -> 紀錄 -> 完整 -> 差異 -> 紀錄 -> 差異 -> 紀錄 ->完整 -> 差異 -> 紀錄 -> 完整 -> 完整 -> 紀錄，說明如下：

備份測試：
1、交易檔 (LSN) 的啟始於你的第一個完整或差異備份。
2、從第二次的交易檔備份到第五次的交易檔備份，中間雖然有一次完整與差異備份，但是都只是包含交易檔的部份，但並沒有截斷交易檔，所以這證明了只有交易檔備份可以截斷，以免記錄檔 (LDF) 不斷的成長。



另一種方式，您也可以透過 DBCC Log(DBName) 的指令查詢目前記錄檔 (LDF) 的筆數，當您進行交易檔備份後，你就會發生回傳的筆數就會變少了。

最後我們來看一下備份與復原模式的相關，請參考下圖說明。

Recovery Model/ Backup	Complete	Differential	Transaction Log	File / Filegroup
Simple	Required	Allowed	Not Allowed	Not Allowed
Bulk-Logged	Required	Allowed	Required	Allowed
Full	Required	Allowed	Required	Allowed

備份策略範例：
我們通常透過完整 + 差異 + 紀錄來規畫資料庫的備份策略，但是怎樣的方法才是最好的，其實沒有一定的公式，取決於備份時的時間、資料可能遺失時間長度、還原時的時間等因素，下列我透過 MSDN 上範例提供給大家參考，希望大家可以透過這個範例學習後，調整出最適合資料庫的備份策略。

資料描述：

Database / Parameter	Sales/Customer
Size	3.5GB
Usage	Track customer orders and shipments
Activity Pattern	Most heavily used during weekdays. Customer orders are added during business hours. Reports are prepared at nights.
Disaster Recovery Requirements	High usage and visibility database. Critical to company operations. Require point-of-failure recovery. This system should be operational within 20-30 minutes if outage happens during working hours. No data loss is acceptable.

首先將資料庫的復原模式設定為 Full，如此可以讓系統保持最大還原到特定時間點的可能性，並且保持每天 PM 10:00進行完整備份、AM 11:00與 PM 4:00進行差異備份，每10分鐘進行一次交易備份。

在這樣的模式，最差的情況下，可以會損失約10分鐘的資料，但這種的情況非常的低，因為這是當你的LDF檔案完全不能讀的情況下才會發生，假設故障發生在星期一的AM 11:21 分，這時候在新的機器上，你就需要先還原星期天的完整備份，然後再還原星期一 AM 11:00的差異備份，最後再依序還原 AM 11:10、AM 11:20的交易備份，如果你想還原到特定的時間點，如 AM 11:16分，在進行最後一個交易備份時，配合 STOPAT 的備份參數即可。


參考連結：
SQL Server 2000 Backup and Restore
http://technet.microsoft.com/en-us/library/cc966495.aspx#EBAA
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/aa173660.aspx
Analyzing Availability and Recovery Requirements
http://msdn.microsoft.com/zh-tw/library/aa196617.aspx
Planning for Disaster Recovery
http://msdn.microsoft.com/zh-tw/library/aa196629.aspx
記錄序號和還原計畫
http://msdn.microsoft.com/zh-tw/library/ms190729.aspx
backupset
http://msdn.microsoft.com/en-us/library/ms186299.aspx
備份概觀 (SQL Server)
http://msdn.microsoft.com/zh-tw/library/ms175477.aspx
復原模式概觀
http://msdn.microsoft.com/zh-tw/library/ms189275.aspx
如何：還原到某個時間點 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms179451.aspx

關鍵字：SQL Server、Backup、Restore、Recovery、Recovery Model、備份、還原、復原模式