SQL-Server实用数据库技术课件第11章

SQLServer应用系统开发第十一章数据备份恢复与数据转换授课教师：张新成课程回顾SQLServer2008的安全机制SQLServer2008的身份验证模式账户、用户、架构、角色、权限等相关概念用户管理、架构管理、角色管理和权限管理本章综述尽管SQLServer2008采取多种措施保证数据库的安全性和完整性，但大到自然灾害，小到病毒入侵、硬件故障、人为操作疏忽或者恶意破坏仍然可能发生，这些故障会导致数据的丢失或破坏，甚至造成灾难性的后果。为了在数据库遭到破坏时能够及时恢复，将损失降到最低，需要定期对数据库进行备份。因此，数据库的备份与恢复对于保证系统的安全性和完整性具有重要的作用。数据库恢复策略使用SSMS进行恢复使用RESTORE语句进行恢复11.2数据库的恢复

备份概述备份设备的创建使用SSMS进行备份使用BACKUP语句进行备份11.1数据库的备份

DTS概述数据的导入数据的导出11.3数据转换服务

本章结构技能展示了解备份和恢复掌握备份设备的创建掌握数据库备份的实施掌握数据库恢复的实施掌握数据的导入/出11.1数据库的备份 11.1.1备份概述11.1.2备份设备的创建11.1.3使用SSMS进行备份11.1.4使用BACKUP语句备份11.1.1备份概述备份是数据的副本，用于在数据库遭到破坏后还原和恢复数据。数据库的破坏难以预测，硬件故障、人为误操作、病毒入侵，以及地震、火灾、战争、盗窃等都可能导致数据的丢失或破坏，甚至造成灾难性的后果。因此，数据备份是一项不容忽视的系统管理工作。完整备份差异备份事务日志备份备份类型文件和文件组备份1.完整备份

完整备份指备份整个数据库，包括表、视图、存储过程等数据库对象以及事务日志。完整备份的优点是操作简单，但由于备份完整内容，因此通常需要花费较多的时间，同时也会占用较多的空间。如果数据库规模较小，则可以使用此备份方式。完整备份是其他备份的基础。在进行其他类型的备份前，首先必须进行完整备份。

2.差异备份

差异备份是完整备份的补充，只备份上次备份后更改的数据。与完整备份相比，差异备份的备份速度较快，占用的空间也较少。对于数据量大且频繁修改的数据库，可以选择差异备份

。

3.事务日志备份

事务日志备份只备份事务日志的内容。使用该方式，可以将数据库恢复到故障点或特定的时间点。事务日志备份与差异备份相似，都是只备份部分数据。区别在于，如果上次完整备份后，数据库中的某行被修改多次，则事务日志备份包含该行所有被更改的历史记录，而差异备份只包含该行的最后一组值

。

4.文件和文件组备份

只备份数据库中的某些文件或文件组。该方式适用于超大型数据库，可以将大型数据库的备份分多次进行，避免数据库备份的时间过长。而且当数据库里的某个或某些文件损坏时，该方式可以使用户仅还原已损坏的文件，而不必还原数据库的其余部分，从而提高恢复速度，减少恢复时间。11.1.2备份设备的创建备份设备是用来存储数据文件、事务日志文件和文件组备份的存储介质。常见的备份设备包括磁盘、磁带或管道。备份设备在磁盘中以文件的方式存储

。在进行备份前，首先必须创建或指定备份设备。11.1.2

备份设备的创建

使用SSMS使用系统存储过程1.使用SSMS创建备份设备【例11-1】创建“总务管理系统”备份设备打开SSMS，在对象资源管理器窗口中，展开【服务器对象】节点，如图11-1所示

图11-1

右击【备份设备】节点，选择【新建备份设备】命令，打开如图11-1所示的【备份设备】窗口

在【设备名称】文本框中输入总务管理系统，在【文件】中选择备份设备路径单击【确定】按钮完成备份设备的创建

,如图11-2所示

图11-22.使用系统存储过程sp_addumpdevice创建备份设备

语法格式

[execute]sp_addumpdevice

‘设备类型’,‘设备逻辑名称’,‘设备物理名称’

【例11-2】在磁盘上创建一个名为firstdisk的备份设备

。execsp_addumpdevice'disk','firstdisk',‘D:\cx\SQL\firstdisk.bak‘备份设备创建后，并没有实际生成该文件。只有在实际执行备份操作,并在备份设备存储备份内容之后，该文件才会生成11.1.3使用SSMS进行备份

【例11-3】对数据库build进行完整备份。打开SSMS，在对象资源管理器窗口中展开【数据库】节点，右击build数据库，选择【任务】|【备份】命令，打开如图11-3所示的【备份数据库】窗口

图11-3

在【数据库】下拉列表框中选择build数据库；在【备份类型】下拉列表框中选择【完整】选项。设置备份到磁盘的目标位置，单击【删除】按钮，删除默认的备份文件再单击【添加】按钮，打开如图11-4所示的【选择备份目标】对话框

图11-4

在【选择备份目标】对话框中选中【备份设备】单选按钮，从下拉列表中选择【总务管理系统】选项。打开【选项】页面，选中【覆盖所有现有备份集】单选按钮，并选中【完成后验证备份】复选框，如图11-5所示

图11-5

在完成build数据库备份后，可以对备份进行检查，具体步骤如下：

在对象资源管理器中，展开【服务器对象】节点下的【备份设备】节点右击备份设备【总务管理系统】，选择【属性】命令，打开【备份设备】窗口选择【媒体内容】选项，打开【媒体内容】页面，可以看到刚创建的build数据库的完整备份，如图11-6所示

图11-611.1.4使用BACkUP语句进行备份

数据库备份的语法格式：

Backupdatabasedatabase_nameTobackup_device[with[name=backup_set_name][description=‘text’][differential][init|oninit][compression|no_compression]参数说明database_name：指定要备份的数据库的名称。backup_device：指定备份操作使用的备份设备名With：指定备份选项；name：指定备份的名称；description：指定备份的描述；differential：表示以差异备份的方式备份数据库init|noinit：init表示新备份的数据覆盖当前备份设备上的内容；noinit表示新备份的数据追加到备份设备已有的内容后面；compression|no_compression：表示是否启用备份压缩功能

【例11-4】为数据库build进行完整备份

use

buildbackupdatabasebuild

to总务管理系统withinitname=‘build完整备份’init选项使新备份的数据覆盖当前备份设备上的内容

【例11-5】为数据库build进行差异备份

backupdatabasebuild

to总务管理系统withnoinit,

differential,name=‘build差异备份’description=

‘这是总务管理数据库的差异备份'noinit选项使新备份的数据追加到备份设备上已有内容的后面11.1.4使用BACkUP语句进行备份事务日志备份的语法格式：

BackupLOGdatabase_nameTobackup_device[with[name=backup_set_name][description=‘text’][No_Truncate]No_Truncate表示完成事务日志备份后，并不清空原有日志的内容，因此允许数据库在损坏时备份日志

【例11-6】为数据库build进行事务日志备份

backuplogbuild

to总务管理系统withnoinitname=‘build日志备份’必须创建完整备份，才能创建日志备份

11.2数据库的恢复

备份数据库恢复是把遭受破坏、丢失数据或出现错误的数据库恢复到原来的状态。数据库备份后，一旦系统发生崩溃或者执行了错误操作，就可以从备份文件中恢复数据库，将数据库备份重新加载到系统中。

11.2 数据库的恢复11.2.1数据库恢复策略11.2.2使用SSMS进行恢复11.2.3使用restore语句进行恢复

11.2.4系统数据库的恢复11.2.1数据库恢复策略

简单恢复完整恢复大容量日志恢复恢复策略

1.简单恢复

简单恢复可以将数据库恢复到上次备份处。优点在于日志的存储空间较小，能够提高磁盘的可用空间，而且也最容易实现。但是，无法将数据库还原到故障点或特定的即时点。不经常更新的小型数据库，一般使用简单恢复方式。

2.完整恢复

完整恢复使用数据库备份和事务日志备份恢复数据库，几乎不造成任何数据丢失，因此成为对付因存储介质损坏而数据丢失的最佳方法。该模式的缺点在于日志文件较大以及由此产生较大的存储量和性能开销

。

3.大容量日志恢复

大容量日志恢复模式对大容量操作进行最小记录。即进行大容量操作时，日志只记录多个操作的最终结果，而并非存储操作的过程细节。所以该模式节省日志空间，速度更快，但只能将数据恢复到事务日志备份的结尾处,不支持即时点恢复。恢复模型优点工作损失表现能否恢复到即时点简单允许高性能大容量复制操作收回日志空间以使空间，要求最小。必须重做自最新的数据库或差异备份后所发生的更改。可以恢复到任何备份的结尾处。随后必须重做更改完全数据文件丢失或损坏不会导致工作损失。可以恢复到任意即时点正常情况下没有。如果日志损坏，则必须重做自最新的日志备份后所发生的更改。可以恢复到任何即时点大容量日志记录允许高性能大容量复制操作大容量操作使用最少的日志空间。如果日志损坏，或者自最新的日志备份后发生了大容量操作，则必须重做自上次备份后所做的更改。否则不丢失任何工作。可以恢复到任何备份的结尾处。随后必须重做.表11-13种恢复模型的比较11.2.2使用SSMS进行恢复【例11-7】恢复build数据库

。打开SSMS，在对象资源管理器中展开【数据库】节点。右击build数据库，依次选择【任务】|【还原】|【数据库】命令，打开如图11-7所示【还原数据库】窗口在【还原数据库】窗口中选中【源设备】单选钮，点击右侧小方块，打开【指定备份】对话框，在【备份媒体】下拉列表中选择【备份设备】选项，然后单击【添加】按钮，选择之前创建的“总务管理系统”备份设备，如图11-8所示在【还原数据库】窗口中，选择【选择用于还原的备份集】下拉列表中的【完整】、【差异】和【事务日志】3种备份，可使数据库恢复到最近一次备份的正确状态，如图11-9所示。打开【选项】页面，选中【回滚未提交的事务，使数据库处于可以使用的状态无法还原其他事务日志】单选钮，如图11-10所示。单击【确定】选钮，完成对数据库的还原操作。还原完成后出现还原成功信息对话框，提示对数据库build的还原已成功完成

图11-7

图11-8

图11-9

图11-1011.2.3使用RESTORE语句进行恢复恢复数据库：

RESTOREDATABASEdatabase_name[from<backup_device[,…n]>][with[file={file_number|@file_number}][recovery|norecovery|standby=standby_file_name][replace|partial]参数说明database_name：指定要恢复的数据库的名称。backup_device：指定恢复操作要用到的备份设备名With：指定恢复选项；Recovery：当还有事务日志需要恢复时，应指定norecovery；如果所有备份都已恢复，则指定recovery

Standby：指定撤销文件名以便可以取消恢复效果。Replace：如果存在另一个具有相同名称的数据库，则首先删除现有的数据库，然后创建指定的数据库及相关文件。如果没有指定replace选项，则将进行安全检查以防止意外重写其他数据库。Partial：指定部分恢复操作。由于程序和用户错误会影响数据库的独立的部分，所以经常需要仅恢复一个数据库对象，如一个数据表。【例11-8】恢复数据库build

restoredatabasebuildfrom总务管理系统withrecoveryrecovery选项表示恢复操作执行后，build据库处于可以使用的正常状态11.2.3使用RESTORE语句进行恢复

恢复日志文件：

RESTORELOGdatabase_name[from<backup_device[,…n]>][with{NORECOVERY|RECOVERY|STANDBY=undo_file_name}

STOPAT={date_time|@date_time_var}|[,]

STOPATMARK='mark_name'[AFTERdatetime]|[,]

STOPBEFOREMARK='mark_name'[AFTERdatetime]]]STOPAT选项允许恢复数据库到精确的时刻状态STOPATMARK和STOPBEFOREMARK指定恢复到某一标记处

【例11-9】假设在“总务管理系统”备份设备上存在一个完整备份，两个事务日志备份，则执行以下3个还原操作，可以确保数据库的一致性

（1）还原完整的数据库备份，但不恢复数据库restoredatabasebuildfrom总务管理系统withfile=1,norecovery（2）还原第一个事务日志文件，但不恢复数据库restorelogbuildfrom总务管理系统withfile=2,norecovery（3）还原第二个事务日志文件，并且恢复数据库restorelogbuildfrom总务管理系统withfile=3,recovery,stopat=‘apr15,201312:00AM’系统数据库master

modelmsdbtempdb11.2.4系统数据库的恢复数据库名称是否备份说

明master是master数据库存储对SQLServer的系统级配置，包括登录名和其他服务器级对象。master数据库应该定期备份model是model数据库用作新数据库的模板。如果向model数据库添加对象或修改了其选项，就应该对其进行备份msdb是msdb数据库包含历史表、SQLServer代理作业、警报、运算符和存储在SQLServer上的集成服务包。msdb一般使用完整备份resource否resource数据库是包含所有系统对象定义的只读数据库。resource数据库不显示为系统数据库，它存储在Mssqlsystemresource.mdf文件中。可以备份文件，但不能备份数据库tempdb否tempdb在SQLServer每次启动时被删除并重建。不能备份tempdb表11-2系统数据库备份与恢复策略

11.3数据转换服务

在数据库应用程序的开发过程中，除了SQLServer也会用到Sybase、Oracle、DB2或Access等其他数据库管理系统，用户可能希望数据能够在不同的数据库管理系统间自由传输为了满足用户的需求，SQLServer2008提供了数据转换服务（DTS）。利用该服务，用户可以在不同的数据源之间进行数据的转换。11.3.1DTS概述DTS（Datatransformationservices）为了支持企业决策，许多组织都需将数据集中起来进行分析，但是通常数据总是以不同的格式存储在不同的地方。有的可能是文本文件，有的虽然具有表结构但不属于同一种数据源，这些情况极大地妨碍数据的集中处理。DTS不仅可以把一个服务器上的所有对象和数据转移到另一个服务器上还可以完成以下任务。数据转换

改变数据格式重构数据转换异构数据合并数据提供传输数据库的图形化界面

DTS（Datatransformationservices）

包含一套用于创建、调度和执行DTS包的工具，如表11-3所示

工

具功

能导入/出向导以交互式方式指导用户完成数据传输工作，可以创建DTS包，并支持数据迁移和转换。DTS设计器完成数据传输工作，支持数据迁移和转换。以图形化的形式实现DTS对象模型，可以创建具有广泛功能的DTS包。DTSRun用于执行现有DTS包的命令提示实用工具BCP工具在本地SQLServer和ASCII码文本之间导入/导出数据表11-3数据转换工具比较

DTS导入/出向导作用：为OLEDB数据源之间复制及转换数据提供最简单的方法使用步骤设置数据源：导入数据时需要选择oracle、access、excel、文本文件等外部数据对象，导出数据时，数据源是本地SQLServer

设置转换方式：选择将数据全部还是部分数据复制到目的地中

保存、调度和复制包：完成数据导入/出后，可以将整个过程生成一个DTS包，在向导中决定DTS包的格式，也可使用SQLServer代理调度包定期执行11.3.2数据的导入

利用DTS向导可以从SQLServer或其他数据源将数据导入SQLServer

【例11-10】将以下Excel表内容利用DTS导入数据库build

。

启动DTS导入导出向导

在【开始】菜单中，选择【所有程序】→【MicrosoftSQLServer2008】→【导入和导出数据（32位）】，打开导入和导出向导，如图11-11所示

图11-11

设置数据源。在【数据源】下拉列表中选择MicrosoftExcel数据类型，文件存放D:\aa\职工表1.xls，如图11-12所示。

图11-12

设置目标数据库。在【目标】中选择“MicrosoftOLEDBProviderforSQLServer”表示数据要复制到SQLServer数据库中。身份验证信息采取Windows验证模式，在【数据库】一栏中填入数据库build”，

如图11-13所示。设置转换方式

图11-13

图11-14

图11-15

图11-16

图11-17

点击下一步，并提示你点击“完成”按钮，进行数据导入如图11-18所示并提示数据传输5行

图11-18

连接SQLServer数据库，新建查询，输入如图11-19所示代码

图11-1911.3.3数据的导出

利用DTS向导可以将数据导出到其他数据库、文本文件或Excel表格。【例11-11】利用DTS将数据导出到Excel表格

。

启动DTS导入导出向导在【开始】菜单中，选择【所有程序】→【MicrosoftSQLServer2008】→【导入和导出数据（32位）】，打开如图11-20所示导入和导出向导设置数据源。在【数据源】下拉列表中选择MicrosoftExcel数据类型，文件存放在D:\aa\zhigongbiao.xls，如图11-21所示。

图11-20

图11-21

设置导出数据存储名称及路径，在【目标】一栏中，选择MicrosoftExcel作为导出数据的类型。在【Excel文件路径】中选择一个要存储导出内容的文件名这里为D:\aa\职工表2.xls，如图11-22所示。设置后，点击“下一步”

设置转换方式（指定表复制和查询）

图11-22

图11-23

图11-24

图11-25

图11-26

完成数据库表的转换。在图11-27中选择“下一步”将弹出如图9-29所示保存并运行对话框，选中【立即运行】点击“下一步”

图11-27

点击“下一步”会弹出“完成该向导”对话框。对话框提示点击“完成”将执行的操作，点击“完成”，将弹出如图11-28所示执行成功对话框

图11-28

点击查看结果。到存储位置打开导出的文件，就可以看到结果，如图11-29所示

图11-29小结备份和恢复备份设备的创建数据备份的实施数据库的恢复数据的导入/出习题--填空题1．数据库的备份类型有4种，分别是_________、差异备份、_________和文件或文件组备份。2．创建备份设备可以使用图形工具，也可以使用系统存储过程_________添加。3．S