数据备份、恢复和报表

1、第 11章 数据备份、恢复和报表 n数据库的导入导出数据库的导入导出 n备份概述备份概述 n备份类型备份类型 n创建备份设备创建备份设备 n数据库备份的执行数据库备份的执行 n恢复（还原）概述恢复（还原）概述 n恢复（还原）数据库概述恢复（还原）数据库概述 n恢复（还原）数据库恢复（还原）数据库 n数据库的导入导出数据库的导入导出 通过导入导出操作可以在SQL Server 2008和其他异类数据源(例 如Excel或Oracle数据库)之间轻松地移动数据。例如，可以将数据从 Excel应用程序导出到数据文件，然后将该数据文件导入到SQL Server表中。 导出是指将数据从SQL Serve

2、r表复制到数据文件中；导入是指将 数据从数据文件加载到SQL Server表中。 在SQL Server 2008中，可以将数据表中的数据在 SQL Server Management Studio中导出（两种途径进行导出）。操作步骤如下 图所示。 在“导入导出向导中”依次选择好数据源，导出的目标，要导出的具 体数据（可以根据需要是要基础数据还是综合数据），最后进行导出。 备份概述：备份概述： Microsoft SQL Server 2008提供了高性能的备份和还原机制。数提供了高性能的备份和还原机制。数 据库备份可以创建备份完成时数据库内存在的数据的副本，这个副本能据库备份可以创建备份完成

3、时数据库内存在的数据的副本，这个副本能 在遇到故障时恢复数据库。在遇到故障时恢复数据库。 这些故障包括：媒体故障，硬件故障，用户操作错误，自然灾害等。这些故障包括：媒体故障，硬件故障，用户操作错误，自然灾害等。 数据库备份对于例行的工作（例如，将数据库从一台服务器复制到另一数据库备份对于例行的工作（例如，将数据库从一台服务器复制到另一 台服务器、设置数据库镜像）也很有用。台服务器、设置数据库镜像）也很有用。 对对SQL Server数据库或事务日志进行备份时，数据库备份记录了在数据库或事务日志进行备份时，数据库备份记录了在 进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏进行备份

4、这一操作时数据库中所有数据的状态，以便在数据库遭到破坏 时能够及时地将其恢复。时能够及时地将其恢复。SQL Server备份数据库是动态的，在进行数据备份数据库是动态的，在进行数据 库备份时，库备份时，SQL Server允许其他用户继续对数据库进行操作。允许其他用户继续对数据库进行操作。 备份类型描述 完整备份完整备份完整备份包含所有数据文件和部分事务日志完整备份包含所有数据文件和部分事务日志 事务日志备份事务日志备份 所有数据库更改所有数据库更改 差异备份差异备份 备份上一次完整数据库备份之后被更改的数据库部分备份上一次完整数据库备份之后被更改的数据库部分 文件或文件组备份文件或文件组备份

5、 制定的文件或文件组（大型数据库完整备份不可取）制定的文件或文件组（大型数据库完整备份不可取） 备份类型：备份类型： 恢复模式 描述 简单恢复模式简单恢复模式 数据库的完整或差异副本数据库的完整或差异副本 截断事务日志截断事务日志 完整恢复模式完整恢复模式 包括数据库备份和事务日志备份包括数据库备份和事务日志备份 大容量日志恢复大容量日志恢复 包括数据库和事务日志备份包括数据库和事务日志备份, 但使用较少的日志空间但使用较少的日志空间 恢复模式中包含的备份类型：恢复模式中包含的备份类型： n以下情况应采用完整数据库备份 : q数据库较小 q数据库具有很少的数据修改操作或是只读数据 库时 n如果

6、采用完整数据库备份，则要定期清除事务日志 星期日星期日星期一星期一星期二星期二 完整数据库备份策略：完整数据库备份策略： n使用数据库和事务日志备份策略的场合: q修改频繁的数据库 q完整备份耗时太长 星期日星期日星期一星期一 数据库和事务日志备份策略：数据库和事务日志备份策略： n使用差异备份策略的场合: q数据库频繁修改 q需要进行最小化备份时 n单独备份事务日志 星期一星期一星期二星期二 增量（差异）备份策略：增量（差异）备份策略： n使用文件或文件组备份策略的场合: q超大型数据库 q完整备份时间太长 n单独备份事务日志 n可能管理比较复杂 星期日星期日星期一星期一星期二星期二星期三星

7、期三 数据文数据文 件件 1 数据文数据文 件件 2 数据文数据文 件件3 文件或文件组备份策略：文件或文件组备份策略： 多媒体：多媒体： 大批量日志备份选项大批量日志备份选项 在这个交互式多媒体中，企业的数据在这个交互式多媒体中，企业的数据 库每天将导入库每天将导入5百万条数据，这会给事务日百万条数据，这会给事务日 志备份带来很大负担。通过修改数据库选志备份带来很大负担。通过修改数据库选 项，可以解决这个问题。项，可以解决这个问题。 创建备份设备：创建备份设备： 备份或还原操作中使用的磁带机或磁盘驱动器称为备份或还原操作中使用的磁带机或磁盘驱动器称为“备份设备备份设备”。 在创建备份时，必须

8、选择要将数据写入的备份设备。在创建备份时，必须选择要将数据写入的备份设备。 Microsoft SQL Server 2008可以将数据库、事务日志和文件备份到磁盘和磁带设可以将数据库、事务日志和文件备份到磁盘和磁带设 备上。备上。 使用使用SQL Server 管理平台创建备份设备：管理平台创建备份设备： 在在SQL Server 管理平台中，选择想要创建备份设备的管理平台中，选择想要创建备份设备的服务器服务器， 打开打开服务器对象服务器对象文件夹。文件夹。 执行备份操作必须拥有对数据库备份的权限许可，执行备份操作必须拥有对数据库备份的权限许可，SQL Server只允许只允许 系统管理员、

9、数据库所有者和数据库备份执行者备份数据库。系统管理员、数据库所有者和数据库备份执行者备份数据库。 备份操作的权限：备份操作的权限： 然后弹出备份设备对话框，如下图所然后弹出备份设备对话框，如下图所 示。示。 在备份设备图标上右击，从弹出的快捷菜单在备份设备图标上右击，从弹出的快捷菜单 中选择中选择新建备份设备新建备份设备选项，如图所示。选项，如图所示。 使用系统存储过程创建备份设备：使用系统存储过程创建备份设备： 在在SQL Server 中，可以使用中，可以使用 sp_addumpdevice 语句创建备份设备，语句创建备份设备， 其语法形式如下：其语法形式如下： sp_addumpdevi

10、ce device_type ,logical_name,physical_name,controller_type|device_statu s 举例：在磁盘上创建了一个备份设备（举例：在磁盘上创建了一个备份设备（test_backup）。）。 程序清单如下：程序清单如下： USE master EXEC sp_addumpdevice disk, test_backup, C: Program Files Microsoft SQL Server MSSQL BACKUP test_backup.bak 数据库备份的执行：数据库备份的执行： 使用使用SQL Server 管理平台进行备份：

11、管理平台进行备份： 在在SQL Server 管理平台中，展开管理平台中，展开数据库数据库文件夹，右击所要进行文件夹，右击所要进行 备份的数据库备份的数据库图标，在弹出的快捷菜单中选择图标，在弹出的快捷菜单中选择任务任务选项，再选择选项，再选择 备份数据库备份数据库，如图所示。，如图所示。 出现出现SQL Server备份对话框，如图所示。图中有两个页框，即备份对话框，如图所示。图中有两个页框，即“常常 规规”和和“选项选项”页框。页框。 单击单击添加添加按钮可以选择将备份添按钮可以选择将备份添 加备份文件还是设备，如下图所示。加备份文件还是设备，如下图所示。 备份数据库的备份数据库的选项选项

12、页框，如图：页框，如图： 使用备份向导：使用备份向导： 在在SQL Server管理平台中，点击菜单中的模板资源管理器。展开管理平台中，点击菜单中的模板资源管理器。展开 backup，再双击，再双击backup database。 在在连接到数据库引擎连接到数据库引擎 对话框中，填写连接信息，再单击对话框中，填写连接信息，再单击连接连接。此时将打开一个新查询编。此时将打开一个新查询编 辑器窗口，其中包含辑器窗口，其中包含备份数据库备份数据库模板的内容，如图所示。模板的内容，如图所示。 按照按照backup database的语法规则，书写数据库备份的的语法规则，书写数据库备份的 sql 语句，

13、语句， 完成后执行此语句，即可完成数据库备份的操作。完成后执行此语句，即可完成数据库备份的操作。 Backup语句的语语句的语 法形式如下：法形式如下： Backup database database_name|database_name_var TO ,n WITH ,FORMAT , INIT | NOINIT ,RESTART :=backup_file_name|backup_file_evar|disk|tape =temp_file_name|temp_file_name_evar 举例：举例：创建一个名为创建一个名为 AdventureWorksBack 的命名备份设备，的命名

14、备份设备， 并执行完并执行完 整的数据库备份。整的数据库备份。 -创建一个名为创建一个名为 AdventureWorksBack 的命名备份设备的命名备份设备 -设备存储在设备存储在D:MyBachupDir 目录下目录下 -执行一个完整的数据库备份以备份到执行一个完整的数据库备份以备份到AdventureWorksBack文件中文件中 程序清单如下：程序清单如下： USE AdventureWorks EXEC sp_addumpdevice disk, AdventureWorksBack, D: MyBachupDir AdventureWorksBack.bak BACKUP DATA

15、BASE AdventureWorks TO AdventureWorksBack 1、BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH INIT 注意以下语句的不同之处：注意以下语句的不同之处： 2、BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH NOINIT 3、BACKUP DATABASE AdventureWorks TO DISK = D: MyBachupDir AdventureWorksBack.bak 举例：举例：为日志创建一个备份设备，并备

16、份为日志创建一个备份设备，并备份 AdventureWorks 数据库的事数据库的事 务日志。务日志。 -创建一个名为创建一个名为 AWBackLog的备份设备的备份设备,设备存储在设备存储在D:Bachup 目录下目录下 -备份备份 AdventureWorks 数据库的事务日志到备份设备数据库的事务日志到备份设备 程序清单如下：程序清单如下： USE AdventureWorks EXEC sp_addumpdevice disk, AWBackLog, D: Bachup AWBackLog.bak BACKUP LOG AdventureWorks TO AWBackLog 举例：举例

17、：在临时的备份文件上创建增量在临时的备份文件上创建增量 （差异）备份。（差异）备份。 - 在临时的备份磁盘文件上创建名为在临时的备份磁盘文件上创建名为MyDiffBackup的差异备份（的差异备份（ D: MyData ） 程序清单如下：程序清单如下： BACKUP DATABASE AdventureWorks TO DISK= D: MyData MyDiffBackup.bak WITH DIFFERENTIAL 举例：举例：用以下代码备份了数据库中某个文件组的用以下代码备份了数据库中某个文件组的Orders2文件。文件。 PhoneOrders数据库包含三个文件：数据库包含三个文件：

18、Orders1 、 Orders2和和Orders3 。 事务日志存储在事务日志存储在Orderlog文件中。已存在以下备份文件文件中。已存在以下备份文件OrderBackup1、 OrderBackup2、 OrderBackup3、 OrderBackupLog。 -备份数据库中某个文件组的备份数据库中某个文件组的 Orders2 文件以及数据库的日志文件。文件以及数据库的日志文件。 程序清单如下：程序清单如下： BACKUP DATABASE PhoneOrders FILE= Orders2 TO OrderBackup2 BACKUP LOG PhoneOrders TO Order

19、BackupLog 多媒体：进行完整备份多媒体：进行完整备份 在这个交互式多媒体中，你需要对数在这个交互式多媒体中，你需要对数 据库执行完整备份。你可以了解到通过据库执行完整备份。你可以了解到通过 SSMS如何建立备份设备，以及设置完整如何建立备份设备，以及设置完整 备份的选项。备份的选项。 多媒体：进行事务日志备份多媒体：进行事务日志备份 在这个交互式多媒体中，你需要对数在这个交互式多媒体中，你需要对数 据库执行事务日志备份。你可以了解到通据库执行事务日志备份。你可以了解到通 过过SSMS如何建立事务日志备份，以及设置如何建立事务日志备份，以及设置 事务日志备份的选项。事务日志备份的选项。

20、恢复（还原）概述：恢复（还原）概述： 场景：场景： 你所在的企业的数据库库拥有一套完整的备份策略：每周日晚上执你所在的企业的数据库库拥有一套完整的备份策略：每周日晚上执 行完整备份，每天晚上执行差异备份，每天行完整备份，每天晚上执行差异备份，每天10点、点、12点、点、14点和点和16点执点执 行事务日志备份。行事务日志备份。 某个周三的下午某个周三的下午13点，用户报告说不能访问数据库。作为点，用户报告说不能访问数据库。作为DBA，你，你 检查时发现数据库发生了严重的故障，存储数据库文件的硬盘无法工作。检查时发现数据库发生了严重的故障，存储数据库文件的硬盘无法工作。 这时你将会如何处理？这时

21、你将会如何处理？ 解决办法：解决办法： 你为服务器更换了新硬盘，使用存储在另外一个盘上的备份文件进行你为服务器更换了新硬盘，使用存储在另外一个盘上的备份文件进行 故障恢复。故障恢复。 先使用上周日晚的完整备份文件进行还原；再使用周二晚上的差异备先使用上周日晚的完整备份文件进行还原；再使用周二晚上的差异备 份文件进行还原；最后使用当天中午份文件进行还原；最后使用当天中午12点的事务日志备份进行了还原。点的事务日志备份进行了还原。 还原的工作方式：还原的工作方式： 还原是从备份中复制数据并将记录的事务应用于此数据，以使其前还原是从备份中复制数据并将记录的事务应用于此数据，以使其前 滚到目标恢复点的

22、过程。滚到目标恢复点的过程。 恢复是保持数据库一致性并使之处于可用状态的操作的完整集合。恢复是保持数据库一致性并使之处于可用状态的操作的完整集合。 使用使用 NORECOVERY 和和 RECOVERY 选项：选项： 还原期间的恢复状态由还原期间的恢复状态由 RESTORE 语句中的语句中的 NORECOVERY 和和 RECOVERY 选项决定。选项决定。 RECOVERY 选项是选项是SQL Server默认的选项。默认的选项。 当当 RECOVERY 选项与最后一个要还原的事务日志一起使用或用于选项与最后一个要还原的事务日志一起使用或用于 完整数据库还原时，在恢复完成后数据库可供用户使用

23、。完整数据库还原时，在恢复完成后数据库可供用户使用。 注意：注意： 若有额外的事务日志或必须还原的差异备份，则不能使用此选项。若有额外的事务日志或必须还原的差异备份，则不能使用此选项。 当要还原多个备份时，应使用当要还原多个备份时，应使用 NORECOVERY 选项。要为除最后选项。要为除最后 一个要还原的备份之外的所有备份指定一个要还原的备份之外的所有备份指定 NORECOVERY 选项。选项。 对于对于 NORECOVERY 选项，还原完数据库后，数据库才能为用户选项，还原完数据库后，数据库才能为用户 使用。使用。 恢复（还原）数据库概述：恢复（还原）数据库概述： 数据库备份后，一旦系统发

24、生崩溃或者执行了错误的数据库操作，数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作， 就可以从备份文件中还原数据库。就可以从备份文件中还原数据库。 数据库还原数据库还原是指将数据库备份加载到系统中的过程。系统在还原是指将数据库备份加载到系统中的过程。系统在还原 数据库的过程中，自动执行安全性检查、重建数据库结构以及完成填数据库的过程中，自动执行安全性检查、重建数据库结构以及完成填 写数据库内容。写数据库内容。 SQL Server还原数据库时，根据数据库备份文件自动创建数据库还原数据库时，根据数据库备份文件自动创建数据库 结构，并且还原数据库中的数据。结构，并且还原数据库中的数据。 由

25、于数据库的还原操作是静态的，所以在还原数据库时，必须限制由于数据库的还原操作是静态的，所以在还原数据库时，必须限制 用户对该数据库进行其他操作，因而在还原数据库之前，首先要设置数用户对该数据库进行其他操作，因而在还原数据库之前，首先要设置数 据库访问属性。如图所示。据库访问属性。如图所示。 使用使用SQL Server管理平台还原数据库：管理平台还原数据库： 打开打开 SQL Server 管理平台，在管理平台，在数据库数据库上单击鼠标右键，从弹上单击鼠标右键，从弹 出的快捷菜单中选择出的快捷菜单中选择任务任务选项，再选择选项，再选择还原数据库还原数据库命令，弹出命令，弹出 还原数据库对话框，

26、如图所示。还原数据库对话框，如图所示。 恢复（还原）数据库：恢复（还原）数据库： 选中选中选项选项页框，进行其他选项的设置，如图所示页框，进行其他选项的设置，如图所示 使用使用 T-SQL 语句还原数据库：语句还原数据库： Transact-SQL提供了 RESTORE 语句还原数据库，其语法形式如下： RESTORE database FROM WITH ,FILE=file_number ,MOVE logical_file_name TO operating_system_file_name ,REPLACE ,NORECOVERY|RECOVERY|STANDBY=undo_file_

27、name :=backup_device_name|backup_device_name_evar |disk|tape|pipe =temp_backup_device|temp_backup_device_var 举例：举例：从从 backup_company 备份设备中还原数据库备份设备中还原数据库 company。 程序清单如下：程序清单如下： RESTORE database company FROM backup_company 举例：举例：使用使用T-SQL 语句从完整备份和差异备份中还原。假设完整备份存语句从完整备份和差异备份中还原。假设完整备份存 在于在于 AWBack 备份

28、文件中，备份文件中，AWBackDiff 文件包含差异备份。文件包含差异备份。 - 从完整备份从完整备份AWBack 还原数据库还原数据库AdventureWorks， - 回滚未提交事务，使数据库处于用户可以使用的状态，无法还原其他回滚未提交事务，使数据库处于用户可以使用的状态，无法还原其他 事务日志。事务日志。 程序清单如下：程序清单如下：RESTORE DATABASE AdventureWorks FROM AWBack WITH NORECOVERY RESTORE DATABASE AdventureWorks FROM AWBackDiff WITH RECOVERY 还原事务日志的方法：还原事务日志的方法： 还原事务日志还原事务日志 将日志中的更改应用到数据库 在还原事务日志之前，先还原完整的数据库备份，再还原最近的差异在还原事务日志之前，先还原完整的数据库备份，再还原最近的差异 备份，后按顺序还原自上次完整或差异备份后的所有事务日志备份。备份，后按顺序还原自上次完整或差异备份后的所有事务日志备份。 在将被还原的最后的日志上使用在将被还原的最后的日志上使用 RECOVERY 选项选项 举例：举例：使用使用T-SQL 语句进行还原。假设一个备份文件语句进行还原。假设一个备份文件AWBack中存在完中存在完 整的数据库整的数据库Adventu