第10章 备份和恢复 本章导读： 在数据库的使用过程中，难免会由于软硬件故障、病毒入侵、操作不当等各种因素造成数据的丢失或损坏。备份和恢复是保证数据库有效性、正确性和可靠性的重要措施。运用适当的备份策略，可以保证及时有效地恢复数据库中的重要数据，将数据损失量降低到最小点。 知识要点： 备份 恢复

10.1 备份 备份是指对SQL Server数据库部分或全部内容进行处理，生成一个副本的过程。数据库备份记录了在进行数据库备份操作时的所有数据状态。 10.1.1 备份概述 10.1.2 备份类型 10.1.3 备份设备 10.1.4 备份数据库

10.1.1 备份概述 备份是系统维护和管理的一项重要内容，执行备份必须拥有对数据库进行备份的权限，在SQL Server 2000中，只有固定服务器角色sysadmin（系统管理员）和固定数据库角色db_owner（数据库所有者）、db_backupoperator（数据库备份执行者）可以做备份操作，但sysadmin（系统管理员）可以授权其它角色执行数据库备份操作。

10.1.1 备份概述 一般情况下，数据库需备份的内容包括系统数据库、用户数据库和事务日志3部分。 10.1.1 备份概述 一般情况下，数据库需备份的内容包括系统数据库、用户数据库和事务日志3部分。 （1）系统数据库主要包括master、msdb和model数据库，记录了重要的系统信息。一旦损坏，SQL Server 2000系统无法正常运行，因此必须完全备份，以便在系统发生故障时能够利用备份还原整个系统。但不必备份tempdb数据库，因为其内容总是在启动后自动建立。 （2）用户数据库包含了用户加载的信息资源，根据其重要性可分为关键数据和非关键数据。关键数据一旦损坏，不易甚至不能重新建立，因此必须进行完全备份。 （3）事务日志记录了用户对数据库的各种事务操作，平时系统会自动管理和维护所有的数据库事务日志文件。相对于数据库备份，事务日志备份所需要的时间较少，但还原所需要的时间却比较长。

10.1.2 备份类型 备份和恢复是由数据库管理员完成的。SQL Server 2000提供了4种备份类型：数据库完全备份、数据库差异备份、事务日志备份、文件和文件组备份。 1．数据库完全备份 数据库完全备份是指备份整个数据库的内容，包括数据库中所有的数据文件及其对象和事务日志文件。实际上，备份的时间和存储空间由数据库中的数据容量决定的。恢复时不需要其它支持文件，操作相对简单。 数据库完全备份是恢复数据的基础文件，事物日志备份和数据库差异备份都需要依赖数据库完全备份。如果完全备份进行得比较频繁，在备份文件中就有大量的数据是重复的。 适用于数据更新缓慢的数据库。由于这种类型的备份不仅速度较慢，而且将占用大量磁盘空间，所以通常将其安排在整个数据库系统的事务运行数目相对较少时（如晚间）进行，以避免对用户的影响和提高数据库备份的速度。

10.1.2 备份类型 2．数据库差异备份 数据库差异备份只记录自上次数据库完全备份之后发生更改的数据。差异备份一般会比完全备份占用更少的空间。数据库差异备份的时间和存储空间由上次数据库完全备份后变化的数据容量决定的。差异备份之前，必须至少有一次完全备份，而还原时，也必须先还原完全备份，才能还原差异备份。在进行多次差异备份后，只能还原到最后一次差异备份时的时刻。 差异备份和还原所用的时间较短，因而通过增加差异备份的备份次数，可以降低丢失数据的风险，但是它无法象事务日志备份那样提供到失败点的无损数据备份。

10.1.2 备份类型 3．事务日志备份 事务日志备份是指对数据库发生的事务进行备份，包括从上次进行事务日志备份、差异备份和完全备份之后，所有已经完成的事务。事务日志备份所需时间和存储空间最小，适用于数据库变化较为频繁或不允许在最近一次数据库备份之后发生数据丢失或损坏的情况。 事务日志备份之前，必须至少有一次完全备份，而还原时，也必须先还原完全备份，然后还原差异备份，最后按照日志备份的先后顺序，依次还原各次事务日志备份的内容。

10.1.2 备份类型 4．文件和文件组备份 文件和文件组备份是指单独备份特定的、相关的数据库文件或文件组。这种备份策略最大的优点：只还原已损坏的文件或文件组，而不用还原数据库的其余部分。 文件和文件组备份通常需要事务日志备份来保证数据库的一致性，文件和文件组备份后还要进行事务日志备份，以反映文件或文件组备份后的数据变化。

10.1.3 备份设备 备份设备是指用来存储数据库或事物日志备份的存储介质。备份设备以文件的形式存储在物理介质上，并同数据库一样具有物理名和逻辑名两种命名方式。备份和恢复数据库时可以交替使用备份设备的物理名和逻辑名。 使用逻辑设备名称标识的备份设备称为永久备份设备，其名称永久地存储在SQL Server的系统表中，可以多次使用。使用物理设备名称标识的备份设备称为临时备份设备，其名称没有记录在系统设备表中，只能使用一次。 备份设备可以是磁盘、磁带或命名管道。当使用磁带时，SQL Server 2000只支持本地磁带机作为备份设备；当使用磁盘时，SQL Server 2000支持本地主机磁盘和远程主机磁盘作为备份设备；当使用命名管道时，SQL Server 2000支持第三方软件供应商提供命名管道来备份和恢复数据库。 备份之前，首先必须建立存储备份数据的备份设备，创建和删除备份设备方法有两种：企业管理器和系统存储过程。一般不要将备份设备建立在数据库所在的磁盘上。

10.1.3 备份设备 1．使用企业管理器创建和删除备份设备 使用企业管理器创建备份设备步骤如下： 10.1.3 备份设备 1．使用企业管理器创建和删除备份设备 使用企业管理器创建备份设备步骤如下： （1）展开企业管理器控制台目录，直至出现“备份”节点，如图10-3所示。 （2）右击“备份”节点，弹出如图10-4的“备份设备属性—新设备”对话框，在“名称”框中输入备份设备逻辑名，如“my_bak”，在“文件名”框中设置备份设备的物理路径及名称，如“d:\backup\my_bak.bak”。

图10-3 “企业管理”对话框1 图10-4 “备份设备属性”对话框 10.1.3 备份设备 图10-3 “企业管理”对话框1 图10-4 “备份设备属性”对话框

图10-5 “企业管理”对话框2 图10-6 “企业管理”对话框3 10.1.3 备份设备 （3）单击“确定按钮”按钮，返回企业管理器，可以看到刚才创建好的备份设备图标，如图10-5所示。 （4）当备份设备不需要时，可以将其删除，在企业管理器中右侧窗口中右击要删除备份设备，在弹出的快捷菜单中选择“删除”命令，则删除该备份设备，如图10-6所示。 图10-5 “企业管理”对话框2 图10-6 “企业管理”对话框3

10.1.3 备份设备 2．使用系统存储过程创建备份设备 使用系统存储过程创建备份设备的命令是sp_addumpdevice，其语法格式如下： sp_addumpdevice [@devtype = ]'类型',[@logicalname = ]'逻辑名',[@physicalname =]'物理名' 说明： （1）[@devtype =]'类型'：指定备份设备的类型，取值可以是:disk、tape、pipe，分别代表磁盘、磁带、命名管道。 （2）[@logicalname =]'逻辑名'：指定备份设备的逻辑名称。 （3）[@physicalname =]'物理名'：指定备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。对于远程硬盘文件，可以使用格式“\\主机名\共享路径名\路径名\文件名”表示；对于磁带设备，用“\\.\tape”表示，其中n为磁带驱动器序列号。

10.1.3 备份设备 【例10-1】 创建一个备份设备，逻辑名为“mycopy1”，物理名为“d:\backup\company.bak”。 sp_addumpdevice @devtype='disk', @logicalname='mycopy1', @physicalname='d:\backup\company.bak' 或： sp_addumpdevice 'disk','mycopy1','d:\backup\company.bak'

10.1.3 备份设备 3．使用系统存储过程删除备份设备 使用系统存储过程删除备份设备的命令是sp_dropdevice，其语法格式如下： 10.1.3 备份设备 3．使用系统存储过程删除备份设备 使用系统存储过程删除备份设备的命令是sp_dropdevice，其语法格式如下： sp_dropdevice [@logicalname=]'逻辑名',[@delfile=]'delfile' 说明： （1）[@logicalname =]'逻辑名'：指定备份设备逻辑名称； （2）[@delfile=]' delfile '：指定参数时，将同时删除相应的物理文件。

10.1.4 备份数据库 备份既可以使用企业管理器执行，也可以使用T-SQL语句执行。而使用企业管理器执行备份又有两种操作：一是利用“工具”→“向导”命令，另一是利用“备份对话框”。 1．使用企业管理器备份数据库 1）展开企业管理器控制台目录，右击“数据库”节点，弹出快捷菜单，选择“所有任务”→“备份数据库”命令，如图10-7所示。 2）单击释放后，弹出“SQL Server备份”对话框的“常规”界面，如图10-8所示。 3）在“数据库”列表话框内选择要备份的数据库；在“名称”框中输入备份集名称；在“描述”框中可以输入备份集描述（可选）； 4）在“备份”选项中选择备份类型（完全、差异、事务日志、文件和文件组）；

10.1.4 备份数据库 图10-7 “创建备份”快捷菜单对话框 图10-8 “常规”界面

单击“目的”选区的“添加”按钮，弹出如图10-9所示的“选择备份目的”对话框，从中可以选择备份设备，也可以指定备份文件名。 10.1.4 备份数据库 单击“目的”选区的“添加”按钮，弹出如图10-9所示的“选择备份目的”对话框，从中可以选择备份设备，也可以指定备份文件名。 注意：“删除”用来逻辑删除备份设备，“内容”用来查看备份设备的现有内容。 图10-9 “创建备份向导”对话框

图10-10 “编辑调度”对话框 图10-11 “创建备份向导”对话框 10.1.4 备份数据库 6）在“重写”选项中选择“追加到媒体”和“重写现有媒体”。 7）选择“调度”复选框，单击其后“…”按钮，弹出如图10-10所示的“编辑调度”对话框，设置调度类型。如果选择最后一种，可以单击“更改”按钮，弹出“编辑反复出作业调度”对话框，如图10-11所示。 图10-10 “编辑调度”对话框 图10-11 “创建备份向导”对话框

10.1.4 备份数据库 2．使用T-SQL语句备份数据库 （1）完全数据库备份 10.1.4 备份数据库 2．使用T-SQL语句备份数据库 （1）完全数据库备份 完全数据库备份是制作数据库中所有内容的一个副本，备份过程花费时间相对较长，备份占用的空间大，因此不宜频繁进行。 典型语法格式如下： backup database数据库名称 to <备份设备>[,...n] [ with [name=备份集名称] [[,]description = '备份描述文本'] [[,]{init|noinit}]]

10.1.4 备份数据库 说明： 1）<备份设备>：指定备份要使用的逻辑或物理备份设备，可取值：{逻辑备份设备名}|{disk|tape}'物理备份设备名'。 2）init：表示重写所有备份集。 3）noinit：表示备份集将追加到指定的设备现有数据之后，以保留现有的备份集。 4）description：备份描述文本。

10.1.4 备份数据库 【例10-2】 将“jxgl”数据库备份到d盘的backup文件夹下的mybackup2.bak文件中。 10.1.4 备份数据库 【例10-2】 将“jxgl”数据库备份到d盘的backup文件夹下的mybackup2.bak文件中。 --首先先创建一个备份设备 sp_addumpdevice 'disk','mycopy2','d:\backup\mybackup2.bak' --用backup database备份数据库jxgl backup database jxgl to mycopy2 with name='jxgl完全备份',description='完全备份' 【例10-3】 将“jxgl”数据库备份到d盘下dbk文件中的多个备份设备上。 --创建第一个备份设备 exec sp_addumpdevice 'disk','file1','d:\dbk\file1.bak' --创建第二个备份设备 exec sp_addumpdevice 'disk','file2','d:\dbk\file2.bak' backup database jxgl to file1,file2 with name='dbbk' 【例10-4】 将“jxgl”数据库备份到网络中的另一台主机data上的共享目录backup中。 sp_addumpdevice 'disk','thecopy','\\data\backup\jxgl.dat' backup database jxgl to thecopy

10.1.4 备份数据库 （2）差异数据库备份 指对最近一次完全数据库备份结束以来发生改变的数据进行备份。当数据库从上次备份以来只修改了很少的数据时，适合使用差异备份。 典型语法格式如下： backup database 数据库名称 to <备份设备>[,...n] with differential [[,]name=备份集名称] [[,]description = '备份描述文本'] [[,]{init|noinit}] 说明：differential表示进行差异备份，其它选项含义与完全数据库备份类似。

10.1.4 备份数据库 【例10-5】 假设对数据库“jxgl”进行了一些修改，现在要做一个差异备份，且将该备份添加到“例10-2”的现有备份之后。 backup database jxgl to mycopy2 with differential, noinit, name='教学管理备份', description='第一次差异备份'

10.1.4 备份数据库 （3）事务日志备份 事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录，备份事务日志将对最近一次备份事务日志以来的所有事务日志进行备份。 注意：如果希望备份事务日志，那么必须设置数据库的恢复模型为“完全”或“大容量日志记录”模式。 设置数据库的恢复模型步骤如下：右击数据库（jxgl），弹出快捷菜单，选择“属性”命令；单击释放后，弹出“jxgl属性”对话框，即可设置故障还原模型。

图10-1 “企业管理器”对话框 图10-2 “jxgl属性”对话框 10.1.4 备份数据库 图10-1 “企业管理器”对话框 图10-2 “jxgl属性”对话框

10.1.4 备份数据库 典型语法格式如下： backup log数据库名称 to <备份设备>[,...n] [with 10.1.4 备份数据库 典型语法格式如下： backup log数据库名称 to <备份设备>[,...n] [with [[,]name=备份集名称] [[,]description='备份描述文本'] [[,]{init|noinit}]] 【例10-6】将数据库“jxgl”的日志文件备份到文件“d:\backup\mylog1.bak”中。 --创建备份设备 exec sp_addumpdevice 'disk','mylog1','d:\backup\mylog1.bak' --备份日志 backup log jxgl to mylog1

10.1.4 备份数据库 （4）文件或文件组备份 当一个数据库很大时，对整个数据库进行备份可能会花费很多时间，这时可以采用文件或文件组备份，即对数据库中的部分文件或文件组进行备份。 典型语法格式如下： backup database数据库名称 <文件或文件组>[,...n] to<备份设备>[,...n] [with differential [[,]name=备份集名称] [[,]description='备份描述文本'] [[,]{init|noinit}]] 说明：<文件或文件组>::={file=逻辑文件名|filegroup=逻辑文件组名} 【例10-6】 将数据库mn7的文件mn7d_data备份到文件“d:\temp\mn7d_data.dat”中。 backup database mn7 file='mn7d_data' to disk='d:\temp\mn7d_data.dat' 【例10-7】 将数据库mn7的文件组group1备份到文件“d:\temp\group1.dat”中。 backup database mn7 filegroup='group1' to disk='d:\temp\group1.dat' with name='group backup of test'

10.2 恢复 恢复就是把遭到破坏或丢失的数据或出现重大错误的数据还原到备份时的状态。恢复是备份的逆过程，数据库备份后，一旦发生系统崩溃或者出现数据丢失，就可以将数据库的副本加载到系统中，让数据库还原到备份时的状态。 10.2.1 恢复概述 10.2.2 恢复数据库

10.2.1 恢复概述 10.2.1 恢复概述 恢复可以还原到数据库备份当时的相关文件，但同时丢失备份完成后对数据库所做的修改。SQL Server 2000提供了3种数据库还原模式：简单还原（Simple Recovery）、完全还原（Full Recovery）、大容量日志记录还原（Bulk-Logged Recovery）。 1．简单还原模式 简单还原模式可以将数据库还原到上次备份处，但是无法将数据库还原到故障点或待定的某时刻。它常用于还原最新的数据库的完全备份和差异备份，若想还原到数据库失败时状态，必须重建最新的数据库备份或者差异备份后的更改。 2．完全还原模式 完全还原模式使用数据库的完全备份和事务日志备份将数据库还原到故障点或特定的时间点。为保证这种还原程度，包括大容量操作（如select into、create index和大容量装载数据）在内的所有操作都将完整地记入日志。在这种还原模式下，如果事务日志损坏，则必须重做最新的日志备份后再进行还原。 3．大容量日志记录还原模式 大容量日志记录还原模式为某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。该种还原模式只允许数据库还原到事务日志备份的结尾处，不支持即时点还原。这种还原模式所采用的备份策略与完成还原模式基本相同。

10.2.2 恢复数据库 恢复既可以使用企业管理器执行，也可以使用T-SQL语句执行。 1．使用企业管理器恢复数据库 10.2.2 恢复数据库 恢复既可以使用企业管理器执行，也可以使用T-SQL语句执行。 1．使用企业管理器恢复数据库 恢复数据库使用企业管理器恢复数据库的操作步骤如下： （1）展开企业管理器控制台目录，直至“数据库”节点，右击“数据库”，弹出快捷菜单，选择“所有任务”→“还原数据库”命令，弹出“还原数据库”对话框的“常规”界面，如图10-9所示。如果单击“选项”卡，则弹出“选项”界面，如图10-10所示。 （2）在“还原为数据库”右侧的下拉列表框中可以选择还原后的数据库名称。 （3）在“还原”右侧选择数据库“还原”类型，各选项含义如下： 数据库：表示还原选定的数据库； 对文件组或文件：表示只想还原部分文件组和文件。 从设备：表示备份时的数据库保存自己设定的目录中。

10.2.2 恢复数据库 图10-9 “常规”界面1 图10-10 “选项”界面

10.2.2 恢复数据库 注意：选择不同的“还原”选项，“参数”选项运行结果不同，后两者运行结果分别如图10-11和10-12所示。 10.2.2 恢复数据库 注意：选择不同的“还原”选项，“参数”选项运行结果不同，后两者运行结果分别如图10-11和10-12所示。 图10-11 “常规”界面2 图10-12 “常规”界面3

10.2.2 恢复数据库 （3）在图10-9所示的对话框中设置“还原”为选择“数据库”，然后根据提示和需要做必要设置，单击“确定”按钮，稍后弹出完成对话框，如图10-13所示。 图10-13 完成对话框

10.2.2 恢复数据库 2．使用T-SQL语句恢复数据库 10.2.2 恢复数据库 2．使用T-SQL语句恢复数据库 使用T-SQL语句restore可以完成对整个数据库的恢复，也可以完成恢复数据库的日志，或者完成恢复数据库的某个文件或文件组。 （1）恢复完全备份数据库和差异备份数据库 典型语法格式如下： restore database数据库名称 [from<备份设备>[,...n]] [with [[,]file=文件号] [[,]move'逻辑文件名'to'物理文件名'][,...n] [[,]{norecovery|recovery}] [[,]replace] ] 说明：

10.2.2 恢复数据库 1）文件号：要还原的备份集序号，如文件号为1表示第1个备份集，文件号为2表示第2个备份集，依次类推。 10.2.2 恢复数据库 1）文件号：要还原的备份集序号，如文件号为1表示第1个备份集，文件号为2表示第2个备份集，依次类推。 2）norecovery：指示还原操作不回滚任何未提交的事务。 注意：当还原数据库备份和多个事务日志时，或在需要使用多个restore语句时（例如在完整数据库备份后进行差异数据库备份），SQL Server要求在除最后的restore语句外的所有其它语句上使用with norecovery选项。 3）recovery：指示还原操作回滚任何未提交的事务。在恢复完成后即可随时使用数据库。 4）replace：指定如果存在同名数据库，将覆盖现有的数据库。

10.2.2 恢复数据库 【例10-9】 设在e盘temp文件夹下有一个职工数据库的完全备份文件“职工.bak”，恢复该数据库，将恢复后的数据库名称改为“职工信息”。如果当前服务器中存在“职工信息”数据库，则覆盖该数据库。 方法一：使用备份设备。 exec sp_addumpdevice 'disk','copy1','e:\temp\职工.bak' restore database 职工信息 from copy1 with move '职工_data' to 'e:\sql_data\职工信息.mdf', move '职工_log' to 'e:\sql_log\职工信息.lgf', replace 方法二：直接指定磁盘文件名 restore database 职工信息 from disk = 'e:\temp\职工.bak' to 'e:\sql_log\职工信息.lgf',

10.2.2 恢复数据库 （2）恢复事务日志 典型语法格式如下： restore log 数据库名称 10.2.2 恢复数据库 （2）恢复事务日志 典型语法格式如下： restore log 数据库名称 [from<备份设备>[,...n]] [with [[,]file=文件号] [[,]move'逻辑文件名'to'物理文件名'][,...n] [[,]{norecovery|recovery}] ] 说明：各参数含义同（1）。

10.2.2 恢复数据库 【例10-10】 假设对数据库“jxgl”先后做了完全数据库备份（如例10-2）、差异数据库备份（如例10-5）和事务日志备份（如例10-6），现在利用这三个备份来恢复数据库。 --恢复完全备份 restore database jxgl from mycopy2 with file=1, norecovery --这时数据库无法使用，继续恢复差异备份 --这时数据库仍然无法使用，继续恢复事务日志备份 restore log jxgl from mylog1 recovery --完成恢复，数据库可以使用

本章小结 备份和恢复是SQL Server中两个最重要的工具，备份和恢复既可以通过企业管理实现，也可以分别通过命令backup和restore实现。