第十三章 MS SQL 2005 理論與實作(二) 資料庫系統理論與實務 [邏輯思維系列]

本章在架構中的位置 MS SQL Server 2005 理論與實作(一) (08) My SQL Server 2005 理論與實作(二)(13) 回復技術(11) 結構化查詢語言 SQL(一)(06) 結構化查詢語言 SQL(二)(07) 與管理(12) 資料庫安全 關聯式代數(05) 並行控制(10) 關聯式模型(03) (正規化) 合併理論(04) 交易處理(09) 資料模型(02) 資料庫系統簡介(01) 邏輯與思維 2 /111

本章內容 13-1簡介 13-2交易處理與實作 13-3存取控制與綱要建立 13-4資料庫備份規劃與實作 13-5資料庫還原規劃與實作 3 /111

13-1簡介 對於一般的使用者而言，一個正確的交易是必要的 本章將會以交易的時際運作情形，以及當交易進行至一半未完成時，資料庫管理系統(MS SQL Server 2005)是否能正確地將所有的操作還原至交易前的情形來驗證 簡單說明MS SQL Server 2005的存取控制，並實際建立一個使用者的帳號，授予各種不同存取資料庫的權限。 資料庫管理系統對於非毀壞性的故障，皆能透過系統自我本身的回復功能來進行交易的完整性，但若是遇到天災人禍的毀壞性故障或人為疏失所造成的資料流失，資料庫管理系統就無法透過自我恢復的功能來完成恢復動作 本章最後會檢視MS SQL Server 2005在資料庫的備份計劃和還原計劃的功能。 4 /111

本章內容 13-1簡介 13-2交易處理與實作 13-3存取控制與綱要建立 13-4資料庫備份規劃與實作 13-5資料庫還原規劃與實作 5 /111

13-2 交易處理與實作 一個基本的交易必須要具備四個基本特性，也簡稱為交易的ACID四個特性，包括 單元性(Atomicity) 一個交易可能包括很多不同的操作，新增(INSERT)、刪除(DELETE)、更新(UPDATE)以及查詢(SELECT)四個基本操作，而這些操作必須是全部被執行完成，亦或是全部都不被執行，形成一個完整的最小執行單元 一致性的保留(Consistency Preservation) 獨立性(Isolation) 永久性(Durability or Permanency) 在MS SQL Server 2005中，一個交易可分為兩種 『隱含式交易』(Implicit Transactions) 『明確式交易』(Explicit Transactions) 6 /111

隱含式交易(Implicit Transactions) 是指開始的資料庫管理系統預設即為交易的開始狀態，不需要由人工下達交易開始的指令(BEGIN TRANSACATION) 所有操作完畢之後，必須由人工指定完成，也就是下達COMMIT或是ROLLBACK的指令之後，此交易才算是完成，否則都處於交易中，且未完成的模式下。 7 /111

明確式交易(Explicit Transactions) 是指所有交易的開始，必須由人工很明確地指出交易開始的指令 例如BEGIN TRANSACTION即表示開始 以下所下達的操作皆為此交易的一部份 最後下達COMMIT或是ROLLBACK指令，此交易才算是結束 續下頁 8 /111

一連串之操作 (INSERT、 DELETE、 UPDATE及SELECT) BEGING TRANSACTION 交易開始 一連串之操作 (INSERT、 DELETE、 UPDATE及SELECT) COMMIT ( or ROLLBACK) 交易結束 圖13-1 明確式交易 9 /111

實作前說明 MS SQL Server 2005的預設情形下是『明確式交易』模式 以【範例13-1】來說明隱含交易模式的切換和使用 為求說明方便，於每一行的最前面標示出行號，在實作上不可加上行號數 10 /111

【範例13-1】 進入隱含式交易模式，進行操作 1 SET IMPLICIT_TRANSACTIONS ON 2 SELECT * FROM 訂單明細WHERE 訂單編號='94010601' 3 DELETE FROM 訂單明細WHERE 訂單編號='94010601' 4 5 ROLLBACK 6 SET IMPLICIT_TRANSACTIONS OFF 7 11 /111

【範例13-2】 此範例的主要目的在於交易進行一半，突然發生停電狀況，可用來驗證資料庫管理系統在恢復正常之後，系統將會如何來處理此交易的操作 BEGIN TRANSACTION 2 SELECT * 訂單明細 WHERE 訂單編號 = '94010601' 3 DELETE FROM 訂單明細 WHERE 訂單編號 = '94010601' 4 5 ----- [發生停電] 利用停止SQL Server服務模擬 ----- 6 SELECT * 訂單WHERE 訂單編號 = '94010601' 7 DELETE FROM 訂單 WHERE 訂單編號 = '94010601' 8 SELECT * 訂單 WHERE 訂單編號 = '94010601' 9 COMMIT 10 ----- [資料庫管理系統恢復正常] ----- 11 12 /111

【範例13-3】 此一範例在執行到最後，下達了ROLLBACK，觀察最後結果 1 BEGIN TRANSACTION 2 SELECT * 訂單明細 WHERE 訂單編號 = '94010601' 3 DELETE FROM 訂單明細 WHERE 訂單編號 = '94010601' 4 5 SELECT * 訂單WHERE 訂單編號 = '94010601' 6 DELETE FROM 訂單 WHERE 訂單編號 = '94010601' 7 SELECT * 訂單 WHERE 訂單編號 = '94010601' 8 ROLLBACK 9 10 13 /111

【範例13-4】 此一範例為一個完整的成功交易 1 BEGIN TRANSACTION 2 SELECT * 訂單明細 WHERE 訂單編號 = '94010601' 3 DELETE FROM 訂單明細 WHERE 訂單編號 = '94010601' 4 5 SELECT * 訂單WHERE 訂單編號 = '94010601' 6 DELETE FROM 訂單 WHERE 訂單編號 = '94010601' 7 SELECT * 訂單 WHERE 訂單編號 = '94010601' 8 COMMIT 9 10 14 /111

本章內容 13-1簡介 13-2交易處理與實作 13-3存取控制與綱要建立 13-4資料庫備份規劃與實作 13-5資料庫還原規劃與實作 15 /111

13-3存取控制 資料庫的使用 在MS SQL Server 2005的驗證可分為兩種模式 要先經過系統的身份驗證(Authentication) 依據系統所授于權利來執行存取控制，稱之為授權(Authorization) 在MS SQL Server 2005的驗證可分為兩種模式 整合微軟公司所開發的Windows作業系統的網域使用者驗證，此種的帳號管理是歸於Windows作業系統 MS SQL Server 2005自行管理的帳號，就是SQL Server驗證方式 MS SQL Server 2005中的帳號(Account)管理 『登入』(login) ，作為驗證使用的帳號 『資料庫使用者』(Database User) ，當此登入被授權使用某個資料庫時，亦可稱為該資料庫的『資料庫使用者』 16 /111

MS SQL Server 2005 資料庫#1 Windows 驗證 登入 user01 資料庫使用者 user01 user03 授權 資料庫#1 登入 user01 資料庫使用者 user01 user03 登入 user02 SQL Server 驗證 資料庫#2 資料庫使用者user03 登入 user03 圖13-2 驗證(Authentication)與授權(Authorization) 17 /111

結構描述(或稱為綱要) 一個企業所需的企業需求很龐大，導致於『資料表』過於繁多，此時可以利用綱要(Schema)的方式來將繁多的物件分類，諸如tables/views/…；綱要(Schema) MS SQL Server 2005繁體中文版的翻譯為『結構描述』，所以以下將以『結構描述』稱之，可管理的物件包括資料表(table)、視界/檢視(View)、預存程式(Stored Procedure)、…等等 若是在資料庫中沒有新增任何的『結構描述』，則會使用預設『結構描述』，以MS SQL Server 2005而言為『dbo』，否則可以自訂『結構描述』名稱 例如新增兩個『結構描述』為『結構描述A』與『結構描述B』來將物件進行分類管理，方便管理者容易掌握和管理 18 /111

(a) 預設結構描述(Schema)為dbo tables/views/…… tables/views/… tables/views/… (a) 預設結構描述(Schema)為dbo (b) 自訂結構描述(Schema) 圖13-3 結構描述(Schema)架構 19 /111

在 SQL Server 2005 中，『使用者』與『結構描述』之間並沒有隱含的連接關係 結構描述可以視為單一命名空間的資料庫物件集合 命名空間(Name Space)是一種集合，在集合中的每一個元素都有其唯一的名稱 為了避免物件名稱衝突，在相同的結構描述中不能有兩個資料表擁有相同的名稱。但是在兩個不同的結構描述中，可以允許存在相同名稱的資料表。 可以將不同的結構描述(Schema)授權於不同的使用者，讓每一個使用者分別被授予不同的存取權限 每一個使用者可以分屬於一個或多個不同的資料庫角色，亦可被授予不同資料庫與結構描述的存取權限 例如只被允許讀取資料(SELECT)，不可以異動資料(Insert、Delete和Update)或是建立資料表…等等 20 /111

資料庫與結構描述 Role01 Role02 資料庫角色 結構描述A 結構描述B 結構描述C 資料庫#1 包含(權限) 授予(權限) 包含 隸屬於 結構描述A 結構描述C 結構描述D 結構描述E 結構描述F 資料庫#2 使用者 User01 User02 User03 包含(權限) 授予(權限) 圖13-4 使用者、角色、資料庫與結構描述之間的關係 21 /111

授權 可以針對一個資料庫角色授于某些特定的權限，只要是隸屬於此資料庫角色的使用者將會同時具有該角色的權限 使用者01同時可隸屬於資料庫角色A與資料庫角色B，也就是可以同時具有兩者角色所擁有的權限 資料庫角色A被禁止使用物件1，但授于存取物件3 資料庫角色B被授于存取物件1與物件3 由於在物件上產生兩個角色的存取權限的衝突，所以使用者01將會被禁止使用物件1 使用者01本身又被系統管理員授于物件2的存取權限，所以使用者01將具有對物件2與物件3的存取權限，而物件1將會被禁止使用 22 /111

物件1 資料庫 角色A 物件2 使用者01 資料庫 角色B 物件3 圖13-5 使用者與角色授權示意圖 23 /111

【範例13-5】建立登入與授權 建立一個SQL Server的『登入』帳號為user01，並授于『訂貨資料庫』的讀取權限。 [步驟01] 使用Microsoft SQL Server Management Studio [物件總管] \ [安全性] \ [登入]上按下滑鼠右鍵，點選[新增登入(N)] 續下頁 24 /111

[步驟02] 續下頁 完成[步驟02]之後將會進入[登入屬性]視窗，並於[選取頁面]中點選[一般]，如圖13-6(b)，並填入以下資料： [登入名稱(N)]為user01 點選[SQL Server 驗證]，並輸入[密碼(P)]，再填入一樣的密碼於[確認密碼(C)] [資料庫(D)]的下拉式表單點選[訂貨資料庫] 按下[確定]按鍵 續下頁 25 /111

26 /111

[步驟03] 續下頁 在完成[步驟02]之後，於[選取頁面]中點選[使用者對應]的選項後，如圖13-6(c)填入以下資料： [已對應到此登入使用者(D)]內點選[訂貨資料庫] [訂貨資料庫的資料庫角色成員資格(R)]中點選[db_datareader] 按下[確定]按鍵 續下頁 27 /111

28 /111

[步驟04] 續下頁 完成[步驟03]之後 可到[物件總管]中檢視 在[安全性] \ [登入] 下，登入user01已經成功地新建完成 並於[資料庫] \ [訂貨資料庫] \ [安全性] \ [使用者] 中，亦可看到user01，表示登入user01也已經成功成為[訂貨資料庫]的資料庫使用者 續下頁 29 /111

30 /111

【範例13-6】 依據【範例13-5】所新建的登入user01連線至MS SQL Server 2005，並連線至[訂貨資料庫]執行以下兩個SQL查詢和更改 select * from 客戶 update 客戶set 聯絡人職稱='行銷專員' where 客戶編號='C0001' 第一個查詢(SELECT)將可以成功查詢出所有客戶資料，而更改(UPDATE)的動作將會出現如圖13-7的訊息 圖13-7 未授權的錯誤訊息 31 /111

本章內容 13-1簡介 13-2交易處理與實作 13-3存取控制與綱要建立 13-4資料庫備份規劃與實作 13-5資料庫還原規劃與實作 32 /111

13-4 資料庫備份規劃與實作 資料庫的資料儲存空間大小，會因為實際需要有所不同，或許小到數M Bytes，也或許大到數Terra Bytes，或是需要更多的儲存空間 資料庫的儲存空間不多的情況下，整個資料庫備份所花的時間並不多 資料庫所使用的空間大小超過數Terra Bytes，一次的備份就可能耗費很多時間，也可能因此要有一段的時間影響使用者存取 好的資料庫備份應該可以依據備份的內容多寡，分為數個不同的備份種類 33 /111

MS SQL Server 2005的備份種類 MS SQL Server 2005提供線上(on-line)備份機制 資料庫備份會大量的存取資料庫，所以將會影響到使用者正常存取資料庫的效率 MS SQL Server 2005的備份種類可分為三種 『完整備份』 『差異備份』 『交易記錄備份』 34 /111

完整備份 續下頁 最單純的一種備份，也就是將目前資料庫內所有的資料，一次全部備份 當資料庫發生任何的故障時，只要使用距離故障時間最近一次的完整備份即可還原至備份時的資料狀態 假如某公司由於一次的完整備份可能要耗費掉半天的時間 影響到正常使用者上線使用資料庫 所以該公司安排在每週日進行完整備份 如果在第二週週日之後的週三系統發生故障，此時資料庫管理師僅能使用第二週週日所進行的完整備份資料進行還原動作 此時將會損失掉週日至週三之間進行的交易資料 續下頁 35 /111

…… 第一週 週日 完整備份 第二週 週日 完整備份 第三週 週日 完整備份 第四週 週日 完整備份 故障 週 三 圖13-8 完整備份 36 /111

完整備份的優、缺點 優點在於簡單容易 缺點在於備份的時間較長 較好的方式 故障後的還原也較為簡單容易 需要較大空間的儲存設備(例如磁碟或磁帶) 兩次完整備份間，未被更改過的資料，會被重複備份兩次 浪費備份設備的空間 較好的方式 是將完整備份當成一個備份計劃中的『備份基準點』 輔以下列兩種備份方式來填補被異動過的資料 節省不必要的資料重複備份，及有效地節省備份時間 缺點在於較為複雜，而且一個『備份計劃』，也會影響到系統故障後的『還原計劃』。 37 /111

差異備份 指距離目前最近一次的『完整備份』為基準點，從該次的『完整備份』至目前為止所有差異的資料備份 例如在第一次的完整備份之後，進行了三次的差異備份 差異備份#1是備份時間t2與t1之間被異動過的資料 差異備份#2是備份t3與t1之間被異動過的資料 差異備份#3則是備份t5與t1之間被異動過的資料 差異備份#4與#5則是基於完整備份#2為基準點 差異備份的方式必須基於一次的完整備份，備份之間差異的資料量，可節省備份媒體的儲存空間，亦可有效地節省備份的時間 38 /111

差異備份的還原方式 依據故障發生的時間點，往前選擇最近一次的完整備份還原後，再選擇一次最近的差異備份還原即可 例如圖中的故障#1與故障#2 故障#1的還原方式： 利用完整備份#1還原 利用差異備份#2還原 系統還原後，損失時間點t4與t3之間的所有交易資料量 故障#2的還原方式： 利用完整備份#2還原 利用差異備份#4還原 系統還原後，損失時間點t8與t7之間的所有交易資料量。 續下頁 39 /111

完整備份＃１ 差異備份＃１ 差異備份＃２ 差異備份＃３ 完整備份＃２ 差異備份＃４ 差異備份＃５ 故障 #1 故障 #2 t1 t2 t3 圖13-9 差異備份 40 /111

交易記錄備份 完整備份和差異備份是針對資料庫的內容備份，也就是『資料檔』(Data Files)的內容 『交易記錄備份』 備份『系統日誌檔』(System Log Files) 『優先寫入日誌檔』(Write-Ahead Logging，WAL)機制 交易在進行時，優先將所有的操作記錄在『系統日誌檔』(System Log Files) 『立即更新』 (Immediate updates)方式或『延遲更新』(Deferred Updates)方法寫入資料庫，以便能達到『資料庫回復』(Database Recovery)功能 41 /111

交易記錄備份 基於最近一次的備份當成『備份基準點』，備份其間的交易記錄，此基準點可能是 如圖13-10所示 續下頁 『完整備份』 『差異備份』 『交易記錄備份』 如圖13-10所示 交易記錄備份#1，基於完整備份#1，備份兩者之間的交易資料 交易記錄備份#2，基於交易記錄備份#1，備份其間的交易記錄 交易記錄備份#3，基於差異備份#2為備份基準點 時間點t3至t6之間並沒有交易記錄備份，是因為被兩次的『差異備份』所取代 續下頁 42 /111

交易記錄備份＃１ 交易記錄備份＃２ 交易記錄備份＃３ 完整備份＃１ 差異備份＃１ 差異備份＃２ 故障 #1 故障 #2 t1 t2 t3 43 /111

交易記錄備份的還原方式 利用交易記錄備份的還原方式，必須依據資料庫管理系統的故障點往前找到最近一次的完整備份，再找出最近一次的差異備份，再依據最近一次的備份基準點之後的每一次交易記錄備份。如圖13-10中的兩次故障點之還原方式如下： 故障#1的還原方式： 利用完整備份#1還原 利用交易記錄備份#1還原 利用交易記錄備份#2還原 在系統還原之後，將會損失時間點t3與t4之間的所有交易資料量。 故障#2的還原方式： 利用差異備份#2還原 利用交易記錄備份#3還原 在系統還原之後，將會損失時間點t7與t8之間的所有交易資料量。 44 /111

三種備份方式的比較 『備份基準點』 如圖13-11中的箭頭所示 續下頁 『完整備份』不用『備份基準點』 『差異備份』與『交易記錄備份』皆要『備份基準點』 如圖13-11中的箭頭所示 (1)『完整備份』可以獨自備份 (2)『差異備份』必須是基於前一次完整備份之後的備份 (3)『交易記錄備份』基於完整備份後的備份 (4)『交易記錄備份』基於差異備份後的備份 (5)『交易記錄備份』基於前一個交易記錄備份後的備份 續下頁 45 /111

『備份基準點』的比較 (1) (2) (3) (4) (5) 完整 完整 差異 完整 交易記錄 完整 差異 交易記錄 …… 交易記錄 完整 圖13-11 『備份基準點』的比較 46 /111

資料庫的備份策略 依據實際不同的需求來安排備份計劃，以達到最高效率，和資料遺失的最低資料損失風險 安排一個備份計劃前，必須考量的至少有三項 第一為資料庫的資料量大小 第二為每日的平均交易量 第三為企業可接受的最高資料損失風險，也就是該企業可接受遺失的交易量大小 可分為以下四種不同的備份策略考量和評估 完整備份 完整備份+差異備份 完整備份+交易記錄備份 完整備份+差異備份+交易記錄備份 47 /111

策略1：完整備份 是MS SQL Server 2005最基本的備份基準點 可以此當唯一的備份計劃，如果該資料庫的資料量並不大，每日的交易量也不大時 如圖13-12所示 完整備份之間的時間間隔距離可依據資料損失風險評估 例如在每日的下班後，資料庫管理人員再進行完整備份 此備份計劃的最高資料損失風險為一日的交易量，也就是從前一日的完整備份至今日完整備份之間的最高遺失量 此種的備份計劃較為簡單，還原計劃也只要選擇系統發生故障點的最近一次完整備份資料進行還原即可，所以還原計劃也是較為簡單 續下頁 48 /111

單一的完整備份圖示 完整備份＃１ 完整備份＃２ 完整備份＃３ 完整備份＃４ 圖13-12 單一的完整備份 49 /111

策略1：完整備份的缺點 當資料庫的資料量很大時，或是每日的交易量很龐大時，每一次的完整備份將會耗費過多的時間，若是在備份計劃中的備份時間間隔太短，將會造成資料庫管理人員在備份工作上的負擔 倘若時間間隔太長，又將會造成資料損失風險的提高 當資料庫的資料量大，或是每日交易量大時，此種的備份計劃將不適用 50 /111

策略2：完整備份+差異備份 資料庫的大小較為龐大時，使用第一種方式，將會產生資料庫管理人員的備份負擔 『完整備份+差異備份』 如圖13-13 改善備份計劃，將兩次完整備份的時間間隔拉長 兩次之間使用『差異備份』來輔助其不足點 降低資料遺失的資料損失風險度 如圖13-13 差異備份之間的間隔必須視其每日的平均交易量 例如每週一次的完整備份，每日採用差異備份來備份其間的差異量 續下頁 51 /111

完整備份+差異備份圖示 完整備份＃１ 差異備份＃１ 差異備份＃２ 差異備份＃３ 完整備份＃２ 差異備份＃４ 圖13-13 完整備份+差異備份 52 /111

策略2：完整備份+差異備份的缺點 由於差異備份的備份基準點一定為完整備份，每一次的差異備份，將會以最近一次的完整備份至該次備份之間的差異量，所以每次的差異備份量將會逐次增加 如圖13-13所示，也將會造成備份的負擔，所以在兩次完整備份之間應該要安排幾次的差異備份也必須列入考量 53 /111

策略3：完整備份+交易記錄備份 差異備份的特性就是從最近一次的完整備份開始備份起，在數次的差異備份之後的資料量將會逐次增加，所以可以改採用『完整備份+交易記錄備份』的備份計劃來改善 如圖13-14所示 兩次完整備份之間插入數次『交易記錄備份』 此備份計劃可適用於每日平均交易量較大時 利用拉長兩次完整備份時間間隔來降低備份負擔，再縮短交易記錄備份時間間隔來降低資料損失風險度 續下頁 54 /111

完整備份+交易記錄備份圖示 交易記錄備份＃２ 交易記錄備份＃４ 交易記錄備份＃１ 交易記錄備份＃３ 完整備份＃１ 完整備份＃２ 圖13-14 完整備份+交易記錄備份 55 /111

策略3：完整備份+交易記錄備份的缺點 在兩次的完整備份之間將會產生過多的交易記錄備份，未來在還原時也將會增加還原計劃的時間 在數次的交易記錄備份中，除了第一次的交易記錄備份是基於前一次完整備份的備份基準點之外，其他皆是基於前一次的交易記錄備份的備份基準點，倘若在其間有一次的交易記錄備份的資料毀壞，將會造成該次的資料毀壞點至系統故障點之間的資料無法還原的問題 56 /111

策略4：完整備份+差異備份+交易記錄備份 在兩次完整備份之間有過多的交易記錄備份，將會有如上述的問題，所以可以考量使用『完整備份+差異備份+交易記錄備份』來改善此一問題 兩次的『完整備份』之間插入數次的『差異備份』 兩次的『差異備』份之間再插入數次的『交易記錄備份』 能有效提升備份效率，也能降低資料損失風險度 續下頁 57 /111

完整備份+差異備份+交易記錄備份圖示 交易記錄備份＃２ 交易記錄備份＃３ 交易記錄備份＃４ 差異備份＃２ 交易記錄備份＃５ 交易記錄備份＃６ 完整備份＃２ 交易記錄備份＃１ 差異備份＃１ 完整備份＃１ 圖13-15 完整備份+差異備份+交易記錄備份 58 /111

檔案與備份裝置之備份 在MS SQL Server 2005的備份儲存媒體可分為兩種 『檔案』型備份 『備份裝置』型備份 檔案型的備份方式，對於資料庫管理人員而言只是儲存於作業系統下的一個或多個檔案(*.bak) 無法得知備份資訊，包括備份的時間、備份的種類以及備份的內容…等等 『備份裝置』型備份 資料庫管理系統(即為MS SQL Server 2005)中特別一個物件項目為『備份裝置』 位於MS SQL Server 2005 Management Studio的物件總管，[伺服器物件]\[備份裝置]) 資料庫管理人員可透過[備份裝置]物件中，看到一切備份資訊，不論在備份或是還原工作都能較為好管理和簡單化 59 /111

『檔案』型備份 資料庫的備份是直接以檔案的形式存放於儲存媒體之中 儲存媒體可以是透過磁帶機或是磁碟機方式儲存 例如 資料庫#1是儲存於磁帶 資料庫#2是儲存於磁碟機 資料庫#3跨兩個(亦可以更多個)磁碟機儲存 對於資料庫管理人員而言，都是直接對作業系統中的『檔案』來進行備份和還原的管理工作 檔案型的備份方式，當然亦可將數個不同的資料庫，儲存於相同的一個檔案之中，但如此的管理方式，無形中增加了管理上的複雜度 續下頁 60 /111

資料庫 資料庫管理系統 – MS SQL Server 2005 資料庫 #1 資料庫 #2 資料庫 #3 儲存媒體 作業系統 磁帶 磁碟 (a) 『檔案』備份 圖13-16 檔案型與備份裝置型之備份 61 /111

『備份裝置』型備份 資料庫在備份之前必須先為所要備份的資料庫建立一個或數個『備份裝置』 每一個備份裝置對應到儲存媒體中，亦是一個檔案 與檔案型的備份方式，差別在於中間多了一層備份裝置來對應 備份裝置是歸屬於資料庫管理系統(亦就是MS SQL Server 2005)所管理，再透過作業系統對應到儲存媒體中的檔案 可以透過MS SQL Server 2005的Management Studio來管理備份和還原的工作 62 /111

『備份裝置』型備份範例說明 資料庫#1 資料庫#2 資料庫#3，跨三個不同的備份裝置 續下頁 對應的是備份裝置#1 透過MS SQL Server 2005與作業系統對應至磁帶中的循序型檔案 資料庫#2 如同資料庫#1 對應的是備份裝置#2和磁碟機中的檔案 資料庫#3，跨三個不同的備份裝置 備份裝置#3是對應到單一磁碟機中的檔案 備份裝置#4和#5都對應到相同的磁碟機，所對應的檔案依然是各自對應一個檔案，而這兩個檔案只是儲存於相同的磁碟機 第一次使用三個備份裝置，以後再使用相同的備份裝置時，一定要同時使用這三個備份裝置，缺一不可 續下頁 63 /111

資料庫 備份裝置 資料庫管理系統– MS SQL Server 2005 資料庫 #1 資料庫 #2 資料庫 #3 備份 裝置 #1 備份 裝置 #2 備份 裝置 #3 備份 裝置 #4 備份 裝置 #5 儲存媒體 磁帶 磁碟 磁碟 磁碟 作業系統 (b) 『備份裝置』備份 圖13-16 檔案型與備份裝置型之備份 64 /111

【範例13-7】資料庫的備份實作 在此節將以圖13-17的備份計劃以及交易進行的資料變化來觀察備份和還原的結果，所以以下將圖13-17的實作分為以下步驟： [步驟01] 新增備份裝置名稱為[訂貨資料庫備份裝置]，並將此檔案儲存於[C:\訂貨資料庫備份檔案] [步驟02] 設定資料庫的復原模式為[完整] [步驟03] 設定覆寫媒體模式為[附加至現有的備份組] [步驟04] 完整備份#1 [步驟05] 交易#1 [步驟06] 交易記錄備份#1 [步驟07] 交易#2 [步驟08] 交易記錄備份#2 [步驟09] 交易#3 [步驟10] 差異備份#1 [步驟11] 交易#4 [步驟12] 差異備份#2 [步驟13] 交易#5 [步驟14] 交易記錄備份#3 [步驟15] 交易#6 [步驟16] 檢視備份歷程 65 /111

update 產品資料 set 建議單價 = 建議單價 + 1 交易#1 交　易 備　份　計　劃 完整備份#1 update 產品資料 set 建議單價 = 建議單價 + 1 交易#1 交易記錄備份#1 update 產品資料 set 建議單價 = 建議單價 + 2 交易#2 交易記錄備份#2 update 產品資料 set 建議單價 = 建議單價 + 3 交易#3 差異備份#1 update 產品資料 set 建議單價 = 建議單價 + 4 交易#4 差異備份#2 update 產品資料 set 建議單價 = 建議單價 + 5 交易#5 交易記錄備份#3 update 產品資料 set 建議單價 = 建議單價 + 6 交易#6 圖13-17備份計劃與交易範例示意圖 66 /111

[步驟01]新增備份裝置名稱為[訂貨資料庫備份裝置]，並將此檔案儲存於[C:\訂貨資料庫備份檔案] 利用Management Studio的物件總管 [伺服器物件] \ [備份裝置]按右鍵選擇[新增備份裝置(N)] 出現備份裝置的對話視窗，並於[裝置名稱(N)]上填入[訂貨資料庫備份裝置] 並於[目的地]的[檔案(F)]中直接填入檔案所要儲存的目錄及檔案名稱，或是按下旁邊的按鍵輔助填寫 完成後將於[伺服器物件] \ [備份裝置]下多一個[訂貨資料庫備份裝置]的物件，表示新增完成，如圖13-18所示 續下頁 67 /111

68 /111

69 /111

[步驟02]設定資料庫的復原模式為[完整] MS SQL Server 2005的復原模式可分為三種方式，分別說明如下： 完整 簡單 將該資料庫的所有交易記錄保存 設定此模式的資料庫可以進行完整備份、差異備份以及交易記錄備份三種方式 簡單 會以最少的空間來儲存交易記錄 每當系統日誌在標示檢查點(Checkpoint)或是當舊的交易已被commit之後，系統便會將檢查點之前或是已commit的交易記錄截斷，以節省空間 設定此模式的資料庫僅可以進行完整備份和差異備份，無法進行交易記錄備份 70 /111

[步驟02]設定資料庫的復原模式為[完整] 大量紀錄 會以最少的空間來記錄大部份的大量作業 (例如建立索引和大量載入)，但會完整記錄其他交易，此模式可有效地提升大量作業的效能，主要目的在輔助『完整』復原模式 建議在執行大量作業前，才切換成『大量紀錄』復原模式 當大量作業執行完畢後，應該立即設回『完整復原』模式 大量記錄復原模式可支援所有復原形式 由於此模式是以最小空間來記錄大量作業，並非以每筆交易為基礎，所以大量記錄復原模式對於大量複製作業而言，將會提高資料遺失的風險 71 /111

[步驟02]設定資料庫的復原模式為[完整] 利用Management Studio的物件總管 在[資料庫] \ [訂貨資料庫]按右鍵選擇[屬性(R)] \ 在[選取頁面]中點選[選項] [復原模式(M)]的下拉式選單中有[完整]、[大量紀錄]以及[簡單]三種復原模式，從其中選擇[完整]的復原模式，並按下[確定]按鍵 72 /111

73 /111

[步驟03] 設定覆寫媒體模式為[附加至現有的備份組] 媒體的使用可分為 『附加』 相同的媒體可以儲存多次的不同備份 每次備份的資料將會附加在檔案末端 此種方式較為方便 增加其資料損失的風險，因為將數次的備份資料儲存於一個檔案，萬一該檔案也因為某種情形而損毀，則全部的備份資料也將完全遺失 『覆蓋』 會將前一次的備份資料完全覆蓋，所以要使用此種模式時，應該為每一次的備份新增一個媒體 74 /111

[步驟03] 設定覆寫媒體模式為[附加至現有的備份組] 利用Management Studio的物件總管 在[資料庫] \ [訂貨資料庫] 點選右鍵並選擇 [工作(T)] \ [備份(B)] \ 在[選取頁面]中點選[選項] 並於覆寫媒體中選擇[備份至現有的媒體集(E)] \ [附加至現有的備份組(H)] 並於[可靠性]中勾選[完成後驗證備份(V)]，以保證備份後資料的正確性 75 /111

76 /111

[步驟04] 完整備份#1 [04-01] 確定來源資料庫 利用Management Studio的物件總管 [04-02] 選擇備份類型 在[資料庫] \ [訂貨資料庫] 點選右鍵並選擇 [工作(T)] \ [備份(B)] \ 在[選取頁面]中點選[一般] 確定來源資料庫(T)中的資料庫確實是要進行備 份的[訂貨資料庫] [04-02] 選擇備份類型 點選[備份類型(K)]的下拉式表單 會出現[完整]、[差異]以及[交易記錄]三種備份類型 點選[完整] 77 /111

[步驟04] 完整備份#1 [04-03] 給定備份組名稱 [04-04] 設定備份裝置 於[備份組]中的[名稱(N)]，設定為[完整備份#1] [04-04] 設定備份裝置 再於[目的地]中[移除(R)]不必要的檔案或備份裝置，再按下[加入(D)] 將會出現[選取備份目的地]的對話框，並點選[備份裝置(B)] 點選下拉式表單，將會看到[步驟01]所新增的備份裝置名稱[訂貨資料庫備份裝置] 點選後按[確定]開始備份 備份後要注意看是否有正常完成，以免備份失敗而不知，將會影響到未來做還原時的工作 78 /111

79 /111

80 /111

81 /111

[步驟05] 交易#1 82 /111

[步驟06] 交易記錄備份#1 重複[步驟04]，並更改以下兩項 [備份組]中的[名稱(N)]，設定為[交易記錄備份#1] [備份類型]改成[交易記錄]即可 83 /111

[步驟07] 交易#2 84 /111

[步驟08] 交易記錄備份#2 重複[步驟04]，並更改以下兩項 [備份組]中的[名稱(N)]，設定為[交易記錄備份#2] [備份類型]改成[交易記錄]即可 85 /111

[步驟09] 交易#3 86 /111

[步驟10] 差異備份#1 重複[步驟04]，並更改以下兩項 [備份組]中的[名稱(N)]，設定為[差異備份#1] [備份類型]改成[差異]即可 87 /111

[步驟11] 交易#4 88 /111

[步驟12] 差異備份#2 重複[步驟04]，並更改以下兩項 [備份組]中的[名稱(N)]，設定為[差異備份#2] [備份類型]改成[差異]即可 89 /111

[步驟13] 交易#5 90 /111

[步驟14] 交易記錄備份#3 重複[步驟04]，並更改以下兩項 [備份組]中的[名稱(N)]，設定為[交易記錄備份#3] [備份類型]改成[交易記錄]即可 91 /111

[步驟15] 交易#6 92 /111

[步驟16] 檢視備份歷程 利用Management Studio的物件總管 在[伺服器物件] \ [備份裝置] \ [訂貨資料庫備份裝置] 點選右鍵 選擇 [屬性(R)] \ 在[選取頁面]中點選[媒體内容] 觀察[備份組]中的備份歷程是否為此備份計劃歷程 93 /111

94 /111

95 /111

本章內容 13-1簡介 13-2交易處理與實作 13-3存取控制與綱要建立 13-4資料庫備份規劃與實作 13-5資料庫還原規劃與實作 96 /111

13-5 資料庫還原規劃 資料庫的『還原計劃』一定相依於當初的『備份計劃』，也就是當系統發生毀壞性故障或是人為造成必須還原之因素後，必須先依據當初備份計劃的步驟，往前推算出該選擇那些備份資料，以及如何進行還原工作。通常發生必要性的還原時，從該還原點往前找尋是否有『交易記錄備份』，將連續的『交易記錄備份』選取並先行記錄下，再往前找尋是否有『差異備份』資料，倘若找到，就需要記錄一次的『差異備份』資料，不論是否找到差異備份，都必須再往前找尋『完整備份』資料，若是找到時，所記錄下的備份資料即為還原計劃中所需要的資料和還原路徑，否則此還原計劃將會失敗，無法順利還原，如圖13-23所示。 97 /111

找尋並記錄 交易記錄備份# 找到？ 是 否 找尋並記錄 差異備份# 找尋並記錄 完整備份# 錯誤 完成 是/否 98 /111

備份計劃 五個還原點t1、t2、t3、t4以及t5，其各別的還原路徑如下： 99 /111

完整備份＃１ 交易記錄備份＃１ 交易記錄備份＃２ 交易記錄備份＃３ 差異備份＃１ 交易記錄備份＃４ 交易記錄備份＃５ 差異備份＃２ 完整備份＃２ 交易記錄備份＃６ 交易記錄備份＃７ 交易記錄備份＃８ 差異備份＃３ 備份計劃 t1 t2 t3 t4 t5 t1 t2 t3 t4 t5 還原路徑 還原之先後順序 圖13-24 資料庫的還原路徑 100 /111

資料庫還原實作 依據圖13-17之備份計劃，如果在此備份計劃中，發生不可預期的系統故障或人為的異動疏失，造成必須將資料庫還原時，如圖13-25所示的兩個還原點，還原#1與還原#2。以下將根據這兩個不同的情形，將[訂貨資料庫]進行還原的工作，也透過此實際的還原操作，可發現這兩次的還原作業之後，仍有遺失的交易資料。 101 /111

update 產品資料 set 建議單價 = 建議單價 + 1 交易#1 備　份　計　劃 交　易 完整備份#1 update 產品資料 set 建議單價 = 建議單價 + 1 交易#1 交易記錄備份#1 update 產品資料 set 建議單價 = 建議單價 + 2 交易#2 交易記錄備份#2 update 產品資料 set 建議單價 = 建議單價 + 3 交易#3 還原#1 差異備份#1 update 產品資料 set 建議單價 = 建議單價 + 4 交易#4 差異備份#2 update 產品資料 set 建議單價 = 建議單價 + 5 交易#5 交易記錄備份#3 update 產品資料 set 建議單價 = 建議單價 + 6 交易#6 還原#2 圖13-25 還原範例示意圖 102 /111

【還原#1】 還原#1的還原路徑： 損失資料： 實作： [完整備份#1]→[交易記錄備份#1] →[交易記錄備份#2] 交易#3 先將已損壞的[訂貨資料庫]先從物件總管中刪除掉 利用Management Studio的[物件總管] \ [資料庫]上按下右鍵 點選[還原資料庫(R)] ，會出現[還原資料庫]視窗 先選擇[還原的來源]中的[來源裝置]旁的按鍵後會出現[指定備份]視窗 在[備份媒體(B)]的下拉式選單中有[檔案]和[備份裝置]兩個選項，在此處選擇[備份裝置] 點選[加入(A)]的按鍵後會出現[選取備份裝置]視窗，再從其中[備份裝置(B)]的下拉式表單中選擇[訂貨資料庫備份裝置]，完成後就按[確定]按鍵返回[還原資料庫]視窗 在[選取要還原的備份組]中看到所有的備份歷程，此時必須依據[還原#1的還原路徑]，點選出所要還原的備份資料如下 103 /111

同時點選三個備份資料之後，再點選[還原資料庫]視窗中的[選項]，並於[復原狀態]中點選第一個的[RESTORE WITH RECOVERY] 此三種復原狀態說明如下： RESTORE WITH RECOVERY 選用此還原選項，可以一次點選所有還原所需要的備份資料，一次完成還原動作 並將還原後的資料庫狀態改為就緒中，讓使用者可以正常使用 倘若還原的過程不是點選所有還原所需要的備份資料，而是採用逐一備份資料還原，則此選項將會用於最後一個備份資料的還原動作，至於過程中的備份資料還原皆應採用RESTORE WITH NORECOVERY或是RESTORE WITH STANDBY 104 /111

RESTORE WITH NORECOVERY 選用此還原選項，是不回復未被認可的交易，以及所還原的資料庫是處於[還原中]的狀態，所以是無法使用的 此種的還原選項只會應用於備份資料還原的過程中，以便能夠還原後續的交易備份 整個還原過程當中，最後一個備份資料的還原必須採用RESTORE WITH RECOVERY的選項，告知資料庫管理系統後續已經沒有要還原的資料，如此被還原後的資料庫才能正常被存取 RESTORE WITH STANDBY 選用此選項時，將會恢復未被認可的交易，但是會將恢復動作儲存於待命資料庫檔案中，讓還原後的資料庫能提供唯讀模式下的使用 此一選項可用於逐一備份資料還原過程中，一個備份資料還原之後與下一個備份資料還原之前，方便資料庫管理者或是使用者可以檢視該時間點資料的正確性 105 /111

106 /111

107 /111

108 /111

109 /111

【還原#2】 還原#2的還原路徑： 損失資料： 實作： [完整備份#1]→[差異備份#2] →[交易記錄備份#3] 交易#6 【還原#2】的操作方式完全和【還原#1】相同 從[物件總管] \ [資料庫] 刪除[訂貨資料庫]，並於[還原資料庫]視窗中，依據[還原#2的還原路徑]如下： [完整備份#1]→[差異備份#2] →[交易記錄備份#3] 並於[選取要還原的備份組(E)]點選此三個備份資料，按下[確定]按鍵，即可成功還原[訂貨資料庫] 110 /111

111 /111