第 7 章

1. 第 7 章 備份資料庫

2. 7-1 備份工作的規劃 • 電腦軟硬體的穩定性仍未達多數人能滿意的水準 • 就算使用的是具有容錯能力的RAID 磁碟陣列, 也是難以保証資料庫是百分之百的安全

3. 備份方式 • SQL Server 內建數種不同的備份方式, 分別說明如下： • 資料庫備份 • 差異式 (Differential) 備份 • 交易記錄 (Transaction Log) 備份 • 檔案及檔案群組備份

4. 資料庫備份 • 也就是備份整個資料庫的內容 • 好處是在還原資料庫時, 也只需將整個資料庫從一份資料庫備份還原到 SQL Server 就可以了。

5. 差異式 (Differential) 備份 • 只備份從上一次執行完整資料庫備份後有更動過的資料 • 所需的備份時間和儲存空間會比資料庫備份少很多, 所以適合做為經常性的備份方式。 • 在還原資料庫時, 要先用第一次做的完整資料庫備份來還原, 然後再用最近一次所做的差異式備份還原到 SQL Server, 就可讓資料庫的內容回復到最近一次差異式備份時的同樣內容。

6. 交易記錄 (Transaction Log) 備份 • 只備份交易記錄檔的內容 • 記錄前一次資料庫備份或交易記錄備份之後, 對資料庫所做的異動過程, • 在做交易記錄備份之前, 一定需做過一次完整的資料庫備份才行。 • 交易記錄備份所需的時間和儲存空間應該不多 • 還原時, 除了要先將資料庫備份還原外, 還需再依序還原各個交易記錄備份中的內容。所以還原所需時間較長

7. 檔案及檔案群組備份 • 資料庫的內容分散存於多個檔案或檔案群組, 而且資料庫已非常龐大, 大到進行一次完整的資料庫備份會有時間和儲存空間上的問題,就可使用這種方式來備份資料庫中部分檔案或檔案群組。 • 由於每次只備份部分的檔案或檔案群組, 因此需做數次不同的備份才能完成整個資料庫的備份, 但資料庫大到不方便做完整備份時也只好如此。 • 當損毀的資料只是資料庫中的某個檔案或檔案群組時, 也只要還原毀損的檔案或檔案群組備份就可以了

8. 差異式備份和交易記錄備份間的差異 • 差異式備份是記錄前一次資料庫備份之後, 資料庫有改變的內容； • 交易記錄備份則是備份交易記錄檔, 也是備份資料庫改變的操作過程

9. 備份規劃 • 有多少時間以及哪些時段可進行備份? • 資料庫變動的頻率如何? • 當系統出問題時, 使用者願等待多久的時間讓資料還原完畢? • 除了這些基本的問題外, 由此會再衍生出其它相關的要點： • 應採用何種備份方式, 或做什麼樣的搭配。 • 要使用哪種儲存媒體 • 由誰執行和管理備份工作 • 各資料庫的備份週期

10. 資料庫備份配合交易記錄備份 • 對一般的組織而言, 平時在用的資料庫可能不小, 而每天會異動的部分可能只佔整個資料量的一小部分而已, • 可考慮定期做一次完整的資料庫備份, 例如每週或每個月, 然後以較短的週期 (例如每天或每兩天等) 做一次交易記錄的備份。 • 平時的備份工作負擔就不會太重 • 就算發生意外需要將備份資料還原時, 也可望能用最近一次的資料庫備份, 再加上後來幾天的交易記錄備份, 將資料還原到最近期的狀態。

11. 資料庫備份配合交易記錄備份 • 例如每週末進行一次資料庫備份, 然後每天下班時進行交易記錄備份, 這樣不管哪一天伺服器出問題, 都能將資料還原到前一天下班時的狀態, 使資料的損失降到最低。 • 如還需要更高層度的資料回復能力, 例如在半天的時間內, 資料庫異動的內容就很多, 此時可考慮每天進行兩次或更多次的備份, 例如每天中午和晚上各做一次交易記錄備份, 這樣一來, 早上伺服器出問題可還原到前一天下班時的狀態, 下午出問題則可回復到中午的狀態。

12. 資料庫備份配合差異式備份 • 優點 • 若回復的時間離上次資料庫被份的時間比較近，則速度很快 • 缺點 • 在較後面的日子時, 每次做差異備份所需的時間和儲存空間也會跟著增加。

13. 三種備份方式綜合使用 • 由於交易記錄備份和差異式備份各有其優缺點, 因此我們可以將兩種方法結合起來使用。例如在資料庫異動頻繁的情況下, 可能損失一、兩小時變動的資料就是個很大的損失了, 此時若是仍只每天進行一次備份, 很可能就會造成在下午需還原, 但只能讓資料庫回到前一天下班時的狀態, 這對組織可能會造成很大的傷害。

14. 三種備份方式綜合使用 • 每週做完整資料庫備份 • 每天做差異式備份的情況下 • 每天的工作時間中插入交易記錄備份

15. 三種備份方式綜合使用 • 例如當週四下午資料庫發生問題需要做還原時, 只需先用最近一次的資料庫備份和差異式備份, 將資料庫回復到前一天的狀態, 再將從早上所做的各次交易記錄備份一一還原起來就可以了。若只是採用前面介紹的每日只做一次備份的方案, 則只能將資料庫回復到前一天的狀態而已。

16. 使用檔案和檔案群組備份 • 前面提過, 當資料庫過大而有不易備份的問題時, 可將資料庫分開存於不同的檔案和檔案群組, 然後以檔案和檔案群組的備份方式將整個資料庫分數次備份起來。 • 此外如果資料庫中有部分資料表會經常變動, 另一部分很少變動, 也可考慮將這兩部分的內容分存於不同的檔案和檔案群組, 然後用不同的備份頻率來備份其內容。

17. 使用檔案和檔案群組備份 • 但依檔案和檔案群組來進行備份的缺點, 也就在於需分數次備份, 才能完成整個資料庫的備份, 而不像做一次資料庫備份, 就能把全部的檔案/檔案群組都備份下來。因此除非是資料庫中有多個檔案已大到會影響備份作業的進行時, 才需使用檔案和檔案群組備份。 • 以上介紹了幾種典型的資料庫備份方式, 大家可依自己的環境和需求, 找出比較合適的方案來加以進行。

18. 使用檔案和檔案群組備份 • 通常異動頻繁的資料庫, 免不了是要多做幾次備份, 而比較不會更動的資料庫則可減少備份次數, 但就算是放在資料倉儲中的歷史性資料庫, 雖然幾乎不會異動, 也最好做完整的資料庫備份, 這樣的話在伺服器出問題時, 可利用這個備份讓資料庫儘速回復原狀, 繼續供使用者使用。

19. 使用備用伺服器 • 除了傳統的備份方式之外, 還有一種方法可提供和備份相同的資料保護, 那就是準備一台備用(standby) 伺服器。 • 在第二章介紹磁碟陣列時提過, RAID 1 磁碟陣列是用一顆額外的硬碟來複製整顆硬碟的內容,使得在有一顆硬碟出問題的情況下, 仍能有一顆硬碟供我們存取資料。而備用伺服器的作法就和這種鏡射的方式一樣, 只不過鏡射的對象改為整個 SQL Server。

20. 使用備用伺服器 • 因此要建立備用伺服器當然得先準備一台額外的伺服器電腦, 雖然配備不見得要與原資料庫伺服器相同, 但也不能差太多, 以免無法負擔 SQL Server 的運作。 • 而建立備用伺服器資料庫的方式, 是先在原伺服器上做完整資料庫備份, 並將此備份拿到備用伺服器上還原, 使兩邊都有相同的資料庫內容。

21. 使用備用伺服器 • 日後只需定期在原伺服器上做交易記錄備份, 並同樣拿到備用伺服器上還原, 使兩邊的資料庫保持同步。一旦原伺服器出問題時, 我們就可立即將備用伺服器接上網路, 讓使用者能繼續享受 SQL Server 的服務。

22. 使用備用伺服器 • 雖然使用備用伺服器能提供近似無中斷的資料庫服務, 不過由於需多準備一台伺服器硬體, 平時也需不斷地做備份和還原的動作, 以保持備用伺服器上的資料夠新 , 所以除非真的有不能讓 SQL Server 服務中斷過久的需求, 否則還是採傳統的 "備份+還原" 的方案就可以了。 • 關於在備用伺服器上還原資料的方式, 會在下一章說明。

23. 應該備份的資料庫 • 在考慮備份策略時, 還有一項重點, 那就是究竟哪些資料庫需要備份。一般而言, 由我們 (使用者) 自建的資料庫大多都需要備份, 此外就是系統資料庫中的 master和 msdb 也都應該要備份, 至於 model 資料庫, 則視您是否建了自訂的資料庫物件, 如果有當然也要備份。至於 tempdb 當然是不用備份的, 而 AdventureWorks這個範例資料庫照理也是不需備份的。如果您使用了第 9 章所介紹的複寫功能, 也要備份『散發』資料庫, 相關介紹請參見第 9 章。

24. 應該備份的資料庫 • 至於各資料庫應採用何種備份方案, 就看各資料庫的使用情形了。就像前面說過的, 變動頻繁的資料庫可考慮採用三種備份方式交替使用的方案, 而很少更動的資料庫當然可適度調整備份週期。但這也只是大方向而已, 就像前面所提過的, 我們必須將時間、空間等多項因素一起加以考量, 才能找出適合的整體備份方案, 這些就留待大家自己去思考了。

25. 資料庫的復原模式 • 當您決定好資料庫的備份方案後, 例如要使用資料庫備份配合交易記錄備份, 或是還要再加上差異式備份, 請先進入資料庫的屬性交談窗, 查看一下資料庫的復原模式是否與您的備份方案相符合：

26. 資料庫的復原模式

27. 資料庫的復原模式 • 簡單 (Simple)：在第 4-19 頁中提到過, SQL Server 會在檢查點 (Checkpoint) 將交易記錄的內容清除掉。而簡單復原模式就相當是設定了這個選項, 換言之交易記錄檔會在特定的檢查點 (例如 SQL Server 停止時) 自動被清除, 因此對資料庫做交易記錄備份就沒什麼用了。選擇簡單復原模式的資料庫, 只能做資料庫備份和差異式備份。

28. 資料庫的復原模式 • 完整 (Full)：所有對資料庫的操作都會記錄到交易記錄中, 因此只要適度的搭配資料庫備份和交易記錄備份, 就能將資料庫回復到最接近問題發生時的狀態。 • 大量記錄 (Bulk-logged)：和完整模式類似, 但對於大量複製動作 (像是使用 bcp、BULK INSERT、SELECT INTO、WRITETEXT、以及 UPDATETEXT 等敘述), 會以較節省空間的方式記錄於交易記錄, 而不像完整模式是 "完整" 記錄。

29. 資料庫的復原模式 • 因此當交易記錄備份中有這類動作的記錄, 還原時只能做完整的還原, 而不能指定還原到某特定的時點。 • 若希望日後新建立的資料庫預設都採用某種模式, 例如都用簡單模式, 只需將model 資料庫設為簡單模式即可。 • 設定好資料庫的回復模式, 接著就可準備開始備份資料庫了。

30. 7-2 備份裝置 • 在介紹了 SQL Server 所提供的各種不同備份方式, 以及如何規劃出適合自己工作環境的備份方案之後, 我們便可開始著手進行備份作業了。雖然從 7.0 開始,SQL Server 就已捨棄裝置 (Device) 的觀念, 不過在備份功能方面還是有個備份裝置可加以設定, 所以這一節我們先來看如何建立備份裝置。

31. 備份裝置 • 認識備份裝置 • 建立備份裝置 • 使用系統預存程序建立備份裝置

32. 認識備份裝置 • 所謂備份裝置聽來似乎是指用來存放備份資料的儲存媒體, 不過在 SQL Server中並不是這個意思, 而只是單指備份資料的存放路徑而已。而 SQL Server 支援的備份儲存媒體則有硬碟和磁帶機, 如果想以 MO 等裝置來備份, 可將它們當成 "硬碟" 來用。

33. 認識備份裝置 • 以硬碟為例, 它只是一個儲存媒體, 但我們卻可在其中設定多個備份資料存放路徑, 也就是設定多個備份『裝置』, 其實每個裝置就是一個檔案。至於使用磁帶機時, 由於只能設定同樣的磁帶機路徑, 所以我們可將其視為單一的備份裝置。

34. 認識備份裝置 • 不管使用何種媒體, 我們都可在同一個裝置中存放多個資料庫的備份資料, 或同一資料庫不同時間的備份資料。備份時可指定要將資料附加在裝置中現有資料的後面, 或是將之覆蓋掉, 這些在稍後介紹備份作業時會再說明。 • 選擇儲存媒體

35. 選擇儲存媒體 • 隨著硬碟空間不斷增加, 而價格又一直下滑, 使用硬碟來備份資料似乎也是個不錯的選擇。但選擇用硬碟做備份時最好注意幾點： • 儘量備份到其它遠端伺服器上的硬碟, 以降低風險, 因為若只備份至本地硬碟上,當發生硬體錯誤或天災時, 連備份資料也都不保了。 • 可能的話, 可自行將備份到硬碟的檔案燒錄到光碟上。

36. 選擇儲存媒體 • 循序存取的磁帶 (機) 雖然在存取磁帶中後段的檔案會較不方便, 但因成本低廉加上技術的改善 (現今的 DAT 產品在讀寫的速度上已有不錯的表現), 所以仍是相當實用的備份裝置, 尤其是資料庫非常大時, 磁帶 (機) 的好處就更明顯了, 甚至可說是唯一可行的備份方案了。

37. 建立備份裝置 • 在進行備份工作之前, 需先建立備份裝置做為存放備份資料的場所。要建立新的備份裝置可在 Management Studio 中展開伺服器物件/備份裝置項目, 然後依如下的步驟進行：

38. 建立備份裝置

39. 建立備份裝置 • 裝置名稱可依個人的喜好來命名, 例如以資料庫名稱來命名, 日後在尋找備份資料時或許會比較好識別。在名稱欄輸入裝置名稱時, 在檔案名稱欄也會出現預設的檔名 (裝置名.BAK), 而預設的路徑則是在 SQL Server 資料夾下的 Backup 子資料夾中。 • 如果您想改存其它的地方, 可按旁邊的...鈕選擇其它的路徑。若用的是磁帶機, 則只需在磁帶輸入磁碟機的路徑, 例如 \\.\TAPE0, 而不用另行加上檔名。

40. 建立備份裝置 • 按下確定鈕後就會建立裝置, 在 Management Studio 中也會列出已建立的備份裝置：

41. 建立備份裝置 • 如果想要檢視備份裝置的詳細資訊, 如存放路徑、檔名、裝置種類等, 只要在裝置名稱上按滑鼠右鈕, 執行『屬性』命令即可。由於是新建的備份裝置, 我們尚無法也不用對它做什麼處理, 下一節我們再來說明裝置中有了備份資料後, 可對裝置進行哪些管理。

42. 建立備份裝置 • 若要刪除備份裝置, 只需先選裝置名稱, 再按Delete鍵即可, 不過要注意的是, 在Management Studio 中刪除備份裝置, 並不會刪除裝置所代表的檔案, 該檔案仍然存在。 • 您可再重新以此檔案重建裝置 (見稍後說明), 或是在作業系統下將它刪除；但如果使用系統預存程序刪除裝置, 就可順便指定刪除檔案, 以下就來看如何用預存程序建立和刪除備份裝置。

43. 使用系統預存程序建立備份裝置 • 我們也可用系統預存程序 sp_addumpdevice 來建立備份裝置, 其用法如下：

44. 使用系統預存程序建立備份裝置 • 其中 '裝置類型' 可為以下兩個值： • disk：表示使用硬碟。此類型也可用於遠端磁碟, 此時需以 UNC 指定檔案的網路路徑。 • tape：表示使用磁帶機。 • 至於 '實體名稱' 這個參數, 就磁碟而言就是檔案的路徑, 若是使用磁帶機的話則為磁碟機的名稱。最後的 '磁帶機狀態' 參數並非必要, 此參數是用來與舊版相容。

45. 使用系統預存程序建立備份裝置 • 例如下列敘述就會在硬碟上建立名為 'MYDATA' 的備份裝置： • 若要移除備份裝置, 可用另一個預存程序： • 最後面的選用參數只是用來設定是否要刪除裝置所使用的檔案, 若設為 'delfile'就表示要刪除檔案。

46. 7-3 進行備份工作 • 這一節我們就來看如何執行備份工作, 以及設定備份作業的細節。 • 在 Management Studio 中手動進行備份 • 設定排程自動進行備份 • 以 T-SQL 語法進行備份 • 從備份資料檔建立備份裝置 • 檢視備份裝置內容 • 驗證備份內容

47. 在 Management Studio 中 手動進行備份 • 我們可用 Management Studio 直接將資料庫備份到現存的備份裝置上。要對資料庫進行備份, 只需在資料庫名稱上按滑鼠右鈕展開快顯功能表, 然後選『工作/備份資料庫』命令：

48. 在 Management Studio 中手動進行備份

49. 在 Management Studio 中 手動進行備份

50. 在 Management Studio 中 手動進行備份