【正文】 1 主講教師 ： 錢 哨 交通部管理干部學(xué)院計(jì)算機(jī)系 錢哨教案 第三章 . 數(shù)據(jù)庫備份與恢復(fù)技術(shù) SQL Server 2022數(shù)據(jù)庫實(shí)踐教程 ——管理與維護(hù)篇 2 第一節(jié) 【 學(xué)習(xí)目標(biāo) 】 ?數(shù)據(jù)庫備份概述； ?掌握如何分別在可視化及命令行方式建立和刪除數(shù)據(jù)庫磁盤備份設(shè)備； ?掌握在可視化狀態(tài)下進(jìn)行完全、差異、日志數(shù)據(jù)備份的方法，重點(diǎn)掌握通過命令行進(jìn)行完全、差異、日志數(shù)據(jù)備份的方法； ?掌握在可視化狀態(tài)下進(jìn)行完全、差異、日志數(shù)據(jù)恢復(fù)數(shù)據(jù)的方法，重點(diǎn)掌握通過命令行進(jìn)行完全、差異、日志數(shù)據(jù)恢復(fù)數(shù)據(jù)的方法； 3 “備份 ” ： 是數(shù)據(jù)的副本，用于在系統(tǒng)發(fā)生故障后還原和恢復(fù)數(shù)據(jù)。 數(shù)據(jù)庫備份并不是簡單地將表中的數(shù)據(jù)復(fù)制，而是將數(shù)據(jù)庫中的所有信息，包括表數(shù)據(jù)、視圖、索引、約束條件，甚至是數(shù)據(jù)庫文件的路徑、大小、增長方式等信息也備份。 創(chuàng)建備份的目的 是為了可以恢復(fù)已損壞的數(shù)據(jù)庫。但是，備份和還原數(shù)據(jù)需要在特定的環(huán)境中進(jìn)行，并且必須使用一定的資源。因此，可靠地使用備份和還原以實(shí)現(xiàn)恢復(fù)需要有一個(gè) 備份和還原策略 。 4 備份時(shí)候，需要考慮以下因素： 組織對(duì)數(shù)據(jù)庫的 備份需求 ，尤其是對(duì) 必要性 的防止數(shù)據(jù)丟失的要求。 每個(gè)數(shù)據(jù)庫的特性。其大小、使用模式、內(nèi)容特性及其數(shù)據(jù)要求等。 資源的約束。例如，硬件、人員、存儲(chǔ)備份媒體空間以及存儲(chǔ)媒體的物理安全性等。 5 1. 數(shù)據(jù)庫磁盤備份設(shè)備 簡稱 備份設(shè)備 ： 是由 SQL Server 2022提前建立的邏輯存儲(chǔ)定義設(shè)備。之所以稱為是邏輯設(shè)備，是由于在建立備份設(shè)備時(shí)候需要明確指定具體的磁盤存儲(chǔ)路徑，即便該磁盤存儲(chǔ)路徑并不存在，也可以正常建立一個(gè)備份設(shè)備。 實(shí)驗(yàn) 1：在資源管理器中建立備份設(shè)備實(shí)驗(yàn) 第一步：在 SQL Server管理平臺(tái)的 【 對(duì)象資源管理器 】 窗口中展開 【 服務(wù)器對(duì)象 】 的子節(jié)點(diǎn) 【 備份設(shè)備 】 上單擊鼠標(biāo)右鍵，彈出快捷菜單，如右圖所示。 6 實(shí)驗(yàn) 1：在資源管理器中建立備份設(shè)備實(shí)驗(yàn) 第二步：單擊新建備份設(shè)備選項(xiàng)，打開 【 備份設(shè)備 】 對(duì)話框。在 【 設(shè)備名稱 】文件框中輸入 “ db_school_bakdevice”；在不存在磁帶機(jī)的情況下， 【 目標(biāo) 】目標(biāo)選項(xiàng)自動(dòng)選中 【 文件 】 單選項(xiàng)，在 【 文件 】 選項(xiàng)對(duì)應(yīng)的文本框中輸入文件路徑和名稱 “ C:\back\”，如下圖所示。 7 實(shí)驗(yàn) 2：在資源管理器中刪除備份設(shè)備實(shí)驗(yàn) 在 SQL Server管理平臺(tái)的 【 對(duì)象資源管理器 】 窗口中展開 【 服務(wù)器對(duì)象 】 的子節(jié)點(diǎn) 【 備份設(shè)備 】 。在節(jié)點(diǎn) 【 db_school_bakdevice】 上單擊鼠標(biāo)右鍵，彈出快捷菜單中刪除該設(shè)備，如下圖所示。 8 實(shí)驗(yàn) 3：通過命令方式建立和刪除備份設(shè)備實(shí)驗(yàn) 1. 建立備份設(shè)備 我們可以通過執(zhí)行系統(tǒng)存儲(chǔ)過程 sp_addumpdevice的形式，建立一個(gè)磁盤備份設(shè)備，基本語法是： EXEC sp_addumpdevice 39。device_type39。 , 39。logical_name39。 , 39。physical_name39。， 其中各個(gè)參數(shù)的含義是： device_type：設(shè)備類型， ‘ disk|tape’， “ disk”表示磁盤， “ tape”表示磁帶。 logical_name：邏輯磁盤備份設(shè)備名。 physical_name：物理磁盤備份設(shè)備名。 例 1：使用 TSQL語句的存儲(chǔ)過程 sp_addumpdevice命令行創(chuàng)建磁盤備份設(shè)備的物理備份設(shè)備名為 “ E:\backup\student_bak”，邏輯備份設(shè)備名為“ db_student_bakdevice”。 exec sp_addumpdevice 39。disk39。,39。db_student_bakdevice39。,39。E:\backup\student_bak39。 9 實(shí)驗(yàn) 3：通過命令方式建立和刪除備份設(shè)備實(shí)驗(yàn) 2. 刪除備份設(shè)備 刪除一個(gè)磁盤備份設(shè)備的基本語法是： EXEC sp_dropdevice 39。logical_name39。 , ‘delfile39。 其中各個(gè)參數(shù)的含義是： logical_name：邏輯磁盤備份設(shè)備名。 delfile：表示是否同時(shí)刪除磁盤備份物理設(shè)備名。 例 2：使用 TSQL語句的存儲(chǔ)過程 sp_dropdevice命令行刪除前面剛創(chuàng)建的磁盤備份設(shè)備。 exec sp_dropdevice 39。db_student_bakdevice39。,delfile39。 10 數(shù)據(jù)庫備份包括完整備份和差異性備份，這兩種備份的區(qū)別如下： （ 1）完整備份： 包含數(shù)據(jù)庫中 全部數(shù)據(jù) 和 日志文件 信息，也被稱為是 全庫備份 或者 海量備份 。對(duì)于文件磁盤量較小時(shí)候，完全備份的資源消耗并不能顯現(xiàn)，但是一旦數(shù)據(jù)庫文件的磁盤量非常大的時(shí)候，就會(huì)明顯的消耗服務(wù)器的系統(tǒng)資源。因此對(duì)于完全備份一般需要停止數(shù)據(jù)庫服務(wù)器的工作，或在用戶訪問量較少的時(shí)間段進(jìn)行此項(xiàng)操作。 故障發(fā)生點(diǎn) 完全數(shù)據(jù)備份 運(yùn)行事務(wù) ↓ 正常運(yùn)行 ─┼───────┼───────────── Ta Tb 數(shù)據(jù)丟失 Tf 重裝完整備份 恢復(fù) ─┼─────── ┥ 11 （ 1）完整備份： 通過還原數(shù)據(jù)庫，只用一步即可以從完整的備份重新創(chuàng)建整個(gè)數(shù)據(jù)庫。如果還原目標(biāo)中已經(jīng)存在數(shù)據(jù)庫，還原操作將會(huì) 覆蓋 現(xiàn)有的數(shù)據(jù)庫； 如果該位置不存在數(shù)據(jù)庫，還原操作將會(huì) 創(chuàng)建 數(shù)據(jù)庫。還原的數(shù)據(jù)庫將與備份完成時(shí)的數(shù)據(jù)庫 狀態(tài)相符 ，但不包含任何未提交的 事務(wù) 。 恢復(fù)數(shù)據(jù)庫后，將回滾到未提交的 事務(wù) 。 小知識(shí)： ? 事務(wù) (Transaction)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么全做，要么全不做，是一個(gè)不可分割的工作單位 ? 事務(wù)和程序是兩個(gè)概念 ? 在關(guān)系數(shù)據(jù)庫中，一個(gè)事務(wù)可以是一條 SQL語句，一組 SQL語句或整個(gè)程序 ? 一個(gè)應(yīng)用程序通常包含多個(gè)事務(wù) ? 事務(wù)是恢復(fù)和并發(fā)控制的基本單位 12 （ 1）完整備份： 當(dāng)執(zhí)行全庫備份時(shí)， SQL Server將備份在 備份過程中 發(fā)生的任何活動(dòng)，以及把任何 未提交的事務(wù) 備份到事務(wù)日志。在恢復(fù)備份時(shí)候， SQL Server利用備份文件中捕捉到的部分事務(wù)日志來確保數(shù)據(jù)一致性。 13 （ 1）完整備份： 實(shí)驗(yàn) 1：在資源管理器中進(jìn)行完全數(shù)據(jù)備份實(shí)驗(yàn) 第一步：打開資源管理器，鼠標(biāo)右擊 school數(shù)據(jù)庫，在展開的菜單中選擇任務(wù)中的備份項(xiàng)。 14 （ 1）完整備份： 實(shí)驗(yàn) 1：在資源管理器中進(jìn)行完全數(shù)據(jù)備份實(shí)驗(yàn) 第二步：在展開的備份數(shù)據(jù)庫界面中，選擇備份類型為 “ 完整 ” ，備份組件為數(shù)據(jù)庫，在備份目標(biāo)為備份到磁盤，選擇添加磁盤的具體的路徑及備份文件名為C:\，如圖所示。點(diǎn)擊確定后完成完全數(shù)據(jù)備份的工作，所生成的C:\將在后面數(shù)據(jù)庫恢復(fù)中被重新應(yīng)用。 15 （ 1）完整備份： 實(shí)驗(yàn) 2： 通過命令行進(jìn)行完全數(shù)據(jù)備份實(shí)驗(yàn) 第一步： sp_addumpdevice 是系統(tǒng)存儲(chǔ)過程，用于創(chuàng)建磁盤備份文件，其基本命令行如下所示： sp_addumpdevice [@devtype=]39。device_type39。,[@logicalname=]39。logical_name39。,[ @physicalname = ] 39。physical_name39。[,{[@trltype = ] controller_type|[@devstatus=]39。device_status39。 }] use master首先，進(jìn)入 master數(shù)據(jù)庫。 Go 下面，在 C盤下建立文件夾 back，然后分別執(zhí)行下面的三個(gè)磁盤備份文件。 exec sp_addumpdevice 39。disk39。,39。backup_file139。,39。c:\back\39。 exec sp_addumpdevice 39。disk39。,39。backup_file239。,39。c:\back\39。 exec sp_addumpdevice 39。disk39。,39。backup_log39。,39。c:\back\39。 注意： 即便是 C盤下面沒有 back文件夾，該命令也可以成功的執(zhí)行。但是如果在實(shí)際進(jìn)行磁盤備份中，由于沒有該文件夾，將在執(zhí)行 backup database的時(shí)候出現(xiàn)錯(cuò)誤。 16 （ 1）完整備份： 實(shí)驗(yàn) 2： 通過命令行進(jìn)行完全數(shù)據(jù)備份實(shí)驗(yàn) 第二步：將 school數(shù)據(jù)庫備份到第一步建立的磁盤備份文件中。 BACKUP DATABASE{database_name|@database_name_var} file_or_filegroup [ ,...f ] TO backup_device [ ,...n ] ..[[,]{INIT|NOINIT}] 在進(jìn)行數(shù)據(jù)庫備份的時(shí)候， INIT和 NOINIT選項(xiàng)參數(shù)非常重要。 ? 使用 NOINIT 選項(xiàng)， SQL Server 把備份追加到現(xiàn)有的備份文件，也就是在原有的數(shù)據(jù)備份基礎(chǔ)上，繼續(xù)將現(xiàn)有的數(shù)據(jù)庫追加性的繼續(xù)備份到該磁盤備份文件中。 ? 使用 INIT 選項(xiàng)， SQL Server 將重寫備份媒體集上所有數(shù)據(jù)，即將上次備份的文件抹去，重新將現(xiàn)有的數(shù)據(jù)庫文件寫入到該磁盤備份文件中。 17 （ 1）完整備份： 實(shí)驗(yàn) 2： 通過命令行進(jìn)行完全數(shù)據(jù)備份實(shí)驗(yàn) backup database school to backup_file1 with noinit backup database school to backup_file2 with init 請(qǐng)反復(fù)執(zhí)行這兩句話，那么我們可以很快從磁盤文件的空間變化中發(fā)現(xiàn) init和 no init的區(qū)別： 可見， init由于重新建立磁盤備份，因此文件并沒有增長；而由于 noinit是追加備份，因此磁盤文件增長非常明顯。當(dāng)然，我們也可以不需要使用磁盤備份文件，而通過直接指定磁盤路徑的方式實(shí)現(xiàn)對(duì)數(shù)據(jù)庫文件進(jìn)行備份。 BACKUP DATABASE school TO DISK=39。D:\ 39。 18 （ 2）差異備份： 差異性備份： （ 1） 是無需完全數(shù)據(jù)備份，僅僅將 變化的數(shù)據(jù) 存儲(chǔ)并追加到數(shù)據(jù)庫備份文件中的過程。 （ 2） 差異性備份僅記錄自 上次完整備份后 更改過的數(shù)據(jù)，但是比完整備份更小、更快，可以簡化頻繁的備份操作，減少數(shù)據(jù)丟失的風(fēng)險(xiǎn)。 （ 3） 差異性備份 必須基于完整備份 ，因此差異性備份的前提是 進(jìn)行至少一