1、（中職）數(shù)據(jù)庫(kù)管理與應(yīng)用案例教程09-Chap09ppt課件數(shù)據(jù)庫(kù)管理與應(yīng)用實(shí)例教程彭勇 主編第9章 數(shù)據(jù)庫(kù)的管理 本章導(dǎo)航本章的主要內(nèi)容及其在SQL Server2005數(shù)據(jù)庫(kù)管理系統(tǒng)中的位置如圖示。 SQL server2005基礎(chǔ)數(shù)據(jù)模型數(shù)據(jù)庫(kù)技術(shù)概述數(shù)據(jù)庫(kù)的創(chuàng)建數(shù)據(jù)庫(kù)的管理數(shù)據(jù)庫(kù)基礎(chǔ)視圖索引表存儲(chǔ)過(guò)程觸發(fā)器數(shù)據(jù)庫(kù)對(duì)象子查詢(xún)連接查詢(xún)簡(jiǎn)單查詢(xún)聯(lián)合查詢(xún)查詢(xún)技術(shù)數(shù)據(jù)庫(kù)備份與恢復(fù)數(shù)據(jù)庫(kù)的安全游標(biāo)、事務(wù)和鎖數(shù)據(jù)庫(kù)分享與附加數(shù)據(jù)庫(kù)導(dǎo)入與導(dǎo)出數(shù)據(jù)庫(kù)安全與管理數(shù)據(jù)庫(kù)設(shè)計(jì)和應(yīng)用程序開(kāi)發(fā)學(xué)習(xí)要點(diǎn)（1）數(shù)據(jù)庫(kù)備份與恢復(fù)的基本概念（2）數(shù)據(jù)庫(kù)備份設(shè)備的管理（3）數(shù)據(jù)庫(kù)備份與恢復(fù)的方法（4）數(shù)據(jù)庫(kù)分離與附加的方法

2、（5）數(shù)據(jù)導(dǎo)入與導(dǎo)出的方法數(shù)據(jù)庫(kù)備份一、數(shù)據(jù)庫(kù)備份概述 SQL Server提供了四種數(shù)據(jù)庫(kù)備份方式，用戶(hù)可以根據(jù)自己的備份策略選擇不同的備份方式。（1）數(shù)據(jù)庫(kù)完全備份（Database-complete） （2）數(shù)據(jù)庫(kù)差異備份（Database-differential） （3）事務(wù)日志備份（Transaction log） （4）文件或文件組備份（File and Filegroup） SQL Server支持在線備份，因此，通常情況下可以一邊進(jìn)行備份，一邊進(jìn)行其他操作。但是，在備份過(guò)程中不允許執(zhí)行以下操作:創(chuàng)建或刪除數(shù)據(jù)庫(kù)文件。創(chuàng)建索引。執(zhí)行非日志操作。自動(dòng)或手工縮小數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)文件大

3、小。二、備份設(shè)備介紹 1磁盤(pán)備份設(shè)備2命名管理設(shè)備3磁帶設(shè)備4物理備份和邏輯備份設(shè)備 5使用T-SQL管理備份設(shè)備 （1）創(chuàng)建備份設(shè)備使用存儲(chǔ)過(guò)程sp_addumpdevice創(chuàng)建備份設(shè)備，其語(yǔ)法格式如下：sp_addumpdevice device_type,logical_name,physical_name（2）查看備份設(shè)備使用存儲(chǔ)過(guò)程sp_helpdevice語(yǔ)句查看備份設(shè)備信息，其語(yǔ)法格式如下：sp_helpdevice name（3）刪除備份設(shè)備使用存儲(chǔ)過(guò)程sp_dropdevice語(yǔ)句刪除備份設(shè)備，其語(yǔ)法格式如下：sp_dropdevice device,delfile三、課堂案

4、例1使用SSMS備份數(shù)據(jù)庫(kù) 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)用管理平臺(tái)備份books數(shù)據(jù)庫(kù)。【案例知識(shí)要點(diǎn)】 創(chuàng)建備份設(shè)備，備份數(shù)據(jù)庫(kù)。【案例完成步驟】（1）展開(kāi)SQL Server Management Studio工具，在“對(duì)象資源管理器”中，展開(kāi)【服務(wù)器對(duì)象】樹(shù)形目錄，右鍵單擊【備份設(shè)備】，如圖所示。 （2）在彈出的快捷菜單中選擇【新建備份設(shè)備】命令，將彈出【備份設(shè)備】窗口，如圖所示。（3）在【設(shè)備名稱(chēng)】文本框中輸入備份設(shè)備的名稱(chēng)：books_backup。（4）在【文件】文本框中輸入備份設(shè)備的路徑和文件名：D:SQLbooks_backup.bak。（5）單擊【確定】按鈕，開(kāi)始創(chuàng)建備份設(shè)備操作。

5、（6）在“對(duì)象資源管理器”中，依次展開(kāi)節(jié)點(diǎn)到要備份的數(shù)據(jù)庫(kù)books。（7）右擊【books】數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇【任務(wù)】【備份】命令，如圖所示。（8）打開(kāi)如圖所示的對(duì)話框。在【名稱(chēng)】文本框內(nèi)，輸入備份名稱(chēng)。默認(rèn)為“books-完整 數(shù)據(jù)庫(kù) 備份”，如果需要，在【說(shuō)明】文本框中輸入對(duì)備份集的描述。默認(rèn)沒(méi)有任何描述。（9）在【備份類(lèi)型】下拉列表框選擇備份的方式。其中，“完整”：執(zhí)行完整的數(shù)據(jù)庫(kù)備份；“差異”：僅備份自上次完全備份以后，數(shù)據(jù)庫(kù)中新修改的數(shù)據(jù)；“事務(wù)日志”：僅備份事務(wù)日志。（10）指定備份目的。 在【目標(biāo)】選項(xiàng)組中單擊【添加】按鈕，并在如圖所示的【選擇備份目標(biāo)】對(duì)話框中，指

6、定一個(gè)備份文件名。 （11）返回到【備份數(shù)據(jù)庫(kù)】對(duì)話框后，單擊【確定】按鈕，開(kāi)始執(zhí)行備份操作，此時(shí)會(huì)出現(xiàn)相應(yīng)的提示信息。單擊【確定】按鈕，完成數(shù)據(jù)庫(kù)備份。四、使用T-SQL備份數(shù)據(jù)庫(kù) 1將數(shù)據(jù)庫(kù)備份到備份設(shè)備先使用sp_addumpdevice創(chuàng)建備份設(shè)備，然后再使用BACKUP DATABASE備份數(shù)據(jù)庫(kù)。創(chuàng)建備份設(shè)備的語(yǔ)法格式如下：ap_addumpdevice 設(shè)備類(lèi)型,邏輯名,物理名備份數(shù)據(jù)庫(kù)的語(yǔ)法格式如下：BACKUP DATABASE 數(shù)據(jù)庫(kù)名 TO 備份設(shè)備（邏輯名）WITHNAME=備份的名稱(chēng),INIT|NOINIT例如，使用sp_addumpdevice創(chuàng)建數(shù)據(jù)庫(kù)備份設(shè)備BK

7、BACK，使用BACKUP DATABASE在該備份設(shè)備上創(chuàng)建books數(shù)據(jù)庫(kù)的完全備份，備份名為booksbak.。先在D盤(pán)上創(chuàng)建SQL文件夾，然后運(yùn)行以下命令：-使用sp_addumpdevice創(chuàng)建數(shù)據(jù)庫(kù)備份設(shè)備EXEC sp_addumpdevice disk,BKBACK,D:SQLbooksbak-EXEC sp_dropdevice BKBACK -執(zhí)行刪除該設(shè)備BACKUP DATABASE books to BKBACK WITH INIT,NAME=booksbak命令執(zhí)行結(jié)果如圖所示。2將數(shù)據(jù)庫(kù)備份到物理設(shè)備上直接將數(shù)據(jù)庫(kù)備份到物理設(shè)備上的語(yǔ)法格式如下：BACKUP DA

8、TABASE 數(shù)據(jù)庫(kù)名 TO 備份設(shè)備（物理名）WITHNAME=備份的名稱(chēng),INIT|NOINIT對(duì)于事務(wù)日志備份采用如下的語(yǔ)法格式：BACKUP LOG 數(shù)據(jù)庫(kù)名 TO 備份設(shè)備（邏輯名|物理名）WITHNAME=備份的名稱(chēng),INIT|NOINIT對(duì)于文件和文件組備份則采用如下的語(yǔ)法格式：BACKUP DATABASE 數(shù)據(jù)庫(kù)名 FILE=數(shù)據(jù)庫(kù)文件的邏輯名|FILEGROUP=數(shù)據(jù)庫(kù)文件組的邏輯名 TO 備份設(shè)備（邏輯名|物理名） WITHNAME=備份的名稱(chēng),INIT|NOINIT例如，使用BACKUP DATABASE直接將數(shù)據(jù)庫(kù)books的差異備份和日志備份到物理文件D:SQLDI

9、FFER.BAK上，備份名為differbak。BACKUP DATABASE books TO DISK=D:SQLDIFFER.BAK WITH DIFFERENTIAL,INIT,NAME=differbak -進(jìn)行數(shù)據(jù)庫(kù)差異備份BACKUP LOG books TO DISK=D:SQLDIFFER.BAK WITH NOINIT,NAME=differbak -進(jìn)行事務(wù)日志備份命令執(zhí)行結(jié)果如圖所示。課堂實(shí)踐1【任務(wù)1】使用SSMS完成以下操作： 創(chuàng)建邏輯名稱(chēng)為books01的備份設(shè)備，將對(duì)應(yīng)物理文件存放在系統(tǒng)默認(rèn)路徑。 對(duì)books數(shù)據(jù)庫(kù)進(jìn)行一次完整備份，備份到備份設(shè)備books01

10、中。 創(chuàng)建邏輯名稱(chēng)為books02的備份設(shè)備，將對(duì)應(yīng)物理文件存放在D:SQLbak中。 對(duì)books數(shù)據(jù)庫(kù)進(jìn)行一次差異備份，備份到備份設(shè)備books02中【任務(wù)2】使用T-SQL完成以上任務(wù)1的操作，要求保存完整操作的T-SQL語(yǔ)句。數(shù)據(jù)庫(kù)恢復(fù)一、數(shù)據(jù)庫(kù)恢復(fù)概述 （1）完整模型 （2）大容量日志模型 （3）簡(jiǎn)單模型 二、課堂案例2使用SSMS恢復(fù)數(shù)據(jù)庫(kù) 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)用管理平臺(tái)恢復(fù)數(shù)據(jù)庫(kù)。【案例知識(shí)要點(diǎn)】 還原數(shù)據(jù)庫(kù)，還原選項(xiàng)的設(shè)置。【案例完成步驟】（1）打開(kāi)SQL Server Management Studio的“對(duì)象資源管理器”，右擊【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)，在彈出的快捷菜單中選擇【還原數(shù)

11、據(jù)庫(kù)】，彈出如圖所示的【還原數(shù)據(jù)庫(kù)】對(duì)話框。（2）在【目標(biāo)數(shù)據(jù)庫(kù)】下拉列表框里可以選擇或輸入要還原的數(shù)據(jù)庫(kù)名。（3）如果備份文件或備份設(shè)備里的備份集很多，還可以選擇“目標(biāo)時(shí)間點(diǎn)”，只要有事務(wù)日志備份，就可以還原到某個(gè)時(shí)刻的數(shù)據(jù)庫(kù)狀態(tài)。在默認(rèn)情況下該項(xiàng)為“最近狀態(tài)”。（4）在【還原的源】選項(xiàng)組里，指定用于還原的備份集的源和位置。（5）在【選項(xiàng)】設(shè)置界面里可以設(shè)置如下內(nèi)容，如圖所示。（6）單擊【確定】按鈕，開(kāi)始執(zhí)行還原操作。三、使用T-SQL恢復(fù)數(shù)據(jù)庫(kù) 1.恢復(fù)數(shù)據(jù)庫(kù)恢復(fù)完全備份數(shù)據(jù)庫(kù)和差異備份數(shù)據(jù)庫(kù)的語(yǔ)法格式如下：RESTORE DATABASE 數(shù)據(jù)庫(kù)名 FROM 備份設(shè)備WITHFILE=n

12、,NORECOVERY|RECOVERY,REPLACE運(yùn)行以下命令：-進(jìn)行數(shù)據(jù)庫(kù)差異備份BACKUP DATABASE books TO BKBACKWITH DIFFERENTIAL,NAME=difBakGO-確保不再使用booksUSE master-還原數(shù)據(jù)庫(kù)完全備份RESTORE DATABASE books FROM BKBACKWITH FILE=1,NORECOVERY,REPLACE-還原數(shù)據(jù)庫(kù)的差異備份RESTORE DATABASE books FROM BKBACKWITH FILE=2,RECOVERYGO命令執(zhí)行結(jié)果如圖所示。2.恢復(fù)事務(wù)日志恢復(fù)事務(wù)日志采用下面的

13、語(yǔ)法格式：RESTORE LOG 數(shù)據(jù)庫(kù)名 FROM 備份設(shè)備WITHFILE=n,NORECOVERY|RECOVERY在上例的基礎(chǔ)上再進(jìn)行一次事務(wù)日志備份，然后使用RESTORE語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)備份的還原。-進(jìn)行數(shù)據(jù)庫(kù)事務(wù)日志備份BACKUP LOG books TO BKBACKWITH NOINIT, NAME=difBakGO-確保不再使用booksUSE master-還原數(shù)據(jù)庫(kù)完全備份RESTORE DATABASE books FROM BKBACKWITH FILE=1,NORECOVERY-還原數(shù)據(jù)庫(kù)的差異備份RESTORE DATABASE books FROM BKBAC

14、KWITH FILE=2,NORECOVERYRESTORE LOG books FROM BKBACKWITH FILE=3,RECOVERYGO3.恢復(fù)部分?jǐn)?shù)據(jù)庫(kù)通過(guò)從整個(gè)數(shù)據(jù)庫(kù)的備份中還原指定文件的用法，SQL Server提供了恢復(fù)部分?jǐn)?shù)據(jù)庫(kù)的功能。所用的語(yǔ)法格式如下：RESTORE DATABASE 數(shù)據(jù)庫(kù)名 FILE=文件名|FILEGROUP=文件組名 FROM 備份設(shè)備WITH PARTIAL,FILE= n,NORECOVERY,REPLACE4.恢復(fù)文件或文件組與文件或文件組備份相對(duì)應(yīng)的，有對(duì)指定文件或文件組的還原，其語(yǔ)法格式如下：RESTORE DATABASE 數(shù)據(jù)庫(kù)名

15、 FILE=文件名|FILEGROUP=文件組名 FROM 備份設(shè)備WITH FILE= n,NORECOVERY,REPLACE【任務(wù)1】使用SSMS完成以下操作。 刪除books數(shù)據(jù)庫(kù)中的Book表。 利用“課堂實(shí)踐1”中的備份books01恢復(fù)books數(shù)據(jù)庫(kù)到完整備份狀態(tài)。 利用“課堂實(shí)踐1”中的備份books02恢復(fù)books數(shù)據(jù)庫(kù)的事務(wù)日志。【任務(wù)2】使用T-SQL完成以上任務(wù)1的操作，要求保存完整操作的T-SQL語(yǔ)句。課堂實(shí)踐2數(shù)據(jù)庫(kù)的分離與附加一、分離和附加概述 可以分離某一SQL Server實(shí)例中的數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和事務(wù)日志文件，然后將它們重新附加到同一或其他SQL Ser

16、ver實(shí)例。 二、課堂案例3分離數(shù)據(jù)庫(kù) 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)用管理平臺(tái)和T-SQL分離books數(shù)據(jù)庫(kù)。【案例知識(shí)要點(diǎn)】 用SSMS分離數(shù)據(jù)庫(kù)的操作步驟，用T-SQL分離數(shù)據(jù)庫(kù)的語(yǔ)法格式和實(shí)現(xiàn)方法。【案例完成步驟】（1）打開(kāi)SQL Server Management Studio的“對(duì)象資源管理器”，展開(kāi)【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)。（2）右鍵單擊【books】，選擇【任務(wù)】【分離】。（3）打開(kāi)【分離數(shù)據(jù)庫(kù)】對(duì)話框，選擇要分離的數(shù)據(jù)庫(kù)books，并進(jìn)行相關(guān)設(shè)置。如圖所示。（4）單擊【確定】按鈕，完成數(shù)據(jù)庫(kù)的分離操作。使用存儲(chǔ)過(guò)程sp_detach_db可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的分離。但只有sysadmin固定服務(wù)器

17、角色的成員才能執(zhí)行sp_detach_db，其基本語(yǔ)法格式如下：sp_detach_db 數(shù)據(jù)庫(kù)名例如，使用T-SQL分離books數(shù)據(jù)庫(kù)。EXEC sp_detach_db books三、課堂案例4附加數(shù)據(jù)庫(kù) 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)用管理平臺(tái)和T-SQL附加books數(shù)據(jù)庫(kù)。【案例知識(shí)要點(diǎn)】 用SSMS附加數(shù)據(jù)庫(kù)的操作步驟，用T-SQL附加數(shù)據(jù)庫(kù)的語(yǔ)法格式和實(shí)現(xiàn)方法。【案例完成步驟】（1）打開(kāi)SQL Server Management Studio的“對(duì)象資源管理器”，右鍵單擊【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)，選擇【附加】。（2）打開(kāi)【附加數(shù)據(jù)庫(kù)】對(duì)話框，如圖所示。（3）單擊【添加】按鈕，打開(kāi)【定位數(shù)據(jù)庫(kù)文件

18、】對(duì)話框，選擇要附加的主要數(shù)據(jù)庫(kù)文件books.mdf，如圖所示。（4）單擊【確定】按鈕，完成數(shù)據(jù)庫(kù)的附加操作。數(shù)據(jù)庫(kù)附加成功后，在【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)中將會(huì)出現(xiàn)【books】數(shù)據(jù)庫(kù)節(jié)點(diǎn)。使用存儲(chǔ)過(guò)程sp_attach_db可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的附加。sp_attach_db 數(shù)據(jù)庫(kù)名,filename=文件名,16例如，使用T-SQL將D:SQLBook文件夾中的數(shù)據(jù)庫(kù)附加到當(dāng)前的SQL Server實(shí)例上。EXEC sp_attach_db books,D:SQLBookbooks.mdf, D:SQLBookbooks_log.ldf課堂實(shí)踐3 【任務(wù)1】使用SSMS完成以下操作。 將books數(shù)據(jù)

19、庫(kù)分離，并將對(duì)應(yīng)的數(shù)據(jù)庫(kù)文件復(fù)制到另一臺(tái)機(jī)器上。 將另一臺(tái)機(jī)器上的books數(shù)據(jù)庫(kù)文件附加到當(dāng)前的SQL Server實(shí)例。【任務(wù)2】使用T-SQL語(yǔ)句完成以上任務(wù)1中的操作，保存完成操作的T-SQL語(yǔ)句。數(shù)據(jù)導(dǎo)入與導(dǎo)出一、數(shù)據(jù)導(dǎo)入與導(dǎo)出概述 通過(guò)數(shù)據(jù)導(dǎo)入與導(dǎo)出操作可以完成在SQL Server 2005數(shù)據(jù)庫(kù)和其他類(lèi)型數(shù)據(jù)庫(kù)（如Excel表格、Access數(shù)據(jù)庫(kù)和Oracle數(shù)據(jù)庫(kù)）之間進(jìn)行數(shù)據(jù)的轉(zhuǎn)換，從而實(shí)現(xiàn)各種不同應(yīng)用系統(tǒng)之間的數(shù)據(jù)移植和共享。二、課堂案例5數(shù)據(jù)導(dǎo)出 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)SQL Server 2005數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)出方法，將books數(shù)據(jù)庫(kù)中的Book表數(shù)據(jù)導(dǎo)出到Acc

20、ess文件和Excel文件中。【案例知識(shí)要點(diǎn)】 SQL Server與Access的數(shù)據(jù)格式轉(zhuǎn)換，SQL Server與Excel的數(shù)據(jù)格式轉(zhuǎn)換。【案例完成步驟】（1）建立一個(gè)空的Access數(shù)據(jù)庫(kù)文件，例如D:SQLBook.mdb（2）啟動(dòng)SQL Server Management Studio，在“對(duì)象資源管理器”中依次展開(kāi)【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)，右鍵單擊【books】節(jié)點(diǎn)。 （3）在彈出的快捷菜單中選擇【任務(wù)】【導(dǎo)出數(shù)據(jù)】，將打開(kāi)“SQL Server導(dǎo)入和導(dǎo)出向?qū)А睂?duì)話框 。（4）單擊【下一步】按鈕，打開(kāi)如圖9-20所示的【選擇數(shù)據(jù)源】的向?qū)?duì)話框。在該對(duì)話框的【數(shù)據(jù)源】處選擇要導(dǎo)出的數(shù)據(jù)源

21、類(lèi)型。（5）單擊【下一步】按鈕，打開(kāi)如圖所示的對(duì)話框，在該對(duì)話框中設(shè)置目標(biāo)數(shù)據(jù)的參數(shù)。在【目標(biāo)】項(xiàng)中選擇“Microsoft Access”選項(xiàng)，在【文件名】項(xiàng)右鍵單擊【瀏覽】按鈕，在打開(kāi)的對(duì)話框中選擇已建立的Access數(shù)據(jù)庫(kù)文件“D:SQLBook.mdb”，如果Access沒(méi)有設(shè)置用戶(hù)名和密碼，則“用戶(hù)名”和“密碼”保留為空即可。（6）單擊【下一步】按鈕，將顯示如圖所示的【指定表復(fù)制或查詢(xún)】對(duì)話框。選擇“復(fù)制一個(gè)或多個(gè)表或視圖的數(shù)據(jù)”。（7）單擊【下一步】按鈕，顯示如圖所示的【選擇源表和源視圖】對(duì)話框。 （8）單擊【下一步】按鈕，顯示如圖所示的【保存并執(zhí)行包】對(duì)話框。 （9）在【目標(biāo)】中

22、選擇“Microsoft Excel”，表示將數(shù)據(jù)導(dǎo)出到Excel表格中，也可根據(jù)實(shí)際情況設(shè)置“Excle文件路徑”和選擇“Excel版本等項(xiàng)目” 。（10）單擊【下一步】，選擇“復(fù)制一個(gè)或多個(gè)表或視圖的數(shù)據(jù)”，也可以根據(jù)實(shí)際情況選擇“編寫(xiě)查詢(xún)以指定要傳輸?shù)臄?shù)據(jù)”。（11）單擊【下一步】，選擇表books.dbo. Employee。其他步驟同上。這樣就將SQL Server數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)出到Excel文件中了。在Excel中打開(kāi)目標(biāo)文件，便可以查看這些表。三、課堂案例6數(shù)據(jù)導(dǎo)入 【案例學(xué)習(xí)目標(biāo)】 學(xué)會(huì)SQL Server 2005數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入方法，從Access文件或Excel文件中導(dǎo)入數(shù)據(jù)到SQL Server 2005數(shù)據(jù)庫(kù)中。【案例知識(shí)要點(diǎn)】 Access與SQL Server的數(shù)據(jù)格式轉(zhuǎn)換，Excel與SQL Server的數(shù)據(jù)格式轉(zhuǎn)換。【案例完成步驟】（1）啟動(dòng)SQL Server Management Studio，在“對(duì)象資源管理器”中展開(kāi)【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)。（2）新建名為【booksData】的數(shù)據(jù)