1、第11章 备份恢复与导入/导出,11.1 备份与恢复的基本概念 11.2 备份数据库 11.3 恢复数据库 11.4 导入与导出,Page 1,2019年3月11日星期一,数据库的备份与恢复是数据库管理中一项十分重要的工作，采用适当的备份策略增强数据备份的效果，能把数据损失控制在最小。本章主要介绍数据库的备份与恢复，同时也讲述了数据导入/导出的内容，以及实现不同数据系统间的数据交换与共享的方法。 通过学习本章，读者应掌握以下内容： 熟练掌握备份与恢复数据库的方法 掌握导入与导出数据的方法,11.1 备份与恢复的基本概念,11.1.1 备份与恢复的需求分析 在实际生活中，造成数据损失的因素有很多
2、，如存储介质错误、用户误操作、服务器的永久性毁坏等，这些都可以靠事先做好的备份来恢复原状。此外，数据的备份和恢复对于完成一些数据库操作也是很方便的。 数据库备份是复制数据库结构、对象和数据的副本，以便数据库遭受破坏时能够修复数据库。数据库恢复是指将备份的数据库再加载到数据库服务器中。 备份数据库，不但要备份用户数据库，也要备份系统数据库。因为系统数据库中存储了SQL Server的服务器配置信息、用户登录信息、用户数据库信息、作业信息等。,Page 3,2019年3月11日星期一,通常在下列情况下需要备份系统数据库。 (1) 修改master数据库之后。 (2) 修改msdb数据库之后。 (3
3、) 修改 model数据库之后。 通常在下列情况下需要备份用户数据库。 (1) 创建数据库之后。 (2) 创建索引之后。 (3) 清理事务日志之后。 (4) 执行大容量数据操作之后。,Page 4,2019年3月11日星期一,11.1.2 备份数据库的基本概念,备份是指将数据库复制到一个专门的备份服务器、活动磁盘或者其他能够长期存储数据的介质上，作为副本。 SQL Server支持在线备份，但是在备份过程中不允许执行以下操作。 (1) 创建或删除数据库文件。 (2) 创建索引。 (3) 执行非日志操作。 (4) 自动或手工缩小数据库或数据库文件大小。,Page 5,2019年3月11日星期一,
4、1数据库备份方式,SQL Server提供了4种数据库备份方式。 (1) 数据库完全备份(Database-complete)：备份数据库的所有数据文件、日志文件和在备份过程中发生的任何活动(将这些活动记录在事务日志中，一起写入备份设备)。完全备份是数据库恢复的基础，日志备份、差异备份的恢复完全依赖于在其前面进行的完全备份。 (2) 数据库差异备份(Database-differential)：差异备份只备份自最近一次完全备份以来被修改的那些数据。 (3) 事务日志备份(Transaction log)：只备份最后一次日志备份后所有的事务日志记录，备份所用的时间和空间更少。 (4) 文件或文件
5、组备份(File and Filegroup)：备份数据库文件或数据库文件组。该备份方式必须与事务日志备份配合执行才有意义。,Page 6,2019年3月11日星期一,2备份设备,SQL Server将数据库、数据库文件和日志文件备份到磁盘和磁带设备上。 1) 磁盘备份设备 2) 命名管道备份设备 3) 磁带备份设备 4) 物理和逻辑备份设备,Page 7,2019年3月11日星期一,11.1.3 数据库恢复的概念,数据库恢复是指将数据库备份重新加载到系统中的过程。 SQL Server所支持的备份是和恢复模式相关联的，不同的恢复模式决定了相应的备份策略。SQL Server提供了三种恢复模式
6、，即完整模式、大容量模式和简单模式，用户可以根据数据库应用的特点选择相应的恢复模式,Page 8,2019年3月11日星期一,(1) 完整模式。默认采用完整模式，它使用数据库备份和日志备份，能够较为完全地防范媒体故障。采用该模式，SQL Server事务日志会记录对数据进行的全部修改，包括大容量数据操作。因此，能够将数据库还原到特定的即时点。 (2) 大容量日志记录模式。该模型和完整模式类似，也是使用数据库备份和日志备份。不同的是，对大容量数据操作的记录，采用提供最佳性能和最少的日志空间方式。这样，事务日志只记录大容量操作的结果，而不记录操作的过程。所以，当出现故障时，虽然能够恢复全部的数据，
7、但是不能恢复数据库到特定的时间点。 (3) 简单模式。使用简单模式可以将数据库恢复到上一次的备份。事务日志不记录数据的修改操作，采用该模式，进行数据库备份时，不能进行“事务日志备份”和“文件/文件组备份”。对于小数据库或数据修改频率不高的数据库，通常采用简单模式。,Page 9,2019年3月11日星期一,11.2 备份数据库,11.2.1 使用SQL Server Management Studio备份数据库 在SQL Server Management Studio的“对象资源管理器”中，依次展开节点到要备份的数据库jxgl。 右击jxgl数据库，在弹出的快捷菜单中选择“任务”|“备份”命
8、令，出现如图11.1所示的窗口。,Page 10,2019年3月11日星期一,11.2.2 创建备份设备,进行数据库备份时，通常要先生成备份设备，如果不生成备份设备就要直接将数据备份到当前存储设备上。在SQL Server Management Studio的“对象资源管理器”中生成备份设备可以在数据库备份的集成环境下同时进行，也可以单独进行。,Page 11,2019年3月11日星期一,11.2.3 使用SQL语句备份数据库,使用SQL语句备份数据库，有两种方式：一种方式是先将一个物理设备建成一个备份设备，然后将数据库备份到该备份设备上；另一种方式是直接将数据库备份到物理设备上。 创建备份设
9、备的语法格式如下。 sp_addumpdevice 设备类型,逻辑名,物理名 各参数的含义如下。 (1) 设备类型：备份设备的类型，如果是以硬盘作为备份设备，则为“disk”。 (2) 逻辑名：备份设备的逻辑名称。 (3) 物理名：备份设备的物理名称，必须包括完整的路径。 方式二中，直接将数据库备份到物理设备上的语法格式如下。 BACKUP DATABASE 数据库名 TO备份设备(物理名)WITH NAME=备份的名称,INIT|NOINIT,Page 12,2019年3月11日星期一,备份数据库的语法格式如下。 BACKUP DATABASE 数据库名 TO 备份设备(逻辑名) WITHN
10、AME=备份的名称,INIT|NOINIT 各参数的含义如下。 (1) 备份设备：是由sp_addumpdevice创建的备份设备的逻辑名称，不要加引号。 (2) 备份的名称：是指生成的备份包的名称，例如图11.1中的“jxgl-完整 数据库 备份”。 (3) INIT：表示新的备份数据将覆盖备份设备上原来的备份数据。 (4) NOINIT：表示新备份的数据将追加到备份设备上已备份数据的后面。,Page 13,2019年3月11日星期一,对于日志备份采用如下的语法格式。 BACKUP LOG 数据库名 TO 备份设备(逻辑名|物理名)WITHNAME=备份的名称,INIT|NOINIT 对于文
11、件和文件组备份则采用如下的语法格式。 BACKUP DATABASE 数据库名FILE=数据库文件的逻辑名|FILEGROUP=数据库文件组的逻辑名TO 备份设备(逻辑名|物理名)WITHNAME=备份的名称,INIT|NOINIT,Page 14,2019年3月11日星期一,【例11.1】 使用sp_addumpdevice创建数据库备份设备SJBACK，使用BACKUP DATABASE在该备份设备上创建jxgl数据库的完全备份，备份名为jxglbak。 先在E盘上创建SQL文件夹，然后运行如下命令。 -使用sp_addumpdevice创建数据库备份设备 EXEC sp_addumpde
12、vice DISK,SJBACK,E:SQL2012 教材jxgl.bak -EXEC sp_dropdevice SJBACK -执行删除该设备 BACKUP DATABASE jxgl TO SJBACK WITH INIT,NAME=jxglbak,Page 15,2019年3月11日星期一,【例11.2】 使用BACKUP DATABASE直接将数据库jxgl的差异数据和日志备份到物理文件G:SQL 2012教材DIFFER.BAK上，备份名为differbak。 BACKUP DATABASE jxgl TO DISK=G:SQL 2012教材DIFFER.BAK WITH DIFF
13、ERENTIAL,INIT,NAME=differbak -进行差异备份 BACKUP LOG jxglTO DISK=G:SQL 2012教材DIFFER.BAKWITH NOINIT,NAME=differbak -进行事务日志备份,Page 16,2019年3月11日星期一,11.3 恢复数据库,恢复数据库就是将原来备份的数据库还原到当前的数据库中，通常是在当前的数据库出现故障或操作失误时进行。还原数据库时，SQL Server会自动将备份文件中的数据库备份全部还原到当前的数据库中，并回滚任何未完成的事务，以保证数据库中数据的一致性。,Page 17,2019年3月11日星期一,11.3
14、.1 恢复数据库前的准备,执行恢复操作之前，应当验证备份文件的有效性，确认备份中是否含有恢复数据库所需要的数据，然后关闭该数据库上的所有用户，备份事务日志。 1验证备份文件的有效性 通过SQL Server Management Studio的“对象资源管理器”可以查看备份设备的属性 使用SQL语句也可以获得备份媒体上的信息。使用RESTORE HEADERONLY语句， 获得指定备份文件中所有备份设备的文件首部信息。使用RESTORE FILELISTONLY语句，获得指定备份文件中的原数据库或事务日志的有关信息。使用RESTORE VERIFYONLY语句，检查备份集是否完整，以及所有卷是
15、否可读。,Page 18,2019年3月11日星期一,【例11.3】 使用SQL语句查看并验证备份文件的有效性。 -查看头信息 RESTORE HEADERONLY FROM DISK=G:SQL 2012教材DIFFER.BAK RESTORE HEADERONLY FROM SJBACK -查看文件列表 RESTORE FILELISTONLY FROM DISK=G:SQL 2012教材DIFFER.BAK RESTORE FILELISTONLY FROM SJBACK -验证有效性 RESTORE VERIFYONLY FROM DISK=G:SQL 2012教材DIFFER.BAK
16、 RESTORE VERIFYONLY FROM SJBACK,2断开用户与数据库的连接 恢复数据库之前，应当断开用户与该数据库的一切连接。所有用户都不准访问该数据库，执行恢复操作的用户也必须将连接的数据库更改为master数据库或其他数据库，否则不能启动还原任务。例如，使用USE master命令将连接数据库改为master。 3备份事务日志 在执行恢复操作之前，如果用户备份事务日志，将有助于保证数据的完整性，在数据库还原后可以使用备份的事务日志，进一步恢复数据库的最新操作。,Page 20,2019年3月11日星期一,11.3.2 使用SQL Server Management Studi
17、o恢复数据库,具体操作演示如下,Page 21,2019年3月11日星期一,11.3.3 使用SQL语句恢复数据库,1恢复数据库 恢复完全备份数据库和差异备份数据库的语法格式如下。 RESTORE DATABASE数据库名 FROM备份设备 WITHFILEn,NORECOVERY|RECOVERY,REPLACE 各参数的含义如下。 (1) FILEn表示从设备上的第几个备份中恢复。 (2) RECOVERY表示在数据库恢复完成后，SQL Server回滚被恢复的数据库中所有未完成的事务，以保持数据库的一致性。 (3) REPLACE表示要创建一个新的数据库，并将备份还原到这个新的数据库，如
18、果服务器上存在一个同名的数据库，则原来的数据库被删除。,Page 22,2019年3月11日星期一,【例11.4】 例11.1对数据库jxgl进行了一次完全备份，这里再进行一次差异备份，然后使用RESTORE DATABASE语句进行数据库备份的还原。 运行如下命令。 -进行数据库差异备份 BACKUP DATABASE jxgl TO SJBACK WITH DIFFERENTIAL, NAME=abBak GO -确保不再使用jxgl USE master -还原数据库完全备份 RESTORE DATABASE jxgl FROM SJBACK WITH FILE=1,NORECOVERY
19、 -还原数据库差异备份 RESTORE DATABASE jxgl FROM SJBACK WITH FILE=2,RECOVERY GO,Page 23,2019年3月11日星期一,2恢复事务日志,恢复事务日志采用下面的语法格式。 RESTORE LOG 数据库名 FROM 备份设备 WITHFILEn,NORECOVERY|RECOVERY 其中各选项的意义与恢复数据库中的相同。,Page 24,2019年3月11日星期一,【例11.5】 在例11.4的基础上再进行一次日志备份，然后使用RESTORE语句进行数据库的还原。 -进行数据库日志备份 BACKUP LOG jxgl TO SJB
20、ACK WITH NAME=abBak GO -确保不再使用jxgl USE master -还原数据库完全备份 RESTORE DATABASE jxgl FROM SJBACK WITH FILE=1,NORECOVERY -还原数据库差异备份 RESTORE DATABASE jxgl FROM SJBACK WITH FILE=2,NORECOVERY RESTORE LOG jxgl FROM SJBACK WITH FILE=3,RECOVERY GO,Page 25,2019年3月11日星期一,3恢复部分数据库,通过从整个数据库的备份中还原指定文件的方法，SQL Server提供
21、了恢复部分数据库的功能。所用的语法格式如下。 RESTORE DATABASE 数据库名 FILE=文件名| FILEGROUP=文件组名 FROM 备份设备 WITH PARTIAL,FILE=n,NORECOVERY,REPLACE 4恢复文件或文件组 和恢复文件或文件组备份相对应的，有对指定文件或文件组的还原，其语法格式如下。 RESTORE DATABASE 数据库名 FILE=文件名| FILEGROUP=文件组名 FROM 备份设备 WITH,FILE=n,NORECOVERY,REPLACE,Page 26,2019年3月11日星期一,11.4 导入与导出,SQL Server
22、2012提供了一个数据导入/导出的工具，这是一个向导程序，用于在不同的SQL Server服务器之间，以及SQL Server与其他类型的数据库或数据文件之间进行数据交换。这里主要是通过SQL Server数据库与Excel进行数据格式转换的实例，说明数据导入/导出工具的使用方法。,Page 27,2019年3月11日星期一,11.4.1 导出数据,【例11.6】 将jxgl数据库中，学生管理系统的部分数据表导出至Excel表中。 具体操作见演示,Page 28,2019年3月11日星期一,11.4.2 导入数据,【例11.7】 将例11.6建立的jxgl.xls文件中的工作表，导入jxgl数据库中。具体操作见演示,Page 29,2019年3月11日星期一,
