第13章数据备份与恢复 13-1备份和恢复概沁 13-2备份设备 13-3备份数据库 13-4恢复数据库 备份和恢复组件是 SQL Server的重要组成部分 备份和恢复是数据库管理员维护数据库安全性和完整性 的主要操作。本章主要介绍了备份基础、备份类型、恢 复模型和备份设备,并详述了备份数据库和恢复数据库 的具体方法
第13章 数据备份与恢复 13-1 备份和恢复概述 13-2 备份设备 13-3 备份数据库 13-4 恢复数据库 备份和恢复组件是SQL Server 的重要组成部分. 备份和恢复是数据库管理员维护数据库安全性和完整性 的主要操作。本章主要介绍了备份基础、备份类型、恢 复模型和备份设备，并详述了备份数据库和恢复数据库 的具体方法
13-1备份和恢复概迒 备份和恢复组件是 SQL Server的重要组成部分.备份和恢复是数据库管理员维护 数据库安全性和完整性的主要操作。本章主要介绍了备份基础、备份类型、恢复模型和 备份设备,并详泷了备份数据库和恢复数据库的具体方法。 13-1-1备份基础 SQL Server2000采统提供了内置的安全性和数据保护机制,以防止非法登录者或 授权用户对 SQL Server数据库或数据造成破坏,但对于合法用户的数据操作不当或 存储媒体受损及运行 SQL Server的服务岀现崩溃性岀错等因素,则需要通过数据库的 备份和恢复来应对该类问题。 备份和恢复组件是 SQL Server的重要组成部分,为存储在 SQL Server数据库中的关 键数据提供重要的保护手段。通过适当设置,可以从多种故障中恢复所备份的数据。弓 起系统故障与数据损失的因秦主要包括 存储介质故障。倘若保存有数据库文件的存储介质∶磁盘驱动器岀现彻底崩溃,而用 户又未會进行过数据库备份,则有可能导致数据的丢失 备份是对 SQL Server数据库或事务日志进行拷贝,数据库备份记录了在进行备份这 操作时,数据库中所有数据的状态,如果数据库因意外而受损,这些备份文件将在数据 库恢复时被用来恢复数据库。一般而霅, SQL Server2000支持在线备份(一边备份 边进行其它操作),但在备份过程中切勿执行以下操作 创建或删除数据库文件 2创建索引与执行非日志操作 3动或手工缩小数据库或数据库文件大小 4倫若系统准备进行备份与以上各种操作正在进行中,则备份处理将被终止;倫 若正在备份过程中,打算 执行以上任何操作,则操作将失败,而备份继续进行
备份和恢复组件是SQL Server 的重要组成部分. 备份和恢复是数据库管理员维护 数据库安全性和完整性的主要操作。本章主要介绍了备份基础、备份类型、恢复模型和 备份设备，并详述了备份数据库和恢复数据库的具体方法。 13-1-1 备份基础 SQL Server 2000 系统提供了内置的安全性和数据保护机制，以防止非法登录者或非 授权用户对SQL Server 数据库或数据造成破坏，但对于合法用户的数据操作不当或 存储媒体受损及运行SQL Server 的服务出现崩溃性出错等因素，则需要通过数据库的 备份和恢复来应对该类问题。 备份和恢复组件是SQL Server 的重要组成部分，为存储在 SQL Server 数据库中的关 键数据提供重要的保护手段。通过适当设置，可以从多种故障中恢复所备份的数据。引 起系统故障与数据损失的因素主要包括： 存储介质故障。倘若保存有数据库文件的存储介质：磁盘驱动器出现彻底崩溃，而用 户又未曾进行过数据库备份，则有可能导致数据的丢失。 备份是对SQL Server 数据库或事务日志进行拷贝，数据库备份记录了在进行备份这一 操作时，数据库中所有数据的状态，如果数据库因意外而受损，这些备份文件将在数据 库恢复时被用来恢复数据库。一般而言，SQL Server 2000支持在线备份（一边备份 一边进行其它操作），但在备份过程中切勿执行以下操作： 1 创建或删除数据库文件。 2 创建索引与执行非日志操作。 3 动或手工缩小数据库或数据库文件大小。 4 倘若系统准备进行备份与以上各种操作正在进行中，则备份处理将被终止；倘 若正在备份过程中，打算 执行以上任何操作，则操作将失败，而备份继续进行。 13-1 备份和恢复概述
13-1-2备份类型 在 SQL Server2000中有三种方法备份数据库中的数据,他们彼此间的联合使用可获取较 好的备份和效用,这些方法为完全数据库备份、差异数据库备份和事务日志备份 1.完全数据库备份 完全数据库备份是指对数据库的完整备份,包括所有的数据以及数据库对象。该备份方法 首先将事务日志写到磁盘上,然后创建相同的数据库和数据库对象及拷贝数据。由于是对 数据库的完全备份,因而这种备份类型不仅速度较慢,而且将占用大量磁盘空间。 在对数据库进行完全备份时,所有未完成的事务或者发生在备份过程中的事务都将被忽略, 若使用完全数据库备份类型,则从开始备份到开始恢复这段时间内发生的任何针对数据库 的修改将无法恢复,所以我们总是在一定的要求或条件下扌使用这种备份类型。 通常,在进行完全数据库备份时常将其安排在晚间或采统闲暇之时,因为此时整个数据库 采统几乎不进行其它事务操作,从而可以提高数据库备份的速度。 2.差异数据库备份 差异数据库备份只记录自上次数据库备份后发生更改的数据,即是指将最近一次数据库备 份以来发生的数据变化备份起来,因而差异备份实际上是一种增量数据库备份。与完整数 据库备份相比差异数据库备份由于备份的数据量较小所以备份和恢复所用的时间较短。因 此可以更经常地备份,经常备份将减少丢失数据的危险。使用差异数据库备份将数据库还 原到差异数据库备份完成时的那一点。若要恢复到精确的故障点,必须使用事务日志备份。 在下列情况下可考虑使用差异数据库备份 自上次数据库备份后数据库中只有相对较少的数据发生了更改。如果多次修改相同的数据, 则差异数据库备份尤其有效 使用的是简单恢复模型,希望进行更频檠的备份,但不希望进行频繁的完整数据库备份。 使用的是完全恢复模型或大容量日志记录恢复模型,希望需要最少的时间在还原数据库时 滚事务日志备份
在SQL Server 2000 中有三种方法备份数据库中的数据，他们彼此间的联合使用可获取较 好的备份和效用，这些方法为完全数据库备份、差异数据库备份和事务日志备份。 1．完全数据库备份 完全数据库备份是指对数据库的完整备份，包括所有的数据以及数据库对象。该备份方法 首先将事务日志写到磁盘上，然后创建相同的数据库和数据库对象及拷贝数据。由于是对 数据库的完全备份，因而这种备份类型不仅速度较慢，而且将占用大量磁盘空间。 在对数据库进行完全备份时，所有未完成的事务或者发生在备份过程中的事务都将被忽略， 若使用完全数据库备份类型，则从开始备份到开始恢复这段时间内发生的任何针对数据库 的修改将无法恢复，所以我们总是在一定的要求或条件下才使用这种备份类型。 通常，在进行完全数据库备份时常将其安排在晚间或系统闲暇之时，因为此时整个数据库 系统几乎不进行其它事务操作，从而可以提高数据库备份的速度。 2．差异数据库备份 差异数据库备份只记录自上次数据库备份后发生更改的数据，即是指将最近一次数据库备 份以来发生的数据变化备份起来，因而差异备份实际上是一种增量数据库备份。与完整数 据库备份相比差异数据库备份由于备份的数据量较小所以备份和恢复所用的时间较短。因 此可以更经常地备份，经常备份将减少丢失数据的危险。使用差异数据库备份将数据库还 原到差异数据库备份完成时的那一点。若要恢复到精确的故障点，必须使用事务日志备份。 在下列情况下可考虑使用差异数据库备份： 自上次数据库备份后数据库中只有相对较少的数据发生了更改。如果多次修改相同的数据， 则差异数据库备份尤其有效。 使用的是简单恢复模型，希望进行更频繁的备份，但不希望进行频繁的完整数据库备份。 使用的是完全恢复模型或大容量日志记录恢复模型，希望需要最少的时间在还原数据库时 滚事务日志备份。 13-1-2 备份类型
3.事务日志备份 事务日志是自上次备份事务日志后对数据库执行的所有事务的一采列记录。可以使用 事务日志备份将数据库恢复到特定的即时点或恢复到故障点。通常,事务日志备份比完全数 据库备份使用的资源少。因此可以比数据库备份更经常地创建事务日志备份。经常备份将减 少丢失数据的危险。 在以下情况下我们常选择事务日志备份 不允许在最近一次数据库备份之后发生数据丢失或损坏现象。 存储备份文件的磁盘空间很小或者留给进行备份操作的时间有限。 准备把数据库恢复到发生失败的前一点,数据库变化较为频繁 由于事务日志备份仅对数据库事务日志进行备份,所以其需要的磁盘空间和备份时间都比完 全数据库备份少得多 正是基于此优点人们在备份时常籴用这样的策略:即每天进行一次数据库备份而以一个或几 个小时的频率备份事务日志,这样利用事务日志备份就可以将数据库恢复到任意一个创建事 务日志备份的时刻。 注:事务日志备份容量有时比数据库备份大。例如,数据库的事务率很高,从而导 致事务日志迅速增大。在这种情况下,应更经常地创建事务日志备份。事务日志 备份只能与完全恢复模型和大容量日志记录恢复模型一起使用。 13-1-3恢复模型 恢复就是把遭受破坏或丢失数据或岀现错误的数据库,恢复到原来的正常状态。该状态的效 果是由备份决定的,但是为了维护数据库的一致性,在备份中未完成的事务并不进行恢复。 可以为 SQL Server2000中数据库恢复有三种恢复模型以供选择,进而确定如何备份数据 以及能承受何种程度的数据丢失。下面是可以选择的三种恢复模型:
3．事务日志备份 事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。可以使用 事务日志备份将数据库恢复到特定的即时点或恢复到故障点。通常，事务日志备份比完全数 据库备份使用的资源少。因此可以比数据库备份更经常地创建事务日志备份。经常备份将减 少丢失数据的危险。 在以下情况下我们常选择事务日志备份： 不允许在最近一次数据库备份之后发生数据丢失或损坏现象。 存储备份文件的磁盘空间很小或者留给进行备份操作的时间有限。 准备把数据库恢复到发生失败的前一点，数据库变化较为频繁。 由于事务日志备份仅对数据库事务日志进行备份，所以其需要的磁盘空间和备份时间都比完 全数据库备份少得多。 正是基于此优点人们在备份时常采用这样的策略：即每天进行一次数据库备份而以一个或几 个小时的频率备份事务日志，这样利用事务日志备份就可以将数据库恢复到任意一个创建事 务日志备份的时刻。 注：事务日志备份容量有时比数据库备份大。例如，数据库的事务率很高，从而导 致事务日志迅速增大。在这种情况下，应更经常地创建事务日志备份。事务日志 备份只能与完全恢复模型和大容量日志记录恢复模型一起使用。 13-1-3 恢复模型 恢复就是把遭受破坏或丢失数据或出现错误的数据库，恢复到原来的正常状态。该状态的效 果是由备份决定的，但是为了维护数据库的一致性，在备份中未完成的事务并不进行恢复。 可以为 SQL Server 2000 中数据库恢复有三种恢复模型以供选择，进而确定如何备份数据 以及能承受何种程度的数据丢失。下面是可以选择的三种恢复模型：
简单恢复模型 简单恢复允许将数据库恢复到最新的备份,可以将数据库恢复到上次备份的即时 点,但无法将数据库还原到故障点或特定的即时点。若要还原到这些点,则应选 择完全恢复或大容量日志记录恢复。简单恢复的备份策略包括完全数据库备你和 差异备份。 2.完全恢复模型 完全恢复允许将数据库恢复到故障点状态,可以使用数据库备份和事务日志备份 提供对媒体故障的完全防范。如果一个或多个数据文件损坏,则媒体恢复叮以还 原所有已提交的事务。正在进行的事务将回滚。完全恢复提供将数据库恢复到故 障点或特定即时点的能力。为保证这种恢复程度,包括大容量操作(如 SELECT INTO、 CREATE INDEⅩ和大容量装载数据)在内的所有操作都将完整地记入日 志 完全恢复的备份策略包括完全数据库备份、差异备份和事务日志备份。 3.大容量日志记录恢复模型 大容量日志记录恢复模型允许大容量日志记录操作,提供对媒体故障的防范,并 对某些大规模或大容量复制操作提供最佳性能和最少的日志使用空间。 这些大容量复制操作的数据丢失程度要比完全恢复模型严重。虽然在完全恢复模 型下记录大容量复制操作的完整日志,但在大容量日志记录恢复模型下,只记录 这些操作的最小日志,而且无法逐个控制这些操作。在大容量日志记录恢复模型 中,数据文件损坏可能导致必须手工重做工作 大容量日志记录恢复的备份策略包括完全数据库备份、差异备份和日志备份。 根据执行的操作,可以有多类适合的模型。麦13-1陈迒了三种恢复模型的比较
1. 简单恢复模型 简单恢复允许将数据库恢复到最新的备份，可以将数据库恢复到上次备份的即时 点，但无法将数据库还原到故障点或特定的即时点。若要还原到这些点，则应选 择完全恢复或大容量日志记录恢复。简单恢复的备份策略包括完全数据库备份和 差异备份。 2．完全恢复模型 完全恢复允许将数据库恢复到故障点状态，可以使用数据库备份和事务日志备份 提供对媒体故障的完全防范。如果一个或多个数据文件损坏，则媒体恢复可以还 原所有已提交的事务。正在进行的事务将回滚。完全恢复提供将数据库恢复到故 障点或特定即时点的能力。为保证这种恢复程度，包括大容量操作（如 SELECT INTO、CREATE INDEX 和大容量装载数据）在内的所有操作都将完整地记入日 志。 完全恢复的备份策略包括完全数据库备份、差异备份和事务日志备份。 3．大容量日志记录恢复模型 大容量日志记录恢复模型允许大容量日志记录操作，提供对媒体故障的防范，并 对某些大规模或大容量复制操作提供最佳性能和最少的日志使用空间。 这些大容量复制操作的数据丢失程度要比完全恢复模型严重。虽然在完全恢复模 型下记录大容量复制操作的完整日志，但在大容量日志记录恢复模型下，只记录 这些操作的最小日志，而且无法逐个控制这些操作。在大容量日志记录恢复模型 中，数据文件损坏可能导致必须手工重做工作。 大容量日志记录恢复的备份策略包括完全数据库备份、差异备份和日志备份。 根据执行的操作，可以有多类适合的模型。表13-1陈述了三种恢复模型的比较
参数 特点 恢复态势 工作损失状况 简单恢复允许高性能大容可恢复到任何备必须重做自最新的数据库 模型 量复制操作,可份的尾端,随后或差异备份后所发生的更 收回日志间。须重做更改 改 完全恢复数据文件损失不 正常情况下无损失。若日 模型 导致工作损失, 可恢复到任意即时点。 可恢复到任意即志损坏则须重做自最新的 日志备份后所发生的更改 时点。 志记 量复制操作,大可恢复到任何备若日志损坏或自最新的日 大容量日允许高性能大容 录恢复模容量操作使用最份的尾端,随后志备份后发生操作则须重 做自上欢备份后所做的更 小的日志空间。 须重做更改。 改,否则将丢失工作数据
参 数 特 点 恢 复 态 势 工 作 损 失 状 况 简单恢复 模型 允许高性能大容 量复制操作，可 收回日志空间。 可恢复到任何备 份的尾端，随后 须重做更改。 必须重做自最新的数据库 或差异备份后所发生的更 改。 完全恢复 模型 数据文件损失不 导致工作损失， 可恢复到任意即 时点。 可恢复到任意即 时点。 正常情况下无损失。若日 志损坏则须重做自最新的 日志备份后所发生的更改。 大容量日 志记 录恢复模 型 允许高性能大容 量复制操作，大 容量操作使用最 小的日志空间。 可恢复到任何备 份的尾端，随后 须重做更改。 若日志损坏或自最新的日 志备份后发生操作则须重 做自上次备份后所做的更 改，否则将丢失工作数据
13-2备份设备 在进行备份前首先必须创建备份设备。备份设备是用来存储数据库事务日志或文件和文件 组备份的存储介质,其可以是硬盘、磁带或管道等。 13-2-1物理设备与逻辑设备 SQL Server使用物理设备名称或逻辑设备名称来标识备份设备。 物理备份设备是操作系统用来标识备份设备名称与引用管理备份设备的,如C: W Backups ACcounting Wbf. bak. 逻辑备份设备是用简单、形象的名称来有效地标识物理备份设备的别名或公用名。 逻辑设备名称永久地存储在 SQL Server内的桑统表中。使用逻辑备份设备的优点是弓 用它比引用物理设备名称简单。例如,逻辑设备名称可以是 bf_ Backup,而物理设备名 称则是C: WBackupsWAccountingw bf bak显得相对累赘。 注:在实施数据库备份或还原时,既可以使用物理设备名又可以使用逻辑备份设备名 使用企业管理器、 Transact-SQL语霅和向导可以很方便地管理数据库备份与恢复操作。 在进行数据库备份前得首先创建备份设备。 13-2-2创建与管理备份设备 1.使用企业管理器创建备份设备 在 SQL Server中使用企业管理器创建备份设备步骤如下 (1)展开服务器组及相关的服务器,选择【管理】节点,右击【备份】(如图13-1所 示),然后在快捷菜单中单击【新建备份设备】命爷,桑统弹出如图13-2所示的【创 建备份设备】属性对话框
在进行备份前首先必须创建备份设备。备份设备是用来存储数据库事务日志或文件和文件 组备份的存储介质，其可以是硬盘、磁带或管道等。 13-2-1 物理设备与逻辑设备 SQL Server 使用物理设备名称或逻辑设备名称来标识备份设备。 物理备份设备是操作系统用来标识备份设备名称与引用管理备份设备的，如C:\Backups \Accounting\bf.bak。 逻辑备份设备是用简单、形象的名称来有效地标识物理备份设备的别名或公用名。 逻辑设备名称永久地存储在 SQL Server 内的系统表中。使用逻辑备份设备的优点是引 用它比引用物理设备名称简单。例如，逻辑设备名称可以是 bf _Backup，而物理设备名 称则是 C:\Backups\Accounting\ bf.bak,显得相对累赘。 注：在实施数据库备份或还原时，既可以使用物理设备名又可以使用逻辑备份设备名。 使用企业管理器、Transact-SQL语言和向导可以很方便地管理数据库备份与恢复操作。 在进行数据库备份前得首先创建备份设备。 13-2 备份设备 13-2-2 创建与管理备份设备 1. 使用企业管理器创建备份设备 在SQL Server 中使用企业管理器创建备份设备步骤如下： （1）展开服务器组及相关的服务器，选择【管理】节点，右击【备份】（如图13-1 所 示），然后在快捷菜单中单击【新建备份设备】命令，系统弹出如图13-2 所示的【创 建备份设备】属性对话框
iii SQL Server Enterprise Manager[控制台根目录,□区 面控制台c)窗口)帮助 回x 操作(A)查看)工具红) 备份设备属性一新设备 中国X围度 米息旬暖C 树 备份1个项目 回,名称0:计算机管理 称 查看内容0 □控制台根目录 crosoft SQL Servers 回计算机管理 白 SQL Server组 白 ED YU(Windows95 or later C磁带动器名 由口数据库 口数据转换服务 °文件名(::1 Program Files \Microsoft SQL Serv 白管理 SQL Server代理 备份 匚新建备份设备⑩) 中当前活动 数据库维护计划备份数据库 由 SQL Server日志 确定 取消 帮助 复制 查看 由口安全性 从这里创建窗口 中口支持服务 刷新更 口 Meta Data Service导出列表) 图13-2创建备份设备属性 帮助Q 图13-1通过快捷菜单新建 备份设备
图13-1 通过快捷菜单新建 备份设备 图13-2 创建备份设备属性
(②)在【创建备份设备】属性对话框的【名称】框中输入该命名备份设备的逻辑名称 (3)在【文件名】文本框中,可直接输廴磁盘物理备份设备所使用的文件名和路径,也可单击浏览 .)按钮,在显示备份设备路径对话框中,再选择具体物理设备所使用的路径与文件名(可另取其 他名称),单击【确定】按钮,即完成备份设备的创建 注:在创建备份设备后,也可通过企业管理器查看或删除该备份设备。只要企业管理器中选中选择 【管理】节点下【备份】,在该对话框右侧即可查看具体备份设备;若要删除只要右击该设备在 弹出菜单中选择 Delete选项即可删除该备份设备 2.使用 Transact-SQL创建备份设备 在 SQL Server2000中,可以使用桑统存储过程 sp_addumpde vice实现创建数据库备份设备, 其语法格式为: sp_addumpdevice [@devtype = 'device_type [@alogicalname 'logical name aphysicalname =]'physicaLname' 其中 [@ devtype=‘ device_type’:表示设备类型,其值可以为:disk(磁盘)、pipe(命名管 道)和tape(磁带设备 @ logicalname=] "logical_name’:表示设备的逻辑名称,该逻辑名称用于 BACKUP和 RESTORE语句中 [@ physicalname=]‘ physical_name':表示备份设备的物理名称,使用不同的备份介 质其名称格式不同。物理名称必须遑照操作系统文件名称的规则或者网络设备的通用命 名规则,并且必须包括完整的路径。 注:采统存储过程 sp_addumpdevice不允许在事务中执行
（2）在【创建备份设备】 属性对话框的【名称】框中输入该命名备份设备的逻辑名称。 （3）在【文件名】文本框中，可直接输入磁盘物理备份设备所使用的文件名和路径，也可单击浏览 （…）按钮，在显示备份设备路径对话框中，再选择具体物理设备所使用的路径与文件名（可另取其 他名称），单击【确定】按钮，即完成备份设备的创建。 注：在创建备份设备后，也可通过企业管理器查看或删除该备份设备。只要企业管理器中选中选择 【管理】节点下【备份】，在该对话框右侧即可查看具体备份设备；若要删除只要右击该设备在 弹出菜单中选择Delete 选项即可删除该备份设备。 2．使用Transact-SQL创建备份设备 在SQL Server 2000中，可以使用系统存储过程 sp_addumpdevice实现创建数据库备份设备， 其语法格式为： sp_addumpdevice [@devtype =] 'device_type' [@logicalname =] 'logical_name' [@physicalname =] 'physical_name' 其中： [@devtype =] ‘device_type’：表示设备类型，其值可以为：disk（磁盘）、pipe（命名管 道）和tape（磁带设备）。 [@logicalname =] ‘logical_name’：表示设备的逻辑名称，该逻辑名称用于BACKUP和 RESTORE语句中。 [@physicalname =] ‘physical_name’：表示备份设备的物理名称，使用不同的备份介 质其名称格式不同。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命 名规则，并且必须包括完整的路径。 注：系统存储过程sp_addumpdevice不允许在事务中执行
【例13-1】创建一个磁盘备份设备 use master EXEC sp_addumpdevice disk, xxgl',c: Wxxgl dat 【例13-2】创建远程磁盘备份设备 use master EXEC sp_addumpdevice 'disk, networkdevice WWservername Sharename wpathWfilename ext 在 SQL Server2000中,可以使用桑统存储过程sp_ dropdevice用来删除 备份设备,sp_ dropdevice语法格式为 sp_dropdevice [@logicalname =]'device adelfile ='delfile] 其中 @ logicalname表示备份设备逻辑名。 defile表示相对应的物理备份设备文件。 【例13-3】删除备份设备。 EXEC sp_-dropdevice xxgl
【例13-1】创建一个磁盘备份设备。 use master EXEC sp_addumpdevice 'disk' , 'xxgl' , 'c:\xxgl.dat' 【例13-2】创建远程磁盘备份设备 use master EXEC sp_addumpdevice 'disk', 'networkdevice' , ‘\\servername\sharename\path\filename.ext‘ 在SQL Server 2000中，可以使用系统存储过程sp_dropdevice 用来删除 备份设备，sp_dropdevice 语法格式为： sp_dropdevice [@logicalname =] 'device' [ [@delfile =] 'delfile'] 其中： @logicalname 表示备份设备逻辑名。 @delfile 表示相对应的物理备份设备文件。 【例13-3】删除备份设备。 EXEC sp_dropdevice 'xxgl' Go