sql server課件第20講和恢復(fù)

1、SQL Server 2005數(shù)據(jù)庫管理系統(tǒng)第十八講 SQL Server備份和恢復(fù)引入 為了防止數(shù)據(jù)庫系統(tǒng)在運(yùn)行過程中,由于一些不可預(yù)測的因素而導(dǎo)致數(shù)據(jù)丟失等問題,數(shù)據(jù)庫的備份與恢復(fù),在實(shí)際的數(shù)據(jù)庫與維護(hù)中,就具有十分重要的意義。學(xué)習(xí)目標(biāo)數(shù)據(jù)庫備份和恢復(fù)的作用數(shù)據(jù)庫備份的方法數(shù)據(jù)庫恢復(fù)的方法知識(shí)重、難點(diǎn)分析 重點(diǎn)：數(shù)據(jù)庫備份的方法數(shù)據(jù)庫恢復(fù)的方法難點(diǎn)：數(shù)據(jù)庫備份的方法數(shù)據(jù)庫恢復(fù)的方法備份和恢復(fù): 備份和恢復(fù)組件是SQL Server 2000 的重要組成部分。備份就是指SQL Server 2000 數(shù)據(jù)庫或事務(wù)日志進(jìn)行拷貝，數(shù)據(jù)庫備份記錄了在進(jìn)行備份這一操作時(shí)數(shù)據(jù)庫中所有數(shù)據(jù)的狀態(tài) ，如果

2、數(shù)據(jù)庫因意外破壞而損壞，這些備份文件將在數(shù)據(jù)庫恢復(fù)時(shí)用來恢復(fù)數(shù)據(jù)庫。備份權(quán)限許可: 執(zhí)行備份操作必須擁有對(duì)數(shù)據(jù)庫備份的權(quán)限許可，SQL Server只允許系統(tǒng)管理員、數(shù)據(jù)庫所有者和數(shù)據(jù)庫備份執(zhí)行者備份數(shù)據(jù)庫。 備份的概念 由于SQL Server 2000支持在線備份，所以通常情況下可以一邊進(jìn)行備份，一邊進(jìn)行其他操作，但是在備份過程中不允許執(zhí)行以下操作： （1）創(chuàng)建或刪除數(shù)據(jù)庫文件。 （2）創(chuàng)建索引。 （3）執(zhí)行非日志操作。 （4）自動(dòng)或手工縮小數(shù)據(jù)庫或數(shù)據(jù)庫文件大小。 如果以上各種操作正在進(jìn)行當(dāng)中，且準(zhǔn)備進(jìn)行備份，則備份處理將被終止。如果在備份過程中，打算執(zhí)行以上任何操作，則操作將失敗而備份

3、繼續(xù)進(jìn)行。備份的概念數(shù)據(jù)備份: 數(shù)據(jù)庫備份 事務(wù)日志備份 其中，數(shù)據(jù)庫備份是創(chuàng)建數(shù)據(jù)庫的數(shù)據(jù)副本。事務(wù)日志是數(shù)據(jù)庫執(zhí)行所有事務(wù)的一系列記錄，它的起始點(diǎn)是自上次備份事務(wù)日志后對(duì)數(shù)據(jù)庫執(zhí)行的所有事務(wù)操作。通過使用事務(wù)備份可以將數(shù)據(jù)庫恢復(fù)到特定的即時(shí)點(diǎn)，或恢復(fù)到故障點(diǎn)。SQL Server 支持的備份類型:（1）完整數(shù)據(jù)庫備份: 備份包括事務(wù)日志的整個(gè)數(shù)據(jù)庫。（2）差異數(shù)據(jù)庫備份: 僅復(fù)制自上一次完整數(shù)據(jù)庫備份之后修改過的數(shù)據(jù)庫頁。（3）事務(wù)日志備份: 日志備份序列提供了連續(xù)的事務(wù)信息鏈，可支持從數(shù)據(jù)庫、差異或文件備份中快速恢復(fù)。（4）文件和文件組備份: 僅允許恢復(fù)數(shù)據(jù)庫中位于故障磁盤上的那部分 。

4、若要備份一個(gè)文件而不是整個(gè)數(shù)據(jù)庫時(shí)，請(qǐng)合理安排步驟以確保數(shù)據(jù)庫中所有的文件按規(guī)則備份，同時(shí)必須進(jìn)行單獨(dú)的事務(wù)日志備份。在恢復(fù)一個(gè)文件備份后，使用事務(wù)日志將文件內(nèi)容前滾，使其與數(shù)據(jù)庫其余部分一致。SQL Server 支持的備份類型: 在進(jìn)行備份以前首先必須創(chuàng)建或指定備份設(shè)備。備份設(shè)備是用來存儲(chǔ)數(shù)據(jù)庫、事務(wù)日志或文件和文件組備份的存儲(chǔ)介質(zhì):1、磁盤設(shè)備2、命名管道設(shè)備 微軟專門為第三方軟件供應(yīng)商提供的一個(gè)備份和恢復(fù)方式。 3、磁帶磁帶設(shè)備 備份設(shè)備在硬盤中是以文件的方式存儲(chǔ)的。備份設(shè)備物理備份設(shè)備: 是操作系統(tǒng)用來標(biāo)識(shí)備份設(shè)備的名稱，如C:BackupsAccountingFull.bak。邏輯

5、備份設(shè)備: 是用來標(biāo)識(shí)物理備份設(shè)備的別名或公用名稱。邏輯設(shè)備名稱永久地存儲(chǔ)在 SQL Server的系統(tǒng)表中。優(yōu)點(diǎn)是引用它比引用物理設(shè)備名稱簡單。例如，邏輯設(shè)備名稱可以是Accounting_Backup，而物理設(shè)備名稱則是C:BackupsAccoun- tingFull.bak。備份或恢復(fù)數(shù)據(jù)庫時(shí)，可以交替使用物理或邏輯備份設(shè)備名稱。物理和邏輯設(shè)備 使用企業(yè)管理器創(chuàng)建邏輯磁盤備份設(shè)備（1）找到服務(wù)器上的“管理”文件夾。（2）右擊“備份”，單擊“新建備份設(shè)備”命令。（3）在“名稱”框中輸入該命名備份設(shè)備的名稱。（4）單擊“文件名”，單擊“瀏覽”（.）按鈕顯示“備份設(shè)備位置”對(duì)話框，再選擇磁盤

6、備份設(shè)備所使用的本地計(jì)算機(jī)上的物理文件。（5）單擊“確定”創(chuàng)建備份設(shè)備。使用sp_addumpdevice創(chuàng)建邏輯磁盤備份設(shè)備語法如下：sp_addumpdevice devtype = device_type , logicalname = logical_name , physicalname = physical_name , cntrltype = controller_type | devstatus = device_status 其中：devtype = device_type 指定設(shè)備類型。logicalname = logical_name 指定設(shè)備的邏輯名稱。physica

7、lname = physical_name 指定設(shè)備的路徑和文件名。使用sp_addumpdevice創(chuàng)建邏輯磁盤備份設(shè)備例：USE 學(xué)生管理數(shù)據(jù)庫goEXEC sp_addumpdevice disk, bk_dev2,e:Program Filesmicrosoft sql serverMSSQLbackupbk_dev2.bak1使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫備份（1）找到創(chuàng)建數(shù)據(jù)庫備份的服務(wù)器。（2）選中指定的數(shù)據(jù)庫，右鍵單擊要進(jìn)行備份的數(shù)據(jù)庫圖標(biāo)，在彈出的菜單中選擇“所有任務(wù)”，再選擇“備份數(shù)據(jù)庫” 。（4）選擇備份的數(shù)據(jù)庫，輸入備份的名稱和備份的描述，選擇備份的類型，如果是針對(duì)文件或文件

8、組進(jìn)行備份，可以單擊右邊的按鈕選擇要備份的文件或文件組。1使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫備份（5）單擊“添加”按鈕選擇要備份的設(shè)備，可以在彈出的對(duì)話框上創(chuàng)建新的備份設(shè)備，也可以選擇多個(gè)備份設(shè)備，一次備份到多個(gè)設(shè)備上。單擊“確定”按鈕完成添加。（6）在“重寫”選項(xiàng)組中: “追加到媒體”:將新的備份添加到備份設(shè)備中， “重寫現(xiàn)有媒體”:用新的備份來覆蓋原來的備份。（7）選擇“調(diào)度”復(fù)選框，可對(duì)備份的時(shí)間表進(jìn)行設(shè)置。單擊右面的按鈕改變備份的時(shí)間安排，在彈出的“編輯調(diào)度”對(duì)話框中設(shè)定備份操作何時(shí)進(jìn)行。1使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫備份1使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫備份（8）在“名稱”中輸入備份安排任務(wù)的名字，默認(rèn)為

9、 第1調(diào)度，在“調(diào)度類型”選項(xiàng)中可以單擊“更改”按鈕改變當(dāng)前默認(rèn)的備份時(shí)間設(shè)置。 （9）單擊“確定”按鈕完成時(shí)間設(shè)置。1使用企業(yè)管理器進(jìn)行數(shù)據(jù)庫備份（9）在數(shù)據(jù)庫備份對(duì)話框中選擇“選項(xiàng)”選項(xiàng)卡進(jìn)行附加設(shè)置。在選項(xiàng)欄中通過單擊復(fù)選框進(jìn)行選擇，如果選中了“完成后驗(yàn)證備份”，表示SQL Server 會(huì)閱讀全部備份，檢查備份介質(zhì)是否可讀，保證介質(zhì)的完整性。（10）單擊“確定”按鈕完成備份。2使用T-SQL語句創(chuàng)建數(shù)據(jù)庫備份格式：BACKUP DATABASE 數(shù)據(jù)庫名 文件或文件組 ,.n TO 備份設(shè)備 ,.n with differential其中with differential表示進(jìn)行增量備

10、份。【例1】創(chuàng)建用于存放 MyNwind 數(shù)據(jù)庫完整備份的邏輯備份設(shè)備。-創(chuàng)建備份設(shè)備exec sp_addumpdevice disk, mynwind_1, disk =e:program filesmicrosoft sql servermssqlbackuplwzz_1.dat-進(jìn)行全面?zhèn)浞輇ackup database mynwind to mynwind_11利用企業(yè)管理器恢復(fù)數(shù)據(jù)庫備份 （1）啟動(dòng)企業(yè)管理服務(wù)器，展開服務(wù)器組，然后展開服務(wù)器。（2）展開“數(shù)據(jù)庫”文件夾，右擊要還原的數(shù)據(jù)庫，指向“所有任務(wù)”子菜單，然后單擊“還原數(shù)據(jù)庫”命令。（3）在“還原為數(shù)據(jù)庫”列表框中，輸入或

11、選擇要恢復(fù)的數(shù)據(jù)庫名稱。1利用企業(yè)管理器恢復(fù)數(shù)據(jù)庫備份（4）在還原選項(xiàng)欄中單擊“數(shù)據(jù)庫”單選按鈕。（5）在“要還原的第一個(gè)備份”列表中，選擇要恢復(fù)的備份集。（6）在“還原”列表中，單擊要恢復(fù)的數(shù)據(jù)庫備份。（7）單擊“選項(xiàng)”選項(xiàng)卡并執(zhí)行下列操作：（可選）在“還原為”中輸入組成數(shù)據(jù)庫備份的各數(shù)據(jù)庫文件的新名稱或新位置。（8）如果沒有其他要應(yīng)用的事務(wù)日志或差異數(shù)據(jù)庫備份，單擊“使數(shù)據(jù)庫可以繼續(xù)運(yùn)行，但無法恢復(fù)其他事務(wù)日志”。如果要應(yīng)用另一個(gè)事務(wù)日志或差異數(shù)據(jù)庫備份，則單擊“使數(shù)據(jù)庫不再運(yùn)行，但能恢復(fù)其他事務(wù)日志”。（9）然后單擊“確認(rèn)”開始恢復(fù)。2使用T-SQL語句恢復(fù)數(shù)據(jù)庫 格式：RESTORE

12、DATABASE database_name | database_name_var ,.n FROM ,.n 【例3】顯示恢復(fù)完整數(shù)據(jù)庫備份。Restore database lwzz From lwzz _1With recovery備份事務(wù)日志文件1、利用企業(yè)管理器備份事務(wù)日志文件備份事務(wù)日志文件2、使用T-SQL語句恢復(fù)數(shù)據(jù)庫格式：BACKUP LOG 數(shù)據(jù)庫名稱| database_name_var TO ,.n with no_truncate 其中with no_truncate表示在完成事務(wù)日志備份后，并不清空原有日志的數(shù)據(jù)。該選項(xiàng)用在數(shù)據(jù)庫遭到損壞或數(shù)據(jù)庫被標(biāo)示為可疑時(shí)進(jìn)行日

13、志的備份。例：備份學(xué)生管理數(shù)據(jù)庫事務(wù)日志文件BACKUP LOG 學(xué)生管理數(shù)據(jù)庫TO disk=c:Program Filesmicrosoft sql serverMSSQLData學(xué)生管理數(shù)據(jù)庫_log.bak備份事務(wù)日志文件還原事務(wù)日志文件1、利用企業(yè)管理器還原事務(wù)日志文件還原事務(wù)日志文件2、使用T-SQL語句恢復(fù)事務(wù)日志文件格式：RESTORE LOG 數(shù)據(jù)庫名稱| database_name_var FROM ,.n With recovery|norecoverystopat = date_time例：還原學(xué)生管理數(shù)據(jù)庫事務(wù)日志文件RESTORE LOG 學(xué)生管理數(shù)據(jù)庫FROM d

14、isk=c:Program Filesmicrosoft sql serverMSSQLData學(xué)生管理數(shù)據(jù)庫_log.bakWith recovery還原事務(wù)日志文件gobackup database 學(xué)生管理數(shù)據(jù)庫 to back01 -完全備份gobackup database 學(xué)生管理數(shù)據(jù)庫 to back02 with differential差異備份1gobackup database 學(xué)生管理數(shù)據(jù)庫 to back03 with differential差異備份2gobackup log 學(xué)生管理數(shù)據(jù)庫 to back04 -日志備份1gobackup log 學(xué)生管理數(shù)據(jù)庫 t

15、o back05 -日志備份2gobackup database 學(xué)生管理數(shù)據(jù)庫 to back06 with differential 差異備份3數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例1restore database 學(xué)生管理數(shù)據(jù)庫 from back01 with norecovery完全還原gorestore database 學(xué)生管理數(shù)據(jù)庫 from back03 with norecovery還原差異2gorestore log 學(xué)生管理數(shù)據(jù)庫 from back04 with norecovery 還原日志1gorestore log 學(xué)生管理數(shù)據(jù)庫 from back05 with reco

16、very,stopat=20091225 10:07:00 還原及時(shí)點(diǎn)數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例1backup log 學(xué)生管理數(shù)據(jù)庫 to back07 -故障點(diǎn)日志備份gorestore database 學(xué)生管理數(shù)據(jù)庫 from back01 with norecovery-完全還原gorestore log 學(xué)生管理數(shù)據(jù)庫 from back04 with norecovery -還原日志1gorestore log 學(xué)生管理數(shù)據(jù)庫 from back05 with norecovery -還原日志2gorestore database 學(xué)生管理數(shù)據(jù)庫 from back06 with

17、norecovery-還原差異3gorestore log 學(xué)生管理數(shù)據(jù)庫 from back07 with recovery -還原故障點(diǎn)日志go數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例1數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例2 數(shù)據(jù)庫某天晚上20:00發(fā)生了系統(tǒng)死機(jī)故障，當(dāng)天數(shù)據(jù)庫中曾經(jīng)成功地進(jìn)行了如下數(shù)據(jù)備份操作：7:00完全數(shù)據(jù)庫備份10:00事務(wù)日志備份15:00差異數(shù)據(jù)庫備份16:00事務(wù)日志備份17:00差異數(shù)據(jù)庫備份19:00事務(wù)日志備份數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例2 假定此次死機(jī)造成了大量數(shù)據(jù)丟失，為了恢復(fù)數(shù)據(jù)，可采取以下步驟：(1)重新啟動(dòng)系統(tǒng)，備份當(dāng)時(shí)的事務(wù)日志，完成故障點(diǎn)事務(wù)日志備份。(2)還原最近一次

18、完全數(shù)據(jù)庫備份，即還原7:00發(fā)生的完全備份。(3)依次還原事務(wù)日志備份，即還原10:00 和16:00發(fā)生的事務(wù)日志備份。 (4)還原差異數(shù)據(jù)庫備份，即還原17：00發(fā)生的差異數(shù)據(jù)庫備份。(5)還原故障點(diǎn)事務(wù)日志。EXEC sp_addumpdevice disk, back01,C:Program FilesMicrosoft SQL ServerMSSQLBACKUPback01.BAK -創(chuàng)建備份設(shè)備go數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例2backup database 學(xué)生管理數(shù)據(jù)庫 to back01 -完全備份Gobackup log 學(xué)生管理數(shù)據(jù)庫 to back02 日志備份1goba

19、ckup database 學(xué)生管理數(shù)據(jù)庫 to back03 with differential差異備份1Gobackup log 學(xué)生管理數(shù)據(jù)庫 to back04 日志備份2gobackup database 學(xué)生管理數(shù)據(jù)庫 to back05 with differential差異備份2gobackup log 學(xué)生管理數(shù)據(jù)庫 to back06 日志備份3go數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例2backup log 學(xué)生管理數(shù)據(jù)庫 to bak07 -故障點(diǎn)日志備份gorestore database 學(xué)生管理數(shù)據(jù)庫 from bak01 with norecovery-完全還原goresto

20、re log 學(xué)生管理數(shù)據(jù)庫 from bak02 with norecovery -日志1還原gorestore log 學(xué)生管理數(shù)據(jù)庫 from bak04 with norecovery -日志2還原gorestore database 學(xué)生管理數(shù)據(jù)庫 from bak05 with norecovery-差異2還原gorestore log 學(xué)生管理數(shù)據(jù)庫 from bak06 with norecovery -日志3還原gorestore log 學(xué)生管理數(shù)據(jù)庫 from bak07 with recovery -故障點(diǎn)還原go數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例2數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例3 SQL Server中，如果數(shù)據(jù)庫文件（非系統(tǒng)數(shù)據(jù)庫文件）遇到錯(cuò)誤時(shí)，該怎么辦?1、建一個(gè)測試數(shù)據(jù)庫test(數(shù)據(jù)庫類型為完全)2、建一個(gè)表，插入點(diǎn)記錄 create table a(c1 varchar(2) go insert into a values(aa) go insert into a values(bb) go3、作完全備份，到文件test_1.bak數(shù)據(jù)庫備份與恢復(fù)運(yùn)用實(shí)例34、再作一點(diǎn)修改 i