数据库原理与应用第12章--数据备份与导入导出课件

1、第12章 数据备份与导入/导出 任课老师：【本章要点】 为使系统在出现故障后，能尽快恢复正常工作，把损失降到最低，必须对数据库进行经常性的备份，以便在需要时能够及时恢复。同时，为了能使不同数据库系统的数据相互通用，可以进行数据的导入、导出。 主要内容如下：SQL Server备份的基本概念；备份设备的类型与建立；数据备份的类型及实现；数据恢复的概念及方法；数据转换的概念及数据导入、导出方法；SSIS设计器简介。121 数据库的备份12.1.1 概述 数据库备份是指系统管理员定期或不定期地将数据库中的部分或全部内容复制到其他存储介质上的过程。数据库的备份可分为静态备份和动态备份。 静态备份是指在

2、备份过程中，不允许对数据库进行任何存取、修改等操作。静态备份操作简单，但是备份与用户事务都必须等待对方结束后才能进行。显然，静态备份降低了数据库的可用性。121 数据库的备份 动态备份是指在备份过程中，允许对数据库进行存取或修改。 动态备份克服了静态备份的缺点，即备份和用户事务可以并发执行。 由于，在备份的同时，数据库有可能被修改。因此，动态备份的数据并不能保证是正确有效的。采用动态备份时，必须建立日志文件，把备份期间各事务对数据库的修改活动记录下来。以便使数据库恢复到某一时刻的正确状态。121 数据库的备份 SQL Server的备份可以通过手工或SQL Server Agent来完成。SQ

3、L Server Agent可以在事先设定的任一天的任何时候执行备份计划。 数据库的备份是十分耗费时间和占用资源的过程，不易频繁进行。应该根据数据库的使用情况确定一个适当的备份周期。 一般而言，对于数据更新频繁，或数据非常重要的数据库，备份的频率应该较高一些。121 数据库的备份 在下列情况下，应及时进行数据库的备份。创建、修改、删除数据库前应该备份数据库；创建了用户自定义对象；增加或删除服务器的系统存储过程；修改了master、msdb、model数据库；清除事务日志或执行了不写入事务日志的操作。121 数据库的备份1212 备份设备 备份设备是指在数据库备份过程中，数据库、事务日志副本的存

4、储介质。 创建备份时，必须选择相应的备份设备，并为其分配逻辑名和物理名。 物理名是操作系统用来标识备份设备的名称。 逻辑名是用来标识物理备份设备的别名或公用名。设备的逻辑名将永久地存储在SQL Server的系统表中。 数据库在备份或还原时，可以交替使用物理名或逻辑名。121 数据库的备份1备份设备的类型 备份设备一般有磁盘设备和磁带设备两类。 磁盘设备包括本地磁盘和远程磁盘。备份时，建议不要将数据库与数据库的备份放在同一物理磁盘上。否则，当包含数据库的磁盘设备发生故障时，备份可能会一起遭到破坏，这将导致数据库无法恢复。 使用磁带设备时，必须将其物理连接到运行SQL Server实例的计算机上

5、。SQL Server不支持磁带设备的远程备份。使用磁带设备时，应考虑操作系统对其的支持性。121 数据库的备份2建立备份设备 备份设备的建立，可以使用SQL Server Management Studio，或使用T-SQL语句。(1)使用SQL Server Management Studio 在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击“服务器对象”文件夹中的“备份设备”，如图12-1所示。121 数据库的备份图12-1 对象资源管理器121 数据库的备份 在弹出的快捷菜单中，单击“新建备份设备”命令，打开“备份设备”对话框，

6、如图12-2所示。121 数据库的备份图12-2 “备份设备”对话框121 数据库的备份 在“备份设备”对话框的“设备名称”栏中输入备份设备的名称(逻辑名)； 选中“文件”单选按钮，在对应的文本框中，输入磁盘备份设备的物理名(一个完整的路径和文件名，如果是网络备份设备，应该是文件通用命名约定的位置)； 单击“文件”文本框右侧“浏览”按钮，打开“定位数据库文件”对话框，选择磁盘备份设备所使用的本地计算机上的物理文件。121 数据库的备份 在“备份设备”对话框中，“磁带”单选按钮仅在运行SQL Server 数据引擎实例的计算机附连有磁带机时才可用。 建立磁带备份设备的方法与建立磁盘备份设备的方法

7、相同。121 数据库的备份(2)使用T-SQL语句 系统存储过程sp_addumpdevice可以用来添加备份设备。其语法形式如下：sp_addumpdevice devtype = device_type, logicalname = logical_name, physicalname = physical_name121 数据库的备份 其中：device_type：备份设备的类型。disk：硬盘文件作为备份设备；tape：磁带备份设备；logical_name：备份设备的逻辑名，用于SQL Server管理备份设备；physical_name：备份设备的物理名称。物理名称必须遵照操作系统

8、文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。121 数据库的备份示例1：创建一个本地磁盘备份设备。语句如下：USE RSGLXTEXEC sp_addumpdevice disk,SQLBackup_RSGLXT1,e:RSGLXT.bak 备份设备创建后，可在SQL Server Management Studio窗口的对象资源管理器中，查看到新建的备份设备，如图12-3所示。121 数据库的备份图12-3 创建备份设备121 数据库的备份3删除备份设备 备份设备的删除，可以使用SQL Server Management Studio，或使用T-SQL语句。(1)使用T

9、-SQL语言 删除备份设备可使用系统存储过程sp_dropdevice。该存储过程可以选择删除备份设备的逻辑名称或物理文件。其具体语法如下：sp_dropdevice logicalname = device，delfile = delfile121 数据库的备份 其中：logicalname = device：数据库设备或备份设备的逻辑名称；delfile = delfile：指出是否应该删除物理备份设备文件。如果将其指定为delfile，那么就会删除物理备份设备磁盘文件。121 数据库的备份(2)使用SQL Server Management Studio 在Microsoft SQL S

10、erver Management Studio窗口的对象资源管理器中，选择“服务器对象”“备份设备”； 右击要删除的备份设备，在弹出的快捷菜单上单击“删除”命令； 在弹出的“删除对象”窗口中，单击“确定”铵钮即可。121 数据库的备份1213 备份类型 在SQL Server 2005中，备份的类型包括：数据库备份(Database Backup)、差异数据库备份(Differentia Database Backup)、事务日志备份(Transaction Log Backup)和数据库文件和文件组备份(File and File Group Backup)。121 数据库的备份1数据库备份

11、 数据库备份是指对数据库中所有的数据、日志文件以及数据库对象进行的备份。 数据库备份也称之为完整数据库备份。这种备份操作简单、易实施，且数据库的还原操作简单。 同时，由于是对数据库的完整备份，故备份速度慢，且占用大量磁盘空间。121 数据库的备份 这种类型的备份适用于以下情况：数据不是非常重要，尽管在备份之后恢复之前数据被修改，但这种修改是可以忍受的；通过批处理或其它方法，在数据库恢复之后可以很容易地重新实现在数据损坏前发生的修改；数据库变化的频率不大。 数据库备份方法应该与其他几种备份方法相互结合，才能最大程度地实现对数据库数据的保护。121 数据库的备份2差异数据库备份 差异数据库备份是指

12、对最近一次完整数据库备份以来的变更数据进行备份，因此差异备份实际上是一种增量数据库备份。 与完整数据库备份相比，差异数据库备份数据量小、备份速度快、备份和恢复所用的时间较短。 经常采用这种类型的备份方法，可以减少丢失数据的危险。121 数据库的备份 在实际中为了最大限度地减少数据库还原时间以及降低数据损失量。可以采取数据库备份、事务日志备份和差异备份混合使用的备份方案： 首先有规律地进行数据库备份，如每日进行备份；其次以较小的时间间隔进行差异备份，如34个小时进行一次备份；最后在两次差异备份之间进行事务日志备份，如30分钟进行一次备份。 在进行还原时，可以先还原最近一次的数据库备份，接着进行差

13、异备份的还原，最后进行事务日志备份的还原。121 数据库的备份3事务日志备份 事务日志备份是指对数据库从上次进行事务日志备份、差异数据库备份和完整数据库备份后，所有发生且已完成的事务进行的备份。 一般情况下，由于这类备份仅对数据库的事务日志进行备份，因此，其占用的资源比完整数据库备份的少。若数据库的事务率很高，导致事务日志迅速增大，备份所占用的资源就可能比完整数据库备份的大。事务日志的备份及恢复相对比较复杂。121 数据库的备份系统管理员在以下情况，应选择事务日志备份。不允许在最近一次数据库备份之后发生数据丢失或损坏现象；存储备份文件的磁盘空间很小或者留给进行备份操作的时间有限；准备把数据库恢

14、复到发生失败的前一点；数据库变化较为频繁。121 数据库的备份4文件和文件组备份 文件或文件组备份是指对数据库中指定的文件或文件夹进行备份。 在进行完文件或文件组备份后应再进行事务日志备份。 在使用文件或文件组进行恢复时，仍要求有一个自上次备份以来的事务日志备份来保证数据库的一致性，否则在文件或文件组备份中的数据变更将无法恢复。121 数据库的备份1214 备份操作1备份策略 创建备份的目的是为了最大程度地恢复数据库系统。因此，在具体备份数据库前，应制定备份和还原策略。 备份和还原的策略包含备份部分和还原部分。 备份部分的策略应确定备份的类型和备份的频率、备份所需的硬件特性和速度、备份设备以及

15、备份的验证方法等。121 数据库的备份2使用SQL Server Management Studio进行备份 在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击“RSGLXT”，在弹出的快捷菜单中，单击“任务”“备份”；打开“备份数据库”对话框，如图12-4所示。121 数据库的备份图12-4 备份数据库-常规121 数据库的备份 在“备份数据库”对话框的“常规”选项卡中，选择要备份的数据库系统数据库、用户数据库；备份的类型完整、差异或事务日志；备份组件数据库、文件和文件组；确定备份集的名称、说明、备份集的过期时间；选择备份的设备。 在选

16、择不同的要备份的数据库时，备份的恢复模式将相应的自动确定。数据库的恢复模式可在“数据库属性”对话框的“选项”选项卡中进行选择或修改。121 数据库的备份 恢复模式旨在控制事务日志维护。恢复模式有3种：SIMPLE、FULL、BULK_LOGGED。SIMPLE(简单模式)：该模式不备份事务日志，可最大程度地减少事务日志的管理开销。如果数据库损坏，则简单恢复模式将面临极大的工作丢失风险。数据只能恢复到已丢失数据的最新备份。 因此，在简单恢复模式下，备份间隔应尽可能短，以防止大量丢失数据。但是，间隔的长度应该足以避免备份开销影响生产工作。在备份策略中加入差异备份可有助于减少开销。121 数据库的备

17、份 通常，对于用户数据库，简单恢复模式用于测试和开发数据库，或用于主要包含只读数据的数据库(如数据仓库)。简单恢复模式并不适合生产系统，因为对生产系统而言，丢失最新的更改是无法接受的。FULL(完整模式)：此模式完整记录所有事务，并将事务日志记录保留到对其备份完毕为止。如果能够在出现故障后备份日志尾部，则可以使用完整恢复模式将数据库恢复到故障点。121 数据库的备份BULK_LOGGEG(大容量日志模式)：此模式可作为完整模式的补充。对于某些大规模大容量的操作(如大容量导入或索引创建），暂时切换到大容量日志恢复模式可提高性能并减少日志空间使用量。 在此模式下仍需要进行日志备份。与完整模式相同，

18、大容量日志模式也将事务日志记录保留到对其备份完毕为止。121 数据库的备份 由于大容量日志恢复模式不支持时点恢复，因此必须在增大日志备份与增加工作丢失风险之间进行权衡。 在各选项确定后，单击“确定”按钮，完成数据库的备份。121 数据库的备份3数据库备份选项的设置 进行数据库备份时，可在“备份数据库”对话框的“选项”选项卡中(如图12-5所示)，设置备份选项。121 数据库的备份图12-5 备份数据库-选项121 数据库的备份追加到现有备份集：将备份集追加到现有媒体集，并保留以前的所有备份；覆盖所有现有备份集：将现有媒体集上以前的所有备份替换为当前备份。检查媒体集名称和备份集过期时间：根据需要

19、，可以要求备份操作验证备份集的名称和过期时间。121 数据库的备份媒体集名称：当选择“检查媒体集名称和备份集过期时间”时，此选项才可用。输入用于检查媒体集名称和备份集过期的媒体集名称。备份到新媒体集并清除所有现有备份集：使用新媒体集，并清除以前的备份集。确定备份的可靠性。121 数据库的备份4使用T-SQL语句进行备份 使用T-SQL语言的BACKUP DATABASE语句可以备份完整数据库或文件和文件组；在完整恢复模式或大容量日志恢复模式下，使用BACKUP LOG语句备份事务日志。BACKUP语句的语法形式有所不同。121 数据库的备份备份整个数据库的语法如下：BACKUP DATABAS

20、E database_name | database_name_varTO backup_device，.nWITH DIFFERENTIAL ，FORMATNOFORMAT ， INIT | NOINIT ， NOSKIP | SKIP 121 数据库的备份备份文件或文件组的语法如下：BACKUP DATABASE database_name | database_name_var ,.n TO backup_device ，nWITH DIFFERENTIAL ，FORMAT | NOFORMAT ，INIT | NOINIT ，NOSKIP | SKIP 121 数据库的备份备份事务日志

21、的语法如下：BACKUP LOG database_name | datahase_name_varTObackup_device，.nWITH FORMAT | NOFORMAT ， INIT | NOINIT ， NOSKIP | SKIP 121 数据库的备份 在上述3个语法形式中，有关参数的含义如下： database_name | database_name_var：要进行事务日志、部分数据库或完整的数据库备份的数据库；backup_device：指定备份操作时要使用的逻辑或物理备份设备；FILE = logical_file_name | logical_file_name_var

22、 ：给一个或多个包含在数据库备份中的文件命名；121 数据库的备份FILEGROUP = logical_filegroup_name | logical_fitegroup_name_var：给一个或多个包含在数据库备份中的文件组命名。文件或文件名备份必须至少包括FILE或FILEGROUP子句之一；DIFFERENTIAL：差异备份必须包括此子句。指定数据库备份或文件备份应该与上一次完整备份后改变的数据库或文件部分保持一致。：差异备份一般会比完整备份占用更少的空间；121 数据库的备份FORMAT：指定应将媒体头写入用于此备份操作的所有卷。任何现有的媒体头都被重写。FORMAT选项使整个媒

23、体内容无效，并且忽略任何现有的内容；NOFORMAT：指定媒体头不应写入所有用于该备份操作的卷中，并且不要重写该备份设备，除非指定了INIT；INIT：指定应重写所有备份集，但是保留媒体头。如果指定了INIT，将重写那个设备上的所有现有的备份集数据。121 数据库的备份NOINIT：表示备份集将追加到指定的磁盘或磁带设备上，以保留现有的备份集。NOINIT是默认设置。NOSKIP：指示BACKUP语句在可以重写媒体上的所有备份集之前先检查它们的过期日期。SKIP：禁用备份集过期和名称检查，这些检查一般由BACKUP语句执行以防重写备份集。121 数据库的备份(2)使用T-SQL语句备份数据库示

24、例示例2：将RSGLXT数据库完整地备份到备份设备SQLBackup_RSGLXT1(e:RSGLXT.bak)中。在“查询”窗口编写代码：BACKUP DATABASE RSGLXT TO DISK = e:RSGLXT.bakWITH FORMAT;GO单击“执行”按钮，备份运行结果，如图12-6所示。121 数据库的备份图12-6 完整数据库备份结果121 数据库的备份示例3：将RSGLXT数据库的事务日志RSGLXT_log写入备份设备。在“查询”窗口编写代码：BACKUP LOG RSGLXTTO DISK = e:RSGLXT.bak 单击“执行”按钮，备份运行结果，如图12-7所

25、示。121 数据库的备份图12-7 事务日志备份结果121 数据库的备份示例4：将RSGLXT数据库中的“PRIMART”文件夹写入备份设备。在“查询”窗口中编写代码：BACKUP DATABASE RSGLXTFILEGROUP = PRIMARYTO DISK = e:RSGLXT.bak 单击“执行”按钮，备份运行结果，如图12-8所示。121 数据库的备份图12-8 文件夹备份结果122 数据库的恢复 数据库系统运行时，可能会出现各种各样的故障，使数据库中的数据丢失或遭到破坏。SQL Server系统采取一系列措施确保数据尽可能不丢失、不破坏，将数据库恢复到最近一个正确的状态，DBMS

26、的这种能力称为数据库的可恢复性(Recovery)。 SQL Server 2005 支持的还原方案如下。数据库完整还原：还原整个数据库，将从完整数据库备份开始，然后还原差异数据库备份和日志备份。122 数据库的恢复文件还原：还原多文件组数据库中的文件或文件组。在简单恢复模式下，此文件必须属于只读文件组。完整文件还原之后，便可还原差异文件备份。页面还原：还原单个页面。页面还原仅在完整恢复模式和大容量日志恢复模式下可用。段落还原：从主文件组和一个或多个辅助文件组开始，分阶段还原数据库。段落还原将从 RESTORE DATABASE 开始，使用 PARTIAL 选项并指定一个或多个要还原的辅助文件

27、组。122 数据库的恢复仅恢复：恢复那些已经与数据库保持一致且只需使其可用的数据。事务日志还原：在完整恢复模式或大容量日志恢复模式下，必须还原日志备份才能到达所需的恢复点。122 数据库的恢复 从某种意义上讲，数据库的恢复比备份更重要。执行数据库恢复以前，应注意以下两点：在数据库恢复前，应该删除故障数据库，以便删除对故障数据库的任何引用；数据库恢复之前，必须限制用户对数据库的访问，数据库的恢复是静态的，应使用SQL Server Management Studio或系统存储过程sp_dbotion设置数据库为单用户。122 数据库的恢复 从数据库备份中恢复数据，可以使用SQL Server M

28、anagement Studio，也可以使用T-SQL语句进行恢复。122 数据库的恢复1221 使用SQL Server Management Studio恢复数据库备份1恢复数据库 在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击要还原的数据库；在弹出的快捷菜单中，单击“任务”“还原”“数据库”，打开“还原数据库”对话框，如图12-9所示。122 数据库的恢复图12-9 还原数据库常规122 数据库的恢复 在“还原数据库”对话框的“常规”选项卡中，选择要还原的数据库、要还原到的时间点、源数据库、用于还原的备份集等；单击“确定”按钮，

29、系统开始数据库的还原。122 数据库的恢复2恢复数据库文件或文件组 在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击要还原的数据库；在弹出的快捷菜单中，单击“任务”“还原”“文件和文件组”，打开“还原文件和文件组”对话框。 “还原文件和文件组”对话框的“常规”选项卡，以及还原的选择内容、方式与数据库的恢复相似。122 数据库的恢复3数据库还原选项的设置 在“还原数据库/还原文件和文件组”对话框的“选项”选项卡(如图12-10所示)，可以指定用于还原备份的其他选项。122 数据库的恢复图12-10 还原数据库-选项122 数据库的恢复覆盖

30、现有数据库：指定还原操作应覆盖所有现有数据库及其相关文件，即使已存在同名的其他数据库或文件。选择此选项等效于在RESTORE语句中使用REPLACE选项；保留复制设置：将已发布的数据库还原到创建该数据库的服务器之外的服务器时，保留复制设置；122 数据库的恢复还原每个备份之前进行提示：在还原每个备份设置前要求进行确认。如果对于不同的媒体集，则必须更换磁带，例如在服务器具有一个磁带设备时，此选项非常有用；限制访问还原的数据库：使还原的数据库仅供db_owner、dbcreator或sysadmin的成员使用。选择此选项等效于在RESTORE语句中使用RESTRICTED_USER选项。122 数

31、据库的恢复1222 使用T-SQL语句进行数据库的恢复 使用T-SQL的RESTORE语句可以还原使用 BACKUP 命令所做的备份。包括：基于完整数据库备份还原整个数据库(完整还原)；还原数据库的一部分(部分还原)；将特定文件、文件组或页面还原到数据库(文件还原或页面还原)；将事务日志还原到数据库(事务日志还原)等。122 数据库的恢复1RESTORE语句的语法还原完整数据库的语法如下：RESTORE DATABASE database_name | database_name_var FROM backup_device，.nWITHFILE = file_number | file_nu

32、mber ，NORFXDVERY | RECDVERY | STANDBY = undo_file_nam， REPLACE122 数据库的恢复还原数据库部分内容的语法如下：RESTORE DATABASE database_name | database_name_varfile_or_filegroup，.nFROMbackup_device，.nWITHPARTIAL ，FILE = file_number | file_number ，NORECOVERY122 数据库的恢复还原文件或文件组的语法如下：RESTORE DATABASEdatabase_name | database_n

33、ame_varfile_or_filegroup，.nFROM backup_device，.nWITHFILE = fi1e_number | file_number ，NORECOVERY122 数据库的恢复还原事务日志的语法如下：RESTORE LOG database_name | database_name_varFROM backup_device，.nWITHFILE = file_number | file_number ，NORECOVERY | RECOVERY | STANDBY = undo_file_name，STOPAT = date_time | date_tim

34、e_var122 数据库的恢复 在上述4个语法形式中，有关参数的含义如下：database_name | database_name_var：指定了将日志或整个数据库备份还原后的数据库名称。backup_device：指定还原操作要使用的逻辑或物理备份设备。file_or_filegroup：指定包括在数据库还原中的逻辑文件或文件组的名称。可以指定多个文件或文件组。122 数据库的恢复FILE = file_number | file_number：标识要还原的备份集。NORECOVERY：指示还原操作不回滚任何未提交的事务。如果需要应用另一个事务日志，则必须指定NORECOVERY或STAN

35、DBY选项。如果NORECOVERY、RECOVERY和STANDBY均未指定，则默认为RECOVERY。RECOVERY：指示还原操作回滚未提交的事务。在恢复进程后即可随时使用数据库。122 数据库的恢复PARTIAL：指定部分还原操作。STOPAT = date_time | date_time_var：指定将数据库还原到指定的。日期和时间时的状态。只有在指定的日期和时间前写入的事务日志记录才能应用于数据库。122 数据库的恢复2恢复数据库备份示例5：从SQLBackup_RSGLXT1逻辑备份设备，还原数据库“RSGLXT”的完整数据库备份。在“查询”窗口中编写语句：RESTORE DA

36、TABASE RSGLXT FROM SQLBackup_RSGLXT1WITH REPLACE单击“执行”按钮，恢复备份的运行结果，如图12-11所示。122 数据库的恢复图12-11 数据库备份恢复122 数据库的恢复 使用RESTORE DATABASE语句恢复整个数据库时，如果要在还原数据库备份后应用事务日志或差异数据库备份，则需要指定NORECOVERY子句。 如果备份设备上有多个备份集，可以使用FILE子句指定文件号标识需要从哪个备份集中恢复数据库。122 数据库的恢复 从差异数据库备份中恢复数据时，首先必须执行RESTORE DATABASE并指定NORECOVERY子句，以恢复

37、差异数据库备份之前的完整数据库备份。然后再执行一次RESTORE DATABASE恢复差异数据库备份，并同时需要指定数据库名称和要从其中恢复差异数据库备份的备份设备。 如果在执行完差异数据库备份恢复以后还要应用事务日志备份，则还必须同时指定NORECOVERY子句，否则指定RECOVERY子句。122 数据库的恢复 还原差异数据库备份的顺序为：先还原最新的完整数据库备份，然后还原最后一次的差异数据库备份。122 数据库的恢复 执行RESTORE LOG语句，可进行由事务日志备份恢复数据库的工作。 还原事务日志备份的步骤为：还原事务日志备份之前的完整数据库备份或差异数据库备份。按顺序恢复自完整数

38、据库备份或差异数据库以后创建的所有事务日志。撤销所有未完成的事务。122 数据库的恢复 如果恢复当前事务日志备份后还要应用其他事务日志备份，则在执行RESTORE LOG时还要指定NORECOVERY子句；否则，指定RECOVERY子句以恢复服务器的运行状态。123 数据导入与导出 在创建和使用SQL Server数据库的过程中，数据的导入、导出是非常普遍的操作。 在对各种数据库系统所管理的格式各不相同、存储于不同地方的数据进行集中分析时，首先需要将数据转换为同一种格式。 数据转换可以将数据由A格式转换为B格式，反之亦然。 数据在由A格式转换为B格式时，对B格式数据而言，则可称之为数据的导入，

39、对A格式的数据而言，则可称之为数据的导出。123 数据导入与导出1231 概述 Microsoft SQL Server 2005允许在 SQL Server 数据表和数据文件之间大容量导入和导出数据（“大容量数据”）。这对在 SQL Server 和异类数据源之间有效传输数据是非常重要的。 “大容量导出”是指将数据从 SQL Server 表复制到数据文件。 “大容量导入”是指将数据从数据文件加载到 SQL Server 表。如用户可以将数据从 Microsoft Excel 应用程序导出到数据文件，然后将这些数据大容量导入到 SQL Server 表中。123 数据导入与导出1数据导入与导

40、出的方法 在Microsoft SQL Server 2005中，从 SQL Server 表中大容量导出数据的基本方法是使用大容量导出数据并生成格式化文件的命令行bcp 实用工具(Bcp.exe)。 在Microsoft SQL Server 2005中，将大容量数据导入SQL Server 表或未分区的视图的基本方法包括：大容量导入数据并生成格式化文件的命令行bcp实用工具(Bcp.exe)；123 数据导入与导出将数据直接从数据文件导入数据库表或未分区视图的BULK INSERT 语句；通过在INSERT语句中指定 OPENROWSET(BULK.)函数选择数据，从而使用 OPENROW

41、SET 大容量行集提供程序，将数据大容量导入SQL Server 表的INSERT.SELECT * FROM OPENROWSET(BULK.)语句。123 数据导入与导出bcp实用工具、BULK INSERT 语句和INSERT.SELECT * FROM OPENROWSET(BULK.)语句的语法均较为复杂，具体可参考有关资料。123 数据导入与导出2导入大容量数据的基本原则 在将数据文件中的数据大容量导入到 Microsoft SQL Server 实例时，请使用以下原则：设置权限。使用 bcp 实用工具、BULK INSERT 语句或 INSERT .SELECT * FROM O

42、PENROWSET(BULK.) 语句的用户帐户必须具有表的所需权限，这些权限由表所有者分配。123 数据导入与导出使用大容量日志恢复模式。此原则适用于使用完整恢复模式的数据库。在执行大容量导入操作之前，先将数据库更改为使用大容量日志恢复模式。之后应立即将数据库重设为完整恢复模式。大容量导入数据后进行备份。对于使用简单恢复模式的数据库，用户在大容量导入操作完成后执行完整备份或差异备份。123 数据导入与导出删除表索引。在导入的数据量与表中已有数据量相比很大时，删除表中的索引可显著提高性能。 注意： 如果加载的数据量与表中已有的数据量相比较小时，删除索引会适得其反。因为重建索引所需的时间可能要比

43、大容量导入操作期间所节省的时间更长。 123 数据导入与导出查找并删除数据文件中的隐藏字符。许多实用工具和文本编辑器都会显示隐藏字符，这些隐藏字符通常位于数据文件末尾。 在大容量导入操作期间，ASCII 数据文件中的隐藏字符会导致问题，这些问题会引发“发现意外空字符”错误。查找并删除所有隐藏字符有助于避免此问题。123 数据导入与导出3注意事项 如果用户不确定应如何针对大容量导入设置数据文件的格式，则可以使用 bcp 实用工具将数据从表导出到数据文件中。 此文件中每个数据字段的格式均显示了将数据大容量导入对应表列时所要求的格式。对数据文件的各个字段使用相同的数据格式。123 数据导入与导出(1

44、)大容量导出的数据文件格式注意事项 在使用 bcp 命令执行大容量导出操作之前，应考虑以下事项：将数据导出到文件时，bcp 命令使用指定的文件名自动创建数据文件。如果该文件名已经存在，正在大容量复制到数据文件的数据将覆盖文件中的现有内容。从表或视图大容量导出到数据文件要求对正在大容量复制的表或视图具有 SELECT 权限。123 数据导入与导出Microsoft SQL Server 可以使用并行扫描检索数据。因此，通常不保证从 SQL Server 实例大容量导出的表行在数据文件中按特定顺序排列。为了确保大容量导出的表行在数据文件中按特定顺序排列，请使用 queryout 选项来通过查询进行

45、大容量导出，并指定一个 ORDER BY 子句。123 数据导入与导出(2)大容量导入的数据文件格式要求 为了导入数据文件中的数据，该文件必须满足以下基本要求：数据必须以行和列的格式表示。 注意：数据文件的结构不必与 SQL Server 表的结构一致，因为大容量导入过程中可以跳过列或对列重新排序。数据文件中的数据格式必须是支持的格式，例如字符格式或本机格式。123 数据导入与导出数据可以是字符格式或本机二进制格式(包括 Unicode)。为了使用 bcp 命令、BULK INSERT 语句或 INSERT . SELECT * FROM OPENROWSET(BULK.) 语句导入数据，目标

46、表必须已存在。数据文件中的每个字段都必须与目标表中的对应列兼容。123 数据导入与导出若要从SQL Server 外部，如从Visual FoxPro表(.dbf)文件或者Microsoft Excel工作表(.xls)文件导入数据，应先将数据转换为逗号分隔值(CSV)文件，才可以在 SQL Server 执行大容量导入操作。若要从包含固定长度或固定宽度字段的数据文件导入数据，则必须使用格式化文件。123 数据导入与导出 此外，在将数据文件中的数据大容量导入表中时，还有注意：用户必须对表具有 INSERT 和 SELECT 权限。如果用户需要执行数据定义语言(DDL)操作(例如禁用约束)时，应

47、具有 ALTER TABLE 权限。123 数据导入与导出使用BULK INSERT 或 INSERT . SELECT * FROM OPENROWSET(BULK.) 大容量导入数据时，必须可以通过 SQL Server 进程的安全性配置文件(如果用户使用 SQL Server 提供的登录名进行登录)或在委托安全性下使用的 Microsoft Windows 登录名对数据文件进行读取操作。此外，用户还必须具有 ADMINISTER BULK OPERATIONS 权限以读取文件。 注意：由于不支持大容量导入到分区视图，因此无法将数据大容量导入到分区视图。123 数据导入与导出1232 使用

48、SQL Server Management Studio导出与导入数据 在SQL Server 2005中，使用SQL Server Management Studio可以很方便地进行数据的导出或导入。数据的导入与导出过程类似。 下面以将RSGLXT数据库中的Employees数据表导出到Excel工作表为例，具体介绍导出数据的方法。123 数据导入与导出 在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击RSGLXT数据库；在弹出的快捷菜单中，单击“任务”“导出数据”选项，如图12-12所示。123 数据导入与导出图12-12 选择“导

49、出数据”选项 123 数据导入与导出 打开“SQL Server导入和导出向导”对话框，如图12-13所示。123 数据导入与导出图12-13 SQL Server导入与导出向导 123 数据导入与导出 单击“下一步”按钮，打开“选择数据源”对话框，如图12-14所示。从中选择数据源为“Microsoft OLE DB Provider for SQL Server”，同时选择数据库服务器名和身份验证方式。123 数据导入与导出图12-14 “选择数据源”对话框 123 数据导入与导出 单击“下一步”按钮，打开“选择目标”对话框(如图12-15所示)，从中选择导出数据的类型、目标文件的存储路径

50、和版本。 123 数据导入与导出图12-15 “选择目标”对话框 123 数据导入与导出 单击“下一步”按钮，打开“指定表复制或查询”对话框(如图12-16所示)，在此选择“复制一个或多个表或视图的数据”选项。 123 数据导入与导出图12-16 “指定表复制或查询”对话框 123 数据导入与导出 单击“下一步”按钮，打开“选择源表和源视图”对话框(如图12-17所示)，从中选择准备导出数据的数据表。 123 数据导入与导出图12-17 “选择源表和源视图”对话框 123 数据导入与导出 选定数据表后，单击“编辑”按钮，打开“列映射”对话框(如图12-18所示)，在此选择默认设置；单击“确定”

51、按钮，返回“选择源表和源视图”对话框。 123 数据导入与导出图12-18 “列映射”对话框 123 数据导入与导出 单击“下一步”按钮，打开“保存并执行包”对话框(如图12-19所示)，在此选择“立即执行”复选框。 123 数据导入与导出图12-19 “保存并执行包”对话框 123 数据导入与导出 单击“下一步”按钮，打开“完成该向导”对话框(如图12-20所示)，验证任务选项。 123 数据导入与导出图12-20 “完成该向导”对话框 123 数据导入与导出 单击“完成”按钮，执行数据导出任务，数据导出完成后，显示“执行成功”界面，如图12-21所示。 123 数据导入与导出图12-21

52、数据导出执行结果 123 数据导入与导出示例6：现有纯文本文件courses.txt，数据如下(数据之间以“逗号”分隔)。课程编号,课程名称,学分0014,离散数学,20015,电子技术,30016,软件工程,30017,数据结构,40018,计算机网络技术基础,2 使用SQL Server Management Studio将courses.txt导入到数据库JXGLXT的CURRICULUM数据表中。123 数据导入与导出 操作步骤如下：在Microsoft SQL Server Management Studio窗口的对象资源管理器中，右击JXGLXT数据库；在弹出的快捷菜单中，单击“任

53、务”“导出数据”选项，如图12-22所示。123 数据导入与导出图12-22 选择“导入数据”选项 123 数据导入与导出打开“SQL Server导入和导出向导”对话框后，单击“下一步”按钮，打开“选择数据源”对话框，在“数据源”下拉列表中，选择“平面文件源”；选定欲导入文件名；确定导入文件的格式，如图12-23所示。123 数据导入与导出图12-23 “选择数据源”对话框 123 数据导入与导出单击“下一步”按钮，打开“选择目标”对话框，选择目标数据库，如图12-24所示。123 数据导入与导出图12-24 “选择目标数据库”对话框 123 数据导入与导出单击“下一步”按钮，打开“选择源表

54、和源视图”对话框，在“目标”下拉列表中选择欲导入数据的数据表，如图12-25所示。123 数据导入与导出图12-25 “选择目标数据表”对话框 123 数据导入与导出选定目标数据表后，可单击“编辑”按钮，打开“列映射”对话框，用户可根据需要选择“删除目标表中的行”替换目标表中的数据，或选择“向目标表中追加行”添加数据，如图12-26所示。123 数据导入与导出图12-26 “列映射”对话框 123 数据导入与导出单击“确定”按钮，返回“选择源表和源视图”对话框；单击“下一步”按钮，打开“保存并执行包”对话框，确定是否选择“保存SSIS包”复选框，如图12-27所示。123 数据导入与导出图12

55、-27 “保存并执行包”对话框 123 数据导入与导出单击“下一步”按钮，打开“完成该向导”对话框，如图12-28所示。123 数据导入与导出图12-28 “完成该向导”对话框 123 数据导入与导出单击“完成”按钮，系统开始导入数据；数据导入任务完成后，显示“执行成功”对话框，如图12-29所示。123 数据导入与导出图12-29 “执行成功”对话框 123 数据导入与导出1233 SSIS设计器简介 在SQL Server 2005中，以前版本的数据转换服务(DTS)被SQL Server整合服务(SSIS)所代替。在进行数据转换过程中，以前版本的DTS设计器被新的SSIS设计器所代替。123 数据导入与导出1SSIS设计器的启