2005數(shù)據庫實踐教程與恢復技術

1、1數(shù)據庫備份與恢復技術SQL Server 2005數(shù)據庫實踐教程管理與維護篇2第一節(jié)【學習目標】數(shù)據庫備份概述；掌握如何分別在可視化及命令行方式建立和刪除數(shù)據庫磁盤備份設備；掌握在可視化狀態(tài)下進行完全、差異、日志數(shù)據備份的方法，重點掌握通過命令行進行完全、差異、日志數(shù)據備份的方法；掌握在可視化狀態(tài)下進行完全、差異、日志數(shù)據恢復數(shù)據的方法，重點掌握通過命令行進行完全、差異、日志數(shù)據恢復數(shù)據的方法；33.1數(shù)據庫備份概述 “備份”：是數(shù)據的副本，用于在系統(tǒng)發(fā)生故障后還原和恢復數(shù)據。 1、數(shù)據庫備份并不是簡單地將表中的數(shù)據復制，而是將數(shù)據庫中的所有信息，包括表數(shù)據、視圖、索引、約束條件，甚至是數(shù)據

2、庫文件的路徑、大小、增長方式等信息也備份。2、創(chuàng)建備份的目的是為了可以恢復已損壞的數(shù)據庫。但是，備份和還原數(shù)據需要在特定的環(huán)境中進行，并且必須使用一定的資源。因此，可靠地使用備份和還原以實現(xiàn)恢復需要有一個備份和還原策略。43.1數(shù)據庫備份概述 備份時候，需要考慮以下因素：1、組織對數(shù)據庫的備份需求，尤其是對必要性的防止數(shù)據丟失的要求。2、每個數(shù)據庫的特性。其大小、使用模式、內容特性及其數(shù)據要求等。3、資源的約束。例如，硬件、人員、存儲備份媒體空間以及存儲媒體的物理安全性等。53.1數(shù)據庫備份概述 1. 數(shù)據庫磁盤備份設備簡稱備份設備： 是由SQL Server 2005提前建立的邏輯存儲定義設

3、備。之所以稱為是邏輯設備，是由于在建立備份設備時候需要明確指定具體的磁盤存儲路徑，即便該磁盤存儲路徑并不存在，也可以正常建立一個備份設備。 實驗1：在資源管理器中建立備份設備實驗 第一步：在SQL Server管理平臺的【對象資源管理器】窗口中展開【服務器對象】的子節(jié)點【備份設備】上單擊鼠標右鍵，彈出快捷菜單，如右圖所示。 63.1數(shù)據庫備份概述 實驗1：在資源管理器中建立備份設備實驗 第二步：單擊新建備份設備選項，打開【備份設備】對話框。在【設備名稱】文件框中輸入“db_school_bakdevice”；在不存在磁帶機的情況下，【目標】目標選項自動選中【文件】單選項，在【文件】選項對應的文

4、本框中輸入文件路徑和名稱“C:backschool_back.bak”，如下圖所示。73.1數(shù)據庫備份概述 實驗2：在資源管理器中刪除備份設備實驗 在SQL Server管理平臺的【對象資源管理器】窗口中展開【服務器對象】的子節(jié)點【備份設備】。在節(jié)點【db_school_bakdevice】上單擊鼠標右鍵，彈出快捷菜單中刪除該設備，如下圖所示。83.1數(shù)據庫備份概述 實驗3：通過命令方式建立和刪除備份設備實驗 1. 建立備份設備我們可以通過執(zhí)行系統(tǒng)存儲過程sp_addumpdevice的形式，建立一個磁盤備份設備，基本語法是： EXEC sp_addumpdevice device_type

5、, logical_name , physical_name，其中各個參數(shù)的含義是：device_type：設備類型，disk|tape， “disk”表示磁盤，“tape”表示磁帶。logical_name：邏輯磁盤備份設備名。physical_name：物理磁盤備份設備名。-例1：使用T-SQL語句的存儲過程sp_addumpdevice命令行創(chuàng)建磁盤備份設備的物理備份設備名為“E:backupstudent_bak”，邏輯備份設備名為“db_student_bakdevice”。exec sp_addumpdevice disk,db_student_bakdevice,E:backup

6、student_bak93.1數(shù)據庫備份概述 實驗3：通過命令方式建立和刪除備份設備實驗 刪除備份設備刪除一個磁盤備份設備的基本語法是： EXEC sp_dropdevice logical_name , delfile其中各個參數(shù)的含義是：logical_name：邏輯磁盤備份設備名。delfile：表示是否同時刪除磁盤備份物理設備名。-例2：使用T-SQL語句的存儲過程sp_dropdevice命令行刪除前面剛創(chuàng)建的磁盤備份設備。exec sp_dropdevice db_student_bakdevice, delfile103.1數(shù)據庫備份概述 2.數(shù)據庫備份方法 數(shù)據庫備份包括完整備

7、份和差異性備份，這兩種備份的區(qū)別如下：（1）完整備份： 包含數(shù)據庫中全部數(shù)據和日志文件信息，也被稱為是全庫備份或者海量備份。對于文件磁盤量較小時候，完全備份的資源消耗并不能顯現(xiàn)，但是一旦數(shù)據庫文件的磁盤量非常大的時候，就會明顯的消耗服務器的系統(tǒng)資源。因此對于完全備份一般需要停止數(shù)據庫服務器的工作，或在用戶訪問量較少的時間段進行此項操作。 故障發(fā)生點 完全數(shù)據備份 運行事務 正常運行 Ta Tb 數(shù)據丟失 Tf 重裝完整備份 恢復 113.1數(shù)據庫備份概述 （1）完整備份： 通過還原數(shù)據庫，只用一步即可以從完整的備份重新創(chuàng)建整個數(shù)據庫。如果還原目標中已經存在數(shù)據庫，還原操作將會覆蓋現(xiàn)有的數(shù)據庫；

8、 如果該位置不存在數(shù)據庫，還原操作將會創(chuàng)建數(shù)據庫。還原的數(shù)據庫將與備份完成時的數(shù)據庫狀態(tài)相符，但不包含任何未提交的事務。 恢復數(shù)據庫后，將回滾到未提交的事務。 小知識： 事務(Transaction)是用戶定義的一個數(shù)據庫操作序列，這些操作要么全做，要么全不做，是一個不可分割的工作單位事務和程序是兩個概念在關系數(shù)據庫中，一個事務可以是一條SQL語句，一組SQL語句或整個程序一個應用程序通常包含多個事務事務是恢復和并發(fā)控制的基本單位123.1數(shù)據庫備份概述 （1）完整備份： 當執(zhí)行全庫備份時，SQL Server將備份在備份過程中發(fā)生的任何活動，以及把任何未提交的事務備份到事務日志。在恢復備份時

9、候，SQL Server利用備份文件中捕捉到的部分事務日志來確保數(shù)據一致性。 133.1數(shù)據庫備份概述 （1）完整備份：實驗1：在資源管理器中進行完全數(shù)據備份實驗第一步：打開資源管理器，鼠標右擊school數(shù)據庫，在展開的菜單中選擇任務中的備份項。 143.1數(shù)據庫備份概述 （1）完整備份：實驗1：在資源管理器中進行完全數(shù)據備份實驗第二步：在展開的備份數(shù)據庫界面中，選擇備份類型為“完整”，備份組件為數(shù)據庫，在備份目標為備份到磁盤，選擇添加磁盤的具體的路徑及備份文件名為C:school_fullback.bak，如圖所示。點擊確定后完成完全數(shù)據備份的工作，所生成的C:school_fullbac

10、k.bak文件將在后面數(shù)據庫恢復中被重新應用。 153.1數(shù)據庫備份概述 （1）完整備份：實驗2：通過命令行進行完全數(shù)據備份實驗 第一步：sp_addumpdevice 是系統(tǒng)存儲過程，用于創(chuàng)建磁盤備份文件，其基本命令行如下所示：sp_addumpdevice devtype=device_type,logicalname=logical_name, physicalname = physical_name,cntrltype = controller_type|devstatus=device_status use master-首先，進入master數(shù)據庫。Go-下面，在C盤下建立文件夾b

11、ack，然后分別執(zhí)行下面的三個磁盤備份文件。exec sp_addumpdevice disk,backup_file1,c:backbackup_file1.bakexec sp_addumpdevice disk,backup_file2,c:backbackup_file2.bakexec sp_addumpdevice disk,backup_log,c:backbackup_log.bak注意：即便是C盤下面沒有back文件夾，該命令也可以成功的執(zhí)行。但是如果在實際進行磁盤備份中，由于沒有該文件夾，將在執(zhí)行backup database的時候出現(xiàn)錯誤。163.1數(shù)據庫備份概述 （1）

12、完整備份：實驗2：通過命令行進行完全數(shù)據備份實驗 第二步：將school數(shù)據庫備份到第一步建立的磁盤備份文件中。BACKUP DATABASEdatabase_name|database_name_var ,.f TO ,.n .,INIT|NOINIT在進行數(shù)據庫備份的時候，INIT和NOINIT選項參數(shù)非常重要。使用 NOINIT 選項，SQL Server 把備份追加到現(xiàn)有的備份文件，也就是在原有的數(shù)據備份基礎上，繼續(xù)將現(xiàn)有的數(shù)據庫追加性的繼續(xù)備份到該磁盤備份文件中。使用 INIT 選項，SQL Server 將重寫備份媒體集上所有數(shù)據，即將上次備份的文件抹去，重新將現(xiàn)有的數(shù)據庫文件寫入

13、到該磁盤備份文件中。 173.1數(shù)據庫備份概述 （1）完整備份：實驗2：通過命令行進行完全數(shù)據備份實驗 backup database school to backup_file1 with noinitbackup database school to backup_file2 with init-請反復執(zhí)行這兩句話，那么我們可以很快從磁盤文件的空間變化中發(fā)現(xiàn)init和no init的區(qū)別：-可見，init由于重新建立磁盤備份，因此文件并沒有增長；而由于noinit是追加備份，因此磁盤文件增長非常明顯。當然，我們也可以不需要使用磁盤備份文件，而通過直接指定磁盤路徑的方式實現(xiàn)對數(shù)據庫文件進行備

14、份。BACKUP DATABASE school TO DISK=D: Mydiffbackup.bak183.1數(shù)據庫備份概述 （2）差異備份： 差異性備份： （1）是無需完全數(shù)據備份，僅僅將變化的數(shù)據存儲并追加到數(shù)據庫備份文件中的過程。 （2）差異性備份僅記錄自上次完整備份后更改過的數(shù)據，但是比完整備份更小、更快，可以簡化頻繁的備份操作，減少數(shù)據丟失的風險。 （3）差異性備份必須基于完整備份，因此差異性備份的前提是進行至少一次的完全數(shù)據備份。 故障發(fā)生點 完全數(shù)據備份 運行事務 差異備份 正常運行 Ta Tb Tc 數(shù)據丟失 Tf 重裝完整備份 差異數(shù)據還原 恢復 193.1數(shù)據庫備份概述

15、 （2）差異備份：在還原差異性備份之前，必須先還原其完全數(shù)據備份。如果按給定備份的要求進行一系列差異性備份，則在還原時只需還原一次完全數(shù)據備份和最近的差異性備份即可。執(zhí)行差異性備份的前提和基本條件如下： 用于經常被修改的數(shù)據庫； 要求一個完全數(shù)據備份，這是執(zhí)行差異性備份的前提； 備份自上次完全數(shù)據備份以來的數(shù)據庫變化； 執(zhí)行差異性備份的語法與完全數(shù)據備份基本一致，僅僅的區(qū)別是在后面寫上WITH DIFFERENTIAL參數(shù)即可。 203.1數(shù)據庫備份概述 （2）差異備份：實驗1：通過命令行進行差異數(shù)據備份實驗 BACKUP DATABASE school TO DISK=D:school_ba

16、ck.bak WITH DIFFERENTIAL -或者 backup database school to backup_file2 WITH DIFFERENTIAL213.1數(shù)據庫備份概述 （2）差異備份：實驗2：在管理平臺中進行差異數(shù)據備份實驗 打開備份向導。在“備份數(shù)據庫”窗口中，選擇備份類型為“差異”。在備份的目標中，指定備份到的磁盤文件位置（本例中為C:backschool.bak文件），如圖所示。然后單擊“確定”按鈕。備份完成后，可以找到C:backschool.bak文件。差異備份文件要比完全備份文件小得多，因為它僅備份自上次完整備份后更改過的數(shù)據。 223.1數(shù)據庫備份概述

17、 （3）日志文件備份：日志文件備份定義：當數(shù)據庫文件發(fā)生信息更改時候，其基本的操作記錄將通過日志文件進行記錄，對于這一部分操作信息進行的備份就是日志文件備份。 運行事務 故障發(fā)生點 日志備份 運行事務 正常運行 Ta Tb Tf重裝后備副本 利用日志文件恢復恢復 233.1數(shù)據庫備份概述 （3）日志文件備份：執(zhí)行日志文件備份的前提和基本條件是要求一個完全數(shù)據備份，備份日志文件的語法形式是：BACKUP LOG database_name | database_name_var TO ,.n WITH , INIT | NOINIT , NO_TRUNCATE| TRUNCATE_ONLY 該命

18、令中的基本參數(shù)的意義是：1、NO_LOG 選項（1）無須備份復制日志，即刪除不活動的日志部分，并且截斷日志。該選項會釋放空間。因為并不保存日志備份，所以沒有必要指定備份設備。（2）NO_LOG 和TRUNCATE_ONLY 是同義的，使用NO_LOG 或TRUNCATE_ONLY 備份日志后，記錄在日志中的更改不可恢復。（3）為了恢復數(shù)據庫的準確性，在執(zhí)行日志文件應用該參數(shù)命令的同時，請立即執(zhí)行BACKUP DATABASE命令，進行完全數(shù)據備份工作，以防止數(shù)據的意外丟失。 243.1數(shù)據庫備份概述 （3）日志文件備份：該命令中的基本參數(shù)的意義是：2、 NO-TRUNCATE 選項（1）當數(shù)據

19、庫文件被損壞或者丟失，應該使用NO-TRUNCATE選項備份事務日志；（2）該選項可以完全備份所有數(shù)據庫的最新活動信息。執(zhí)行后MSSQL將進行下面的活動： 保存整個事務日志，即使無法訪問數(shù)據庫。 不清理已提交事務日志的事務。 可以將數(shù)據庫恢復到系統(tǒng)出現(xiàn)故障的時刻。 使用TRUNCATE-ONLY選項，或者NO_LOG選項，一般在以下情況發(fā)生： 事務日志已滿，清理日志文件。 需要截斷事務日志。253.1數(shù)據庫備份概述 （3）日志文件備份：該命令中的基本參數(shù)的意義是：2、 NO-TRUNCATE 選項（3）該參數(shù)是只能夠與BACKUP LOG命令一起使用的；（4）該參數(shù)使用的意義是，指定不截斷日志

20、，并使數(shù)據庫引擎嘗試執(zhí)行備份，而不考慮數(shù)據庫的狀態(tài)。（5）使用 NO_TRUNCATE 執(zhí)行的備份可能具有不完整的元數(shù)據。該選項允許在數(shù)據庫損壞時備份日志。實質上，當數(shù)據庫遭受嚴重損壞后，該命令是最后的解決辦法，即無論怎樣都現(xiàn)將發(fā)生的任何操作信息先備份到日志中，嘗試進行后期盡可能的還原工作。 263.1數(shù)據庫備份概述 （3）日志文件備份：實驗1：在管理平臺中進行日志文件備份實驗 打開備份向導。在“備份數(shù)據庫”窗口中，選擇備份類型為“事務日志”。在備份的目標中，指定備份到的磁盤文件位置（本例中為c:backbackup_log.bak文件），如圖所示。然后單擊“確定”按鈕。備份完成后，可以找到c

21、:backbackup_log.bak文件。 273.1數(shù)據庫備份概述 （3）日志文件備份：實驗2：通過命令行進行日志文件備份實驗 -備份事務日志，追加到現(xiàn)有日志文件backup log school to disk=d:school_log.bak WITH NOINIT-清空日志文件backup log school with no_log-備份事務日志，重寫現(xiàn)有日志文件，并盡可能的將所有發(fā)生的操作信息到日志文件中BACKUP LOG school TO DISK=c:school_log.bak WITH INIT,NO_TRUNCATE -如果不想要日志或者是日志已沒有什么作用時,可以

22、考慮以下的實現(xiàn)方案: backup log DBNAME with no_log|truncate_onlyno_truncate28第二節(jié)【學習目標】掌握在可視化狀態(tài)下進行完全、差異、日志數(shù)據恢復數(shù)據的方法重點掌握通過命令行進行完全、差異、日志數(shù)據恢復數(shù)據的方法；熟練掌握案例及案例所示的命令行過程 293.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗1：在管理平臺中利用完全數(shù)據備份還原數(shù)據庫實驗 第一步：首先新建一個空的school數(shù)據庫，而后用鼠標右鍵單擊“對象資源管理器”中的“school”數(shù)據庫對象。在彈出的快捷菜單中選擇“任務”“還原

23、” “數(shù)據庫”選項，如圖所示 303.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗1：在管理平臺中利用完全數(shù)據備份還原數(shù)據庫實驗 第二步：在“還原數(shù)據庫”窗口中，選擇還原的數(shù)據庫為“school”，選擇用于還原的備份集為在備份操作中備份的完整數(shù)據集，如圖所示 313.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗1：在管理平臺中利用完全數(shù)據備份還原數(shù)據庫實驗 在“還原數(shù)據庫”窗口中選擇選項，在還原選項中選“覆蓋現(xiàn)有數(shù)據庫”復選框，如圖所示，按“確定”按鈕。還原操作完成后，打開“school”數(shù)據庫

24、，可以看到其中的數(shù)據進行了還原。在school中看不到進行完整備份后新增加的school數(shù)據，因為還原過程進行了完整備份的還原。 323.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗2：在管理平臺中中利用差異數(shù)據備份還原數(shù)據庫實驗 第一步：在實驗1的基礎上，將school數(shù)據庫的student表中插入一條學生記錄后（假設姓名是關羽，如圖所示），選擇一次差異數(shù)據備份，備份至backup_file2.bak文件中。如圖所示。 333.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗2：在管理平臺中中利用

25、差異數(shù)據備份還原數(shù)據庫實驗 第二步：刪除school數(shù)據庫，而后先進性一次完全數(shù)據備份，但是過程和實驗1卻不完全一致。在還原數(shù)據庫的常規(guī)選項中的操作過程相同，但是在“選項”中，必須設置其恢復狀態(tài)為“不對數(shù)據庫進行任何操作，不回滾未提交事務”，如圖所示。即將數(shù)據庫臨時“掛起”，處于恢復狀態(tài)。 343.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗2：在管理平臺中中利用差異數(shù)據備份還原數(shù)據庫實驗 注意：此時student表中是沒有關羽同學的，當然由于school數(shù)據庫被掛起，任何用戶現(xiàn)在還無法使用該數(shù)據庫。隨后，我們需要在完全數(shù)據還原的基礎上，進行差

26、異性數(shù)據庫還原。如圖所示。 353.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗2：在管理平臺中中利用差異數(shù)據備份還原數(shù)據庫實驗 然后同樣在還原選項中選“覆蓋現(xiàn)有數(shù)據庫”復選框，并設置其恢復狀態(tài)為“回滾未提交事務，使數(shù)據庫處于可用狀態(tài)”，完成差異數(shù)據還原工作。注意，如果你在完全數(shù)據庫恢復過程中，其恢復狀態(tài)沒有選擇第二項，而是默認的第一項，那么你這樣操作在進行差異性數(shù)據還原的過程中將會出現(xiàn)下面的操作失誤問題。如圖所示。成功后，查看student表，發(fā)現(xiàn)“關羽”同學已經被恢復了。 。 363.2 SQL Server 2005的數(shù)據庫恢復 在管理平臺中通過數(shù)據庫備份文件恢復數(shù)據庫 實驗3：在資源管理器中利用日志文件還原數(shù)據庫實驗 第一步：在實驗1的基礎之上，我們建立了完全數(shù)據備份文件。而后在school數(shù)據庫的student表中依然插入一條學生信息（假設該學生是“關羽”），隨后進行school數(shù)據庫的事務日志備份工