（中职）数据库管理与应用案例教程09-Chap09课件

1、（中职）数据库管理与应用案例教程09-Chap09ppt课件数据库管理与应用实例教程彭勇 主编第9章 数据库的管理 本章导航本章的主要内容及其在SQL Server2005数据库管理系统中的位置如图示。 SQL server2005基础数据模型数据库技术概述数据库的创建数据库的管理数据库基础视图索引表存储过程触发器数据库对象子查询连接查询简单查询联合查询查询技术数据库备份与恢复数据库的安全游标、事务和锁数据库分享与附加数据库导入与导出数据库安全与管理数据库设计和应用程序开发学习要点（1）数据库备份与恢复的基本概念（2）数据库备份设备的管理（3）数据库备份与恢复的方法（4）数据库分离与附加的方法

2、（5）数据导入与导出的方法数据库备份一、数据库备份概述 SQL Server提供了四种数据库备份方式，用户可以根据自己的备份策略选择不同的备份方式。（1）数据库完全备份（Database-complete） （2）数据库差异备份（Database-differential） （3）事务日志备份（Transaction log） （4）文件或文件组备份（File and Filegroup） SQL Server支持在线备份，因此，通常情况下可以一边进行备份，一边进行其他操作。但是，在备份过程中不允许执行以下操作:创建或删除数据库文件。创建索引。执行非日志操作。自动或手工缩小数据库或数据库文件大

3、小。二、备份设备介绍 1磁盘备份设备2命名管理设备3磁带设备4物理备份和逻辑备份设备 5使用T-SQL管理备份设备 （1）创建备份设备使用存储过程sp_addumpdevice创建备份设备，其语法格式如下：sp_addumpdevice device_type,logical_name,physical_name（2）查看备份设备使用存储过程sp_helpdevice语句查看备份设备信息，其语法格式如下：sp_helpdevice name（3）删除备份设备使用存储过程sp_dropdevice语句删除备份设备，其语法格式如下：sp_dropdevice device,delfile三、课堂案

4、例1使用SSMS备份数据库 【案例学习目标】 学会用管理平台备份books数据库。【案例知识要点】 创建备份设备，备份数据库。【案例完成步骤】（1）展开SQL Server Management Studio工具，在“对象资源管理器”中，展开【服务器对象】树形目录，右键单击【备份设备】，如图所示。 （2）在弹出的快捷菜单中选择【新建备份设备】命令，将弹出【备份设备】窗口，如图所示。（3）在【设备名称】文本框中输入备份设备的名称：books_backup。（4）在【文件】文本框中输入备份设备的路径和文件名：D:SQLbooks_backup.bak。（5）单击【确定】按钮，开始创建备份设备操作。

5、（6）在“对象资源管理器”中，依次展开节点到要备份的数据库books。（7）右击【books】数据库，在弹出的快捷菜单中选择【任务】【备份】命令，如图所示。（8）打开如图所示的对话框。在【名称】文本框内，输入备份名称。默认为“books-完整 数据库 备份”，如果需要，在【说明】文本框中输入对备份集的描述。默认没有任何描述。（9）在【备份类型】下拉列表框选择备份的方式。其中，“完整”：执行完整的数据库备份；“差异”：仅备份自上次完全备份以后，数据库中新修改的数据；“事务日志”：仅备份事务日志。（10）指定备份目的。 在【目标】选项组中单击【添加】按钮，并在如图所示的【选择备份目标】对话框中，指

6、定一个备份文件名。 （11）返回到【备份数据库】对话框后，单击【确定】按钮，开始执行备份操作，此时会出现相应的提示信息。单击【确定】按钮，完成数据库备份。四、使用T-SQL备份数据库 1将数据库备份到备份设备先使用sp_addumpdevice创建备份设备，然后再使用BACKUP DATABASE备份数据库。创建备份设备的语法格式如下：ap_addumpdevice 设备类型,逻辑名,物理名备份数据库的语法格式如下：BACKUP DATABASE 数据库名 TO 备份设备（逻辑名）WITHNAME=备份的名称,INIT|NOINIT例如，使用sp_addumpdevice创建数据库备份设备BK

7、BACK，使用BACKUP DATABASE在该备份设备上创建books数据库的完全备份，备份名为booksbak.。先在D盘上创建SQL文件夹，然后运行以下命令：-使用sp_addumpdevice创建数据库备份设备EXEC sp_addumpdevice disk,BKBACK,D:SQLbooksbak-EXEC sp_dropdevice BKBACK -执行删除该设备BACKUP DATABASE books to BKBACK WITH INIT,NAME=booksbak命令执行结果如图所示。2将数据库备份到物理设备上直接将数据库备份到物理设备上的语法格式如下：BACKUP DA

8、TABASE 数据库名 TO 备份设备（物理名）WITHNAME=备份的名称,INIT|NOINIT对于事务日志备份采用如下的语法格式：BACKUP LOG 数据库名 TO 备份设备（逻辑名|物理名）WITHNAME=备份的名称,INIT|NOINIT对于文件和文件组备份则采用如下的语法格式：BACKUP DATABASE 数据库名 FILE=数据库文件的逻辑名|FILEGROUP=数据库文件组的逻辑名 TO 备份设备（逻辑名|物理名） WITHNAME=备份的名称,INIT|NOINIT例如，使用BACKUP DATABASE直接将数据库books的差异备份和日志备份到物理文件D:SQLDI

9、FFER.BAK上，备份名为differbak。BACKUP DATABASE books TO DISK=D:SQLDIFFER.BAK WITH DIFFERENTIAL,INIT,NAME=differbak -进行数据库差异备份BACKUP LOG books TO DISK=D:SQLDIFFER.BAK WITH NOINIT,NAME=differbak -进行事务日志备份命令执行结果如图所示。课堂实践1【任务1】使用SSMS完成以下操作： 创建逻辑名称为books01的备份设备，将对应物理文件存放在系统默认路径。 对books数据库进行一次完整备份，备份到备份设备books01

10、中。 创建逻辑名称为books02的备份设备，将对应物理文件存放在D:SQLbak中。 对books数据库进行一次差异备份，备份到备份设备books02中【任务2】使用T-SQL完成以上任务1的操作，要求保存完整操作的T-SQL语句。数据库恢复一、数据库恢复概述 （1）完整模型 （2）大容量日志模型 （3）简单模型 二、课堂案例2使用SSMS恢复数据库 【案例学习目标】 学会用管理平台恢复数据库。【案例知识要点】 还原数据库，还原选项的设置。【案例完成步骤】（1）打开SQL Server Management Studio的“对象资源管理器”，右击【数据库】节点，在弹出的快捷菜单中选择【还原数

11、据库】，弹出如图所示的【还原数据库】对话框。（2）在【目标数据库】下拉列表框里可以选择或输入要还原的数据库名。（3）如果备份文件或备份设备里的备份集很多，还可以选择“目标时间点”，只要有事务日志备份，就可以还原到某个时刻的数据库状态。在默认情况下该项为“最近状态”。（4）在【还原的源】选项组里，指定用于还原的备份集的源和位置。（5）在【选项】设置界面里可以设置如下内容，如图所示。（6）单击【确定】按钮，开始执行还原操作。三、使用T-SQL恢复数据库 1.恢复数据库恢复完全备份数据库和差异备份数据库的语法格式如下：RESTORE DATABASE 数据库名 FROM 备份设备WITHFILE=n

12、,NORECOVERY|RECOVERY,REPLACE运行以下命令：-进行数据库差异备份BACKUP DATABASE books TO BKBACKWITH DIFFERENTIAL,NAME=difBakGO-确保不再使用booksUSE master-还原数据库完全备份RESTORE DATABASE books FROM BKBACKWITH FILE=1,NORECOVERY,REPLACE-还原数据库的差异备份RESTORE DATABASE books FROM BKBACKWITH FILE=2,RECOVERYGO命令执行结果如图所示。2.恢复事务日志恢复事务日志采用下面的

13、语法格式：RESTORE LOG 数据库名 FROM 备份设备WITHFILE=n,NORECOVERY|RECOVERY在上例的基础上再进行一次事务日志备份，然后使用RESTORE语句进行数据库备份的还原。-进行数据库事务日志备份BACKUP LOG books TO BKBACKWITH NOINIT, NAME=difBakGO-确保不再使用booksUSE master-还原数据库完全备份RESTORE DATABASE books FROM BKBACKWITH FILE=1,NORECOVERY-还原数据库的差异备份RESTORE DATABASE books FROM BKBAC

14、KWITH FILE=2,NORECOVERYRESTORE LOG books FROM BKBACKWITH FILE=3,RECOVERYGO3.恢复部分数据库通过从整个数据库的备份中还原指定文件的用法，SQL Server提供了恢复部分数据库的功能。所用的语法格式如下：RESTORE DATABASE 数据库名 FILE=文件名|FILEGROUP=文件组名 FROM 备份设备WITH PARTIAL,FILE= n,NORECOVERY,REPLACE4.恢复文件或文件组与文件或文件组备份相对应的，有对指定文件或文件组的还原，其语法格式如下：RESTORE DATABASE 数据库名

15、 FILE=文件名|FILEGROUP=文件组名 FROM 备份设备WITH FILE= n,NORECOVERY,REPLACE【任务1】使用SSMS完成以下操作。 删除books数据库中的Book表。 利用“课堂实践1”中的备份books01恢复books数据库到完整备份状态。 利用“课堂实践1”中的备份books02恢复books数据库的事务日志。【任务2】使用T-SQL完成以上任务1的操作，要求保存完整操作的T-SQL语句。课堂实践2数据库的分离与附加一、分离和附加概述 可以分离某一SQL Server实例中的数据库的数据文件和事务日志文件，然后将它们重新附加到同一或其他SQL Ser

16、ver实例。 二、课堂案例3分离数据库 【案例学习目标】 学会用管理平台和T-SQL分离books数据库。【案例知识要点】 用SSMS分离数据库的操作步骤，用T-SQL分离数据库的语法格式和实现方法。【案例完成步骤】（1）打开SQL Server Management Studio的“对象资源管理器”，展开【数据库】节点。（2）右键单击【books】，选择【任务】【分离】。（3）打开【分离数据库】对话框，选择要分离的数据库books，并进行相关设置。如图所示。（4）单击【确定】按钮，完成数据库的分离操作。使用存储过程sp_detach_db可以实现数据库的分离。但只有sysadmin固定服务器

17、角色的成员才能执行sp_detach_db，其基本语法格式如下：sp_detach_db 数据库名例如，使用T-SQL分离books数据库。EXEC sp_detach_db books三、课堂案例4附加数据库 【案例学习目标】 学会用管理平台和T-SQL附加books数据库。【案例知识要点】 用SSMS附加数据库的操作步骤，用T-SQL附加数据库的语法格式和实现方法。【案例完成步骤】（1）打开SQL Server Management Studio的“对象资源管理器”，右键单击【数据库】节点，选择【附加】。（2）打开【附加数据库】对话框，如图所示。（3）单击【添加】按钮，打开【定位数据库文件

18、】对话框，选择要附加的主要数据库文件books.mdf，如图所示。（4）单击【确定】按钮，完成数据库的附加操作。数据库附加成功后，在【数据库】节点中将会出现【books】数据库节点。使用存储过程sp_attach_db可以实现数据库的附加。sp_attach_db 数据库名,filename=文件名,16例如，使用T-SQL将D:SQLBook文件夹中的数据库附加到当前的SQL Server实例上。EXEC sp_attach_db books,D:SQLBookbooks.mdf, D:SQLBookbooks_log.ldf课堂实践3 【任务1】使用SSMS完成以下操作。 将books数据

19、库分离，并将对应的数据库文件复制到另一台机器上。 将另一台机器上的books数据库文件附加到当前的SQL Server实例。【任务2】使用T-SQL语句完成以上任务1中的操作，保存完成操作的T-SQL语句。数据导入与导出一、数据导入与导出概述 通过数据导入与导出操作可以完成在SQL Server 2005数据库和其他类型数据库（如Excel表格、Access数据库和Oracle数据库）之间进行数据的转换，从而实现各种不同应用系统之间的数据移植和共享。二、课堂案例5数据导出 【案例学习目标】 学会SQL Server 2005数据库的数据导出方法，将books数据库中的Book表数据导出到Acc

20、ess文件和Excel文件中。【案例知识要点】 SQL Server与Access的数据格式转换，SQL Server与Excel的数据格式转换。【案例完成步骤】（1）建立一个空的Access数据库文件，例如D:SQLBook.mdb（2）启动SQL Server Management Studio，在“对象资源管理器”中依次展开【数据库】节点，右键单击【books】节点。 （3）在弹出的快捷菜单中选择【任务】【导出数据】，将打开“SQL Server导入和导出向导”对话框 。（4）单击【下一步】按钮，打开如图9-20所示的【选择数据源】的向导对话框。在该对话框的【数据源】处选择要导出的数据源

21、类型。（5）单击【下一步】按钮，打开如图所示的对话框，在该对话框中设置目标数据的参数。在【目标】项中选择“Microsoft Access”选项，在【文件名】项右键单击【浏览】按钮，在打开的对话框中选择已建立的Access数据库文件“D:SQLBook.mdb”，如果Access没有设置用户名和密码，则“用户名”和“密码”保留为空即可。（6）单击【下一步】按钮，将显示如图所示的【指定表复制或查询】对话框。选择“复制一个或多个表或视图的数据”。（7）单击【下一步】按钮，显示如图所示的【选择源表和源视图】对话框。 （8）单击【下一步】按钮，显示如图所示的【保存并执行包】对话框。 （9）在【目标】中

22、选择“Microsoft Excel”，表示将数据导出到Excel表格中，也可根据实际情况设置“Excle文件路径”和选择“Excel版本等项目” 。（10）单击【下一步】，选择“复制一个或多个表或视图的数据”，也可以根据实际情况选择“编写查询以指定要传输的数据”。（11）单击【下一步】，选择表books.dbo. Employee。其他步骤同上。这样就将SQL Server数据库中的数据导出到Excel文件中了。在Excel中打开目标文件，便可以查看这些表。三、课堂案例6数据导入 【案例学习目标】 学会SQL Server 2005数据库的数据导入方法，从Access文件或Excel文件中导入数据到SQL Server 2005数据库中。【案例知识要点】 Access与SQL Server的数据格式转换，Excel与SQL Server的数据格式转换。【案例完成步骤】（1）启动SQL Server Management Studio，在“对象资源管理器”中展开【数据库】节点。（2）新建名为【booksData】的数据