第 10 章 数据备份恢复与数据转换

1. 第10章 数据备份恢复与数据转换

2. 10.1 备份与恢复概述 10.2 数据库的恢复 10.3 数据库的维护 10.4 数据转换服务（DTS） 10.5 使用DTS设计器

3. 10.1 备份与恢复概述 恢复就是把遭受破坏、丢失的数据或出现错误的数据库恢复到原来的正常状态，这一状态是由备份决定的，不同的数据库备份类型，都应该个别采取不同的还原方法。就某种意义来说，数据库的还原比数据库的备份更加重要并困难。因为数据库备份是在正常的状态下进行，然而数据库还原则是在非正常的状态下进行，例如硬件故障、系统瘫痪以及操作疏忽等。 备份就是指对SQL SEVER数据库及其他相关信息进行复制，数据库备份能记录数据库中所有数据的当前状态，以便在数据库遭到破坏时能够将其恢复。

4. 完全数据库备份（Database-complete） 这是最完整的数据库备份方式，它会将数据库内所有的对象完整地复制到指定的设备上。由于它是备份完整内容，因此通常会需要花费较多的时间，同时也会占用较多的空间。对于数据量较少，或者变动较小不需经常备份的数据库而言，可以选择使用这种备份方式。 差异备份或称增量备份（Database-differential） 差异数据库备份只会针对自从上次完全备份后有变动的部分进行备份处理，这种备份模式必须搭配完全数据库备份一起使用，最初的备份使用完全备份保存完整的数据库内容，之后则使用差异备份只记录有变动的部分。由于差异数据库备份只备份有变动的部分，因此比起完全数据库备份来说，通常它的备份速度会比较快，占用的空间也会比较少。对于数据量大且需要经常备份的数据库，使用差异备份可以减少数据库备份的负担。 数据库文件和文件组备份（File and filegroup） 这种备份模式是以文件和文件组作为备份的对象，可以针对数据库特定的文件或特定文件组内的所有成员进行数据备份处理。不过在使用这种备份模式时，应该要搭配事务日志备份一起使用，因为当我们在数据库中还原部分的文件或文件组时，也必须还原事务日志，使得该文件能够与其他的文件保持数据一致性。 事务日志备份（Transaction log） 事务日志备份与差异数据库备份非常相似，都是备份部分数据内容，只不过事务日志备份是针对自从上次备份后有变动的部分进行备份处理，而不是针对上次完全备份后的变动。 3 1 4 2 10.1.1 数据库备份的类型

5. 10.1.2 备份设备的创建与删除 1、使用企业管理器创建与删除备份设备 在进行备份前首先必须指定或创建备份设备，备份设备是用来存储数据文件、事务日志文件和文件组备份的存储介质，可以是硬盘、磁带或管道。当使用磁盘作为备份设备时，SQL Server允许将本地主机硬盘和远程主机上的硬盘作为备份设备，备份在硬盘中以文件方式存储。 （1）在控制台根目录中依次展开“Microsoft SQL Server” →“SQL Server组” →“所使用的服务器” →“管理”→“备份”，鼠标右键单击“备份”节点（或从“操作”菜单中选择），选择“新建备份设备”，如图10-1所示。

6. （2）在弹出的“备份设备属性—新设备”对话框的名称中输入备份设备逻辑名，比如“DNXSBF”，文件名框中会自动生成包括默认路径的物理文件名，C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DNXSBF.BAK 如图10-2所示。 用户可以自行设置存放路径，单击“确定”即创建完成了备份设备“DNXSBF”。 注意 物理备份设备是指操作系统所标识的磁盘或磁带，如C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DNXSBF.BAK。逻辑备份设备是用来标识物理备份设备的别名或公用名称。 逻辑备份名称永久地存储在MASTER数据库下SYSYDEVICES系统表中。使用逻辑备份设备的优点是引用它比引用物理设备名称简单。 在企业管理器中删除备份设备与创建的过程类似，选中要删除的备份设备右键单击，在弹出的菜单中选择“删除”即可删除。

7. 注意 物理备份设备是指操作系统所标识的磁盘或磁带，如C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DNXSBF.BAK。逻辑备份设备是用来标识物理备份设备的别名或公用名称。 逻辑备份名称永久地存储在MASTER数据库下SYSYDEVICES系统表中。使用逻辑备份设备的优点是引用它比引用物理设备名称简单。 在企业管理器中删除备份设备与创建的过程类似，选中要删除的备份设备右键单击，在弹出的菜单中选择“删除”即可删除。

8. 2、使用系统存储过程sp_addumpdevice语句创建备份设备2、使用系统存储过程sp_addumpdevice语句创建备份设备 在SQL Server 中，可以使用sp_addumpdevice语句创建备份设备，语法格式为： [ EXECUTE ] sp_addumpdevice ‘设备类型’ [ , ‘ 设备逻辑名称’ ] [ , ‘物理名称’ ] [ , 管理员 | ‘验证设备’ ] 设备类型值可以是 DISK ，PIPE，TAPE。 【例10-1】在磁盘上创建一个磁盘备份设备 diskbackup。 在查询分析器中输入代码： Exec sp_addumpdevice 'disk' , 'diskbackup' , 'E:\DATA\diskbackup.bak' 运行后显示：“（所影响的行数为 1 行） '硬盘' 设备已添加。”，操作完成。

9. 3、使用系统存储过程sp_dropdevice语句删除备份设备3、使用系统存储过程sp_dropdevice语句删除备份设备 语法格式： [ EXECUTE ] sp_dropdevice ‘备份设备逻辑名’ [ , '物理名' ] 【例10-2】删除上面创建的磁盘备份设备diskbackup。 在查询分析器中输入代码： sp_dropdevice 'diskbackup'， 'E:\DATA\diskbackup.bak' 运行后显示：“设备已除去。”

10. 10.1.3 数据库的备份 1、使用企业管理器进行备份 在企业管理器中右键单击所要进行备份的数据库图标或节点，在弹出的快捷菜单中选择“所有任务”－>“备份数据库” 。随即弹出的“SQL Server备份”对话框中有“常规”和“选项”两个选项卡。如图10-3、10-4所示。 图10-3 “常规”选项卡 图10-4 “选项”选项卡

11. 2、使用Transact-SQL语句Backup备份数据库及事务日志2、使用Transact-SQL语句Backup备份数据库及事务日志 （1）数据库备份的语法格式： BACKUP BATABASE database_name | @database_name_var to < backup_device > [ , …n ] [ with [[,] format ][[,] init | noinit ][[,] restart ][[,]differential ] ] 【例10-3】创建磁盘备份设备(DNXS和DNXSDIFF)，分别对数据库diannaoxs执行完全备份和差异备份。 在查询分析器中输入代码： USE MASTER /*创建（完全）备份设备*/ EXEC sp_addumpdevice ‘DISK’, ‘ DNXS ’, ‘ E:\DATA\DNXS.DAT’ /*创建（差异）备份设备*/ EXEC sp_addumpdevice ‘DISK’, ‘ DNXSDIFF ’, ‘ E:\DATA\DNXSDIFF.DAT’ /*执行完全备份*/ BACKUP BATABASE diannaoxs to DNXS With NOINIT /*执行差异备份*/ BACKUP BATABASE diannaoxs to DNXSDIFF With differential GO

12. （2）事务日志备份的语法格式： BACKUP LOG database_name | @database_name_var to <backup_device> [, …n ] [WITH NO_TRUNCATE] [ [ , ] NO_LOG | TRUNCATE_ONLY ] 【例10-4】创建磁盘备份设备(DNXSLOG1)，对数据库diannaoxs事务日志进行备份。 在查询分析器中输入代码： USE MASTER EXEC sp_addumpdevice ‘DISK’, ‘DNXSLOG1’, ‘E:\DATA\DNXSLOG1.DAT’ BACKUP Log diannaoxs to DNXSLOG1 GO 此外，数据库的备份还有直接复制数据库文件MDF和日志文件LDF的方法。具体内容可参见数据库的分离与附加相关操作。

13. 10.2 数据库的恢复 10.2.1 数据库恢复策略 数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中恢复数据库，将数据库备份加载到系统中。数据库恢复模型有3种。 大容量日 志记录恢复 完全恢复 简单恢复

14. 表10-2 3种恢复模型的比较

15. （1）打开企业管理器，单击所用的服务器，在“工具”菜单中单击“还原数据库”命令。随即弹出的“还原数据库”对话框中有“常规”和“选项”两个选项卡。（1）打开企业管理器，单击所用的服务器，在“工具”菜单中单击“还原数据库”命令。随即弹出的“还原数据库”对话框中有“常规”和“选项”两个选项卡。 10.2.2 用企业管理器恢复数据库 • （2）在“常规”选项卡中，选择还原数据库的名称、还原类型，如图10-5所示。 • 在还原为数据库旁的下拉列表中选择要恢复的数据库； • 在还原文件组中时通过单击按钮来选择相应的数据库备份类型。 图10-5 数据库还原的设置

16. 10.2.3 用RESTORE命令恢复数据库 • 1、恢复数据库的RESTORE命令： • RESTORE DATABASE database_name | @database_name_var • [ from <backup_device [ , …n] > ] • [ with [ [,] file = { file_number | @file_number } ] • [ [,] move ‘logical_file_name’ to ‘operating_system_file_name’ ] [ [,] replace ] [ [,] norecovery | recovery | standby= undo_file_name ] ] 【例10．6】磁盘备份设备(DNXS)包含数据库diannaoxs的完全备份。磁盘备份设备(DNXSDIFF)包含数据库diannaoxs的差异备份。请还原数据库。 在查询分析器中输入代码： use master /*（1）从磁盘备份设备(DNXS)恢复完全数据库备份，使用NORECOVERY选项。*/ restore database diannaoxs from DNXS WITH NORECOVERY /*（2）从磁盘备份设备(DNXSDIFF)恢复差异数据库备份，使用RECOVERY选项。*/ restore database diannaoxs from DNXSDIFF WITH RECOVERY

17. 10.2.3 用RESTORE命令恢复数据库 2、恢复日志文件的RESTORE命令： RESTORE LOG { database_name | @database_name_var } [ FROM < backup_device > [ ,...n ] ] [ WITH [{ NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] [[ , ]STOPAT = { date_time | @date_time_var } | [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ] | [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ] ]] 包括三个附加的选项：STOPAT，STOPATMARK和STOPBEFOREMARK。STOPAT选项允许恢复数据库到精确的时刻状态，这个状态是在错误发生以前某一时间指定的特定一点。STOPAMARK 和STOPBEFOREMARK子句指定恢复到一个标记处。 【例10．7】对数据库diannaoxs的事务日志进行恢复。 在查询分析器中输入代码： use master RESTORE LOG FROM DNXSLOG1 WITH RECOVERY，STOPAT=’APR 15，2006 12：00 AM’

18. 10.2.4、恢复系统数据库 对MASTER数据库，通常进行完全数据库备份。恢复MASTER数据 库主要有两种途径：如果MASTER数据库已经严重损坏，但SQLSERVER可以启动， 则使用MASTER数据库当前备份来恢复备份；如果MASTER数据库已经严重损坏，SQLSERVER不能启动，或者MASTER数据库当前备份也不能使用，则必须执行“重建 MASTER库工具”来重建MASTER数据库，然后再使用备份来进行恢复。 （1）关闭SQL Server服务器， 在CMD下运行系统 安装目录下的bin子 目录下的rebuilem.exe 文件，这是个命令行程 序，运行后可以重 新创建系统数据库。 （2）系统数据 库重新建立后， 启动SQL Server。 （3）SQL Server 启动后系统数据库 是空的，可从备份数据库 中恢复。一般先恢复 master，再恢复 msdb，最后恢复 model。

19. 10.3 数据库的维护 利用数据库的维护计划向导可以方便地设置数据库的核心维护任务，以便于定期地执行这些任务。 10.3.1 创建数据库维护计划 在企业管理器中右键单击所要设置维护计划的数据库图标或节点，在弹出的快捷菜单中选择“所有任务”再选择“维护计划”。具体见书上图10-6至图10-16。

20. 10.3.2 编辑修改数据库维护计划 启动SQL Server企业管理器，在控制台根目录中依次展开“Microsoft SQL Server” →“SQL Server组” →“所使用的服务器” →“管理”→“数据库维护”。 鼠标双击“数据库维护计划”项目，或右键单击选择“属性”对话框，其中有“常规”“优化”“完整性”“完全备份”“事务日志备份”“报表”六个选项卡，可对数据库的维护计划进行设置、编辑和修改。如图10-17所示。 图10-17 数据库维护计划编辑修改对话框

21. 10.4 数据转换服务（DTS） 10.4.1 数据转换服务简介 SQL提供了一种把数据库从一个SQL Server数据库或非SQL Server 数据库（如ACCESS、ORACLE）传输到一个SQL Server 数据库的方法，这就是数据转换服务（DTS，DATA TRANSFORMAION SERVICES）。 DTS不仅可以把一个服务器上的所有对象和数据转移到另一个服务器上，还可以完成以下任务 数据转换 从其他的数据来源将数据传入SQL Server。 改变数据格式 合并原来多列数据为一列，合并多行数据为一行。 转换异种数据 重构数据 提供传输数据库的图形用户界面

22. 10.4.2 DTS导入/导出向导 数据转换 服务（DTS）导入 /导出向导为OLE DB数据源 之间复制及转换数据提供了最简单的方 法。DTS向导有“导出向导”和“导入向导”两种，分 别用来将数据从SQL Server 转换成其他数据格式，或者 从其他数据来源转入SQL Server。 保存、调度和 复制包。在完成数据导入导出后，可以将整个过程生成一个DTS包，在向导中来决定保存DTS包的格式，也可以使用SQL Server 代理调度包定期执行。 设置转换方式。选择将数据全部还是部分（如记录的一部分或列的一部分）数据复制到目的中。 设置数据源。在 导入数据时，需要选定外部数据对象。在导出数据时，数据源 就是本地SQL Server。

23. 10.4.2 DTS导入/导出向导操作步骤 开始菜单中，选择SQL Server程序组中的“导入导出数据”选项。 在SQL Server 企业管理器中，选择“工具”主菜单“数据转换服务”子菜单下的“导出数据”或“导入数据”。 1、启动DTS向导有多种方法可以打开DTS导入/导出向导。 选择某一数据库，单击鼠标右键，在快捷菜单中选择“所有任务”下的“导出数据“菜单项。

24. 2、设置数据源 大多数的 OLE DB 和 ODBC 数据源以及用户指定的 OLE DB 数据源。 文本文件。 从ODBC源导入，则选择MICROSOFT OLE DB 提供程序。 Oracle 和 Informix 数据库。 必须已经安装 Oracle 或 Informix 客户端软件。 Microsoft Excel 电子表格。 Microsoft Access 和 Microsoft FoxPro 数据库。 dBase 或 Paradox 数据库。

25. 如果使用SQL Server 作为直接数据源，则需要选择用于SQL Server的 Microsoft OLE DB 提供程序。此时需要输入SQL Server 身份验证信息，并选择所要使用的数据库。

26. 3、设置数据目的 在“选择目的”对话框中选择“Microsoft Access”，并在“文件名”文本框中输入所要保存的数据库地址。如果加密该数据库，可以在“用户名”和“密码”文本框中输入相应内容。

27. 4、设置转换方式 单击“下一步”，进入“指定表复制或查询”对话框，如图10-21所示。 图10-21设置转换方式

28. 从源数据库复制表和视图： 选中此项，则显示“选择源表和视图”对话框。如图10-22所示，可以将字段从数据源中选定的表或视图复制到指定的位置。但这种方式不会筛选或排列记录。 有3个选项，不同的选项对应不同的步骤和功能： 在SQL Server 数据库之间复制对象和数据： 只有源数据库和目的数据库都是SQL SEVER 数据库时，“在SQL Server 数据库之间复制对象和数据”选项才是可用的。如选中此项，则显示“选择要传输的对象”对话框。可传输的对象包括表、视图、存储过程、默认值、规则、约束、用户定义的数据类型、登录、用户、角色和索引。利用此项，可以实现数据库的迁移，如把一个数据库复制到服务器上，或把服务器存储的数据库复制到本地机上。 • 用一条查询指定要传输的数据： • 如选中此项，则显示“键入SQL语句”对话框。在其中可以生成用于在表或视图中查询特定行的SQL语句，只有符合查询条件的记录才可以复制。

29. 图10-22 选择源表和视图 图10-24 SQL Server 数据库之间复制对象和数据

30. 5、保存、调度和复制包 数据源、目的和转换的规则可以保存为DTS包，并调度该文件按预定的时间间隔运行，如图10-25所示。在该界面中的选项如下： 图10-25 保存、调度和复制包

31. 用复制方法发布目的数据: 将目的表用于复制。单击“用复制方法发布目的数据”选项 时，DTS 导入/导出向导结 束运行后将启动创建发布 向导。 立即运行: 设置完成后，立即运行转换并创建目的数据库。 调度 DTS 包以后执行: 将数据转换服务 (DTS) 包保存到 Microsoft SQL Server msdb 数据库、SQL Server 2000 Meta Data Services、COM 结构化存储文件或 Microsoft Visual Basic 文件，并调度该文件按预定义的时间间隔运行。单击浏览（"…"）按钮显示"编辑反复出现的作业调度"对话框，在其中可调度包的执行。如果没有修改调度，默认为每天午夜 12:00 运行包。 结构化存储文件: 将包保存为 COM 结构化存储文件。 如果希望在网络间复制、移动和发送软件包而不必将其存储在SQL Server 数据库中，可以使用该选项。 Visual Basic 文件: 将包保存为 Visual Basic 文件。 Title SQL Server : 将包保存到 SQL Server 中，存储在 msdb 数据库的 sysdtspackages 表中。 SQL Server Meta Data Services: 将包保存到 Meta Data Services。若打算追踪 Meta Data Services 的包版本、元数据和数据沿袭信息，则使用此选项。

32. 10.5 使用DTS设计器 编辑现有软件包。 DTS设计器以图形方式实现 DTS对象模型，可以用图形方式创建 DTS 软件包。使用 DTS设计器执行以下任务。 创建包括复杂工作流的软件包，这些工作流包括多个步骤，而这些步骤使用条件逻辑、事件驱动代码或与数据源的多种连接。 创建包含一个或多个步骤的简单软件包。

33. 10.5.1 DTS包结构 在DTS中，DTS包是整个DTS基础。可以认为DTS包是一个有组织的连接、DTS任务、DTS转换和工作流约束的集合。每一个DTS包都是包括一个或具有一定顺序的多个任务的工作流。 可以将 DTS 解决方案创建为一个或多个包。每个包都可能包含一组用来定义要执行工作的经过组织的任务、对数据和对象的转换、用来定义任务执行的工作流约束以及与数据源和目标的连接。DTS 包还提供了一些服务，例如记录包执行详细信息、控制事务和处理全局变量。 包是顶层对象，包括任务、工作流、连接三个对象。

34. 优先约束 说明 完成时（蓝色箭头） 如果想让任务 2 等待任务 1 完成（无论结果如何），则可以使用“完成时”优先约束将任务 1 链接到任务 2。 成功时（绿色箭头） 如果想让任务 2 等待任务 1 成功完成，则可以使用“成功时”优先约束将任务 1 链接到任务 2。 成功时（绿色箭头） 如果想让任务 2 只在任务 1 无法成功执行时开始执行，则可以使用“失败时”优先约束将任务 1 链接到任务 2。 （2）工作流：设置任务优先顺序 定义一组任务时，这些任务通常应该按照某种顺序执行。当任务按顺序执行时，每个任务就成为了过程中的一个步骤。在 DTS 设计器中，可以在 DTS 设计器设计表中操作任务，并使用优先约束控制任务的执行顺序。 优先约束按顺序链接包中的任务。下表显示了可以在 DTS 中使用的优先约束的类型。 • （1）任务：包中的定义步骤 • DTS 包通常包括一个或多个任务。每个任务都定义一个可在包执行过程中执行的工作项目。可以使用任务执行下列操作： • 转换数据 • 复制和管理数据 • 将任务作为软件包中的作业运行 DTS 包结构 • （3）连接：访问和移动数据 • 要成功执行复制和转换数据的 DTS 任务，DTS 软件包必须与它的源和目标数据以及所有其它数据源（如查找表）建立有效连接。 • 创建软件包时，通过从可用的 OLE DB 提供程序和 ODBC 驱动程序列表选择连接类型，可对连接进行配置。可用的连接类型包括： • Microsoft 数据访问组件 (MDAC) 驱动程序 • Microsoft Jet 驱动程序 • 其它驱动程序

35. 10.5.2 创建DTS包 1、启动DTS设计器 在SQL Server 企业管理器中展开“数据转换服务”菜单项，在“本地包”子项上右击，在快捷菜单中选择“新建包”菜单项，弹出“DTS包”界面，如图10-27所示。

36. 2、数据源和目的数据 建立包的首要任务就是建立数据源与目的的连接。在本节中将一个例子来说明如何使用DTS设计器。本例任务是将diannaoxs数据库导入到EXECL工作簿（电脑销售.XLS）中。 在设置数据源时，选择“连接”面板中的“”按钮，弹出“连接属性”对话框。如图10-28所示。在“新建连接”对话框中输入数据连接名称“diannaoxs”,并选择数据源类型（Microsoft OLE DB Provider for SQL Server）及数据库diannaoxs。 图10-28 设置数据源图 10-29 设置目的数据

37. 设置目的数据时，选择“连接”面板中的“”按钮，弹出“连接属性”对话框。如图10-29所示。在“新建连接”对话框中输入数据连接名称“电脑销售”,并选择数据源类型（Microsoft Excel 97-2000），然后选择对应的文件名（E:\数据库教材编写\电脑销售.xls）。 注：所选择的EXECL文件名必须是存在的，否则会报错。

38. 3、定义数据转换任务 • 在建立两个连接的基础上可以开始实现彼此间的数据转换操作。确定“DINNAOXS”为数据源，“电脑销售”为目的数据。单击“任务”面板上的 “”按钮，用出现的选择连接箭头分别单击数据源和目的数据图标，会在数据源和目的数据图标间出现一条表示转换数据任务的连接箭头，如图10-30所示。

39. 4、设置转换数据任务属性 在连接箭头上双击鼠标左键，弹出“转换数据任务属性”对话框。在“源”选项卡中设置，主要是选择要作为数据来源的表（如供货商表）。如图10-31所示。 图10-31 设置任务 的“源”属性

40. 在“目的” 选项卡中选择数据转换的目的地。单击“创建”按纽，在弹出的“创建目的表”对话框中输入用于创建表的SQL语句。如图10-32所示。 图10-32 设置任务的 “目的”属性

41. 在“转换” 选项卡中设置要进行的转换（设置数据源与目的数据之间的对应关系）。如图10-34所示。 图10-34 设置任务的“转换”属性 如果出现图10-33 “转换验证”对话框，是因为数据源表和目的数据表的字段不一样，系统无法自动建立映射关系，所以需要给出验证信息。

42. 10.5.3 包的保存与运行 1、保存包 在设置完整个包后，可以选择“包”菜单下的“保存”菜单项来保存包。输入包名称“电脑销售电子表”，选择所要保存的位置。如图10-35所示。 2、执行包 在“包”菜单下选择“执行”菜单项，就可以运行当前的包。此时弹出一个对话框，来显示执行的进程，如图10-36所示。 图10-36 执行包 图10-35 保存包