第九章数据库的备份和恢复第九章数据库的备份和恢复数据库的备份和恢复 主要内容主要内容 1.SQL Server备份的基本概念备份的基本概念 2.备份设备的概念和分类备份设备的概念和分类 3.如何实施数据备份如何实施数据备份 4.如何从备份的数据中恢复数据如何从备份的数据中恢复数据 5.恢复系统数据库恢复系统数据库 教学目标教学目标 掌握数据备份的方法掌握数据备份的方法 掌握从备份中恢复数据的方法和恢复系统数据库的方掌握从备份中恢复数据的方法和恢复系统数据库的方法法数据库的备份和恢复常见的造成数据库损坏的因素有：常见的造成数据库损坏的因素有：事务内部的故障事务内部的故障系统故障系统故障介质故障（硬故障）介质故障（硬故障） 计算机病毒计算机病毒 用户操作错误用户操作错误 数据库故障数据库故障数据库恢复技术数据库恢复技术问题一：如何建立备份数据？问题一：如何建立备份数据？问题二：如何利用备份数据实施数据库恢复？问题二：如何利用备份数据实施数据库恢复？数据库的备份和恢复9.1 备份的概述备份的概述1.备份的定义备份的定义备份备份是指制作数据库结构、对象和数据的是指制作数据库结构、对象和数据的拷贝，以便在数据库遭到破坏的时候能够拷贝，以便在数据库遭到破坏的时候能够修复数据库。修复数据库。数据库的备份和恢复2.备份的分类备份的分类备份可以分为种类型：备份可以分为种类型：静态备份：静态备份：备份期间不允许对数据库有备份期间不允许对数据库有任何存取、修改活动的备份方式任何存取、修改活动的备份方式动态备份：动态备份：备份期间可以对数据库有存备份期间可以对数据库有存取或修改，即备份和用户事务可以并发执行取或修改，即备份和用户事务可以并发执行1、静态备份和动态备份、静态备份和动态备份海量备份：海量备份：备份全部数据库备份全部数据库增量备份：增量备份：每次只备份上一次更新过的每次只备份上一次更新过的数据数据2、海量备份和增量备份、海量备份和增量备份数据库的备份和恢复.何时备份何时备份周期性数据库备份周期性数据库备份数据库备份是十分耗费时间和资源的，不能数据库备份是十分耗费时间和资源的，不能频繁进行。应该根据数据库的使用情况确定频繁进行。应该根据数据库的使用情况确定一个备份的周期。一个备份的周期。数据库的不定期备份数据库的不定期备份在数据库发生以下变化以后，应该进在数据库发生以下变化以后，应该进行数据库的备份：行数据库的备份： 创建、修改、删除数据库前应该备份数据库。创建、修改、删除数据库前应该备份数据库。创建了用户自定义对象。创建了用户自定义对象。增加或删除服务器的系统存储过程。增加或删除服务器的系统存储过程。修改了修改了master、msdb、model数据库。数据库。数据库的备份和恢复9.2 备份设备备份设备备份设备备份设备（backup device）是指）是指SQL Server中中存储数据库和事务日志备份拷贝的载体。存储数据库和事务日志备份拷贝的载体。备份设备可以被定义备份设备可以被定义成本地的磁盘文件成本地的磁盘文件、远程远程服务器上的磁盘文件服务器上的磁盘文件、磁带或者命名管道磁带或者命名管道。创建备份时，必须选择存放备份数据的备份创建备份时，必须选择存放备份数据的备份设备。当建立一个备份设备时，需要给其分设备。当建立一个备份设备时，需要给其分配配一个逻辑名一个逻辑名和和一个物理名一个物理名。数据库的备份和恢复1.备份设备的类型备份设备的类型）磁盘设备）磁盘设备磁盘备份设备是指被定义成备份设备磁盘备份设备是指被定义成备份设备文件的硬盘或其它磁盘存储媒体。引用文件的硬盘或其它磁盘存储媒体。引用磁盘备份设备与引用任何其它操作系统磁盘备份设备与引用任何其它操作系统文件是一样的。文件是一样的。如果在网络上将文件备份到远程计算机如果在网络上将文件备份到远程计算机上的磁盘，需使用通用命名规则名称，上的磁盘，需使用通用命名规则名称，以以 ServernameSharenamePathFile 格式指定文件的位置。格式指定文件的位置。数据库的备份和恢复2）磁带设备）磁带设备磁带备份设备的用法与磁盘设备相同，但必磁带备份设备的用法与磁盘设备相同，但必须将磁带设备物理连接到运行须将磁带设备物理连接到运行 SQL Server 实实例的计算机上。例的计算机上。SQL Server不支持磁带设备的不支持磁带设备的远程备份。远程备份。 SQL Server基于基于Windows98的桌面版本中，的桌面版本中，不支持磁带备份设备。若要将不支持磁带备份设备。若要将SQL Server的数的数据备份到磁带，应使用据备份到磁带，应使用 Windows NT 4.0 或或 Windows 2000 支持的磁带备份设备或磁带驱动支持的磁带备份设备或磁带驱动器。器。第一个安装到计算机上的磁带设备被分配的名字是第一个安装到计算机上的磁带设备被分配的名字是.Tape0，第二个磁带设备的名字是，第二个磁带设备的名字是.Tape1，以后安装的磁带设备的命名以此类推。以后安装的磁带设备的命名以此类推。数据库的备份和恢复3）命名管道备份设备）命名管道备份设备SQL Server系统提供了把备份放在系统提供了把备份放在Name Pipe命命名管道上的能力，允许第三方软件供应商提供命名管道上的能力，允许第三方软件供应商提供命名管道备份设备来备份和恢复名管道备份设备来备份和恢复SQL Server数据库。数据库。但要注意，命名管道备份设备不能通过企业管理但要注意，命名管道备份设备不能通过企业管理器创建和管理。器创建和管理。数据库的备份和恢复 展开服务器组，展开服务器组，然后展开服务器然后展开服务器。 展开展开“管理管理”文件夹，右击文件夹，右击“备份备份”，然后，然后单击单击“新建备份设备新建备份设备”命令。命令。1）使用企业管理器建立备份设备）使用企业管理器建立备份设备.建立备份设备建立备份设备数据库的备份和恢复 在在“名称名称”框中框中输入该备份设备的名称输入该备份设备的名称，这是备，这是备份设备的逻辑名。份设备的逻辑名。 如果建立一个磁盘备份设备，如果建立一个磁盘备份设备，单击单击“文件名文件名”，执行下列操作之一以确定备份设备的物理名。执行下列操作之一以确定备份设备的物理名。注意：如果窗口中没有列出磁带设备，则表明注意：如果窗口中没有列出磁带设备，则表明本地计算机无法检测到磁带设备。本地计算机无法检测到磁带设备。数据库的备份和恢复2）使用）使用T-SQL语句建立备份设备语句建立备份设备系统存储过程系统存储过程sp_addumpdevice可以用来添加备份设备。可以用来添加备份设备。其语法形式如下：其语法形式如下： sp_addumpdevice device_type , logical_name , physical_name device_type：备份设备的类型。可以是以下三种备份设备的类型。可以是以下三种类型中的一种：类型中的一种：disk 以硬盘文件作为备份设备；以硬盘文件作为备份设备；pipe 命名管道备份设备；命名管道备份设备；tape 磁带备份设备。磁带备份设备。 logical_name：备份设备的逻辑名，用于备份设备的逻辑名，用于SQL Server管理备份设备。管理备份设备。 physical_name：备份设备的物理名称。物理名：备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。备的通用命名规则，并且必须包括完整的路径。数据库的备份和恢复【例例】分别创建一个本地磁盘、网络磁盘和磁带备分别创建一个本地磁盘、网络磁盘和磁带备份设备。份设备。/* 创建本地磁盘备份设备创建本地磁盘备份设备 */USE StudentEXEC sp_addumpdevice disk , DiskBak_Student , c:dumpStu.bak/* 创建一个磁带备份设备创建一个磁带备份设备 */EXEC sp_addumpdevice tape , TapeBak_Student , .tape0/* 创建一个网络磁盘备份设备创建一个网络磁盘备份设备 */EXEC sp_addumpdevice disk ,NetBak_Stu , BackSrvSQLBakStu.bak数据库的备份和恢复.管理备份设备管理备份设备）列出备份设备）列出备份设备 可以使用企业管理器和执行系统存储过程可以使用企业管理器和执行系统存储过程sp_helpdevice来列出服务器上的备份设备。来列出服务器上的备份设备。 使用企业管理器列出备份设备的步骤如下：使用企业管理器列出备份设备的步骤如下：展开服务器组，选择并连接一个服务器展开服务器组，选择并连接一个服务器展开展开“管理管理”，单击，单击“备份备份”将显示服务器将显示服务器上的所有备份设备。上的所有备份设备。数据库的备份和恢复）查看备份设备信息和删除备份设备）查看备份设备信息和删除备份设备使用使用T-SQL语句语句使用使用RESTORE 和和RESTORE HEADERONLY可分别查看备份设备中备份集内包含的数据库和可分别查看备份设备中备份集内包含的数据库和事务日志文件列表以及查看特定备份设备上所有事务日志文件列表以及查看特定备份设备上所有备份集的备份首部信息备份集的备份首部信息例：例：RESTORE From DiskBak_StuRESTORE HEADERONLY From DiskBak_Stu数据库的备份和恢复删除备份设备删除备份设备sp_dropdevicelogicalname=device/* 删除建立的磁带备份设备删除建立的磁带备份设备TapeBak_Student */EXEC sp_dropdevice TapeBak_Student 使用企业管理器使用企业管理器）展开服务器组，然后展开服务器）展开服务器组，然后展开服务器）展开）展开“管理管理”，然后单击，然后单击“备份备份”）在详细信息窗格中右键单击特定的命名备）在详细信息窗格中右键单击特定的命名备份设备，然后根据需要可进行相关信息的查看份设备，然后根据需要可进行相关信息的查看以及删除操作以及删除操作数据库的备份和恢复9.3 备份类型备份类型SQL Server 支持的备份类型包括（支持的备份类型包括（4类）：类）：完整数据库备份完整数据库备份 备份包括事务日志在内的整个数据库。备份包括事务日志在内的整个数据库。差异数据库备份差异数据库备份 在完整数据库备份之间执行差异数据库备份。在完整数据库备份之间执行差异数据库备份。事务日志备份事务日志备份 日志备份序列提供了连续的事务信息链，可支持日志备份序列提供了连续的事务信息链，可支持从数据库、差异或文件备份中快速恢复数据。从数据库、差异或文件备份中快速恢复数据。数据库文件和文件组备份数据库文件和文件组备份 数据库的备份和恢复1 完整数据库备份完整数据库备份与事务日志备份和差异数据库备份相比，与事务日志备份和差异数据库备份相比，完整数据库备份中的每个备份使用的存储空完整数据库备份中的每个备份使用的存储空间更多。间更多。另一方面，由于完整数据库备份不能频另一方面，由于完整数据库备份不能频繁地创建，因此，不能最大程度地恢复丢失繁地创建，因此，不能最大程度地恢复丢失的数据。的数据。 完整数据库备份是指所有的数据库对象、完整数据库备份是指所有的数据库对象、数据和事务日志都将被备份。数据和事务日志都将被备份。特点：特点：数据库的备份和恢复一般来说完整性备份应该与后面的备份方法结一般来说完整性备份应该与后面的备份方法结合使用才能最大程度地保护数据库数据，只有合使用才能最大程度地保护数据库数据，只有以下几种情况下可以单独使用：以下几种情况下可以单独使用：）系统中所存数据重要性很低）系统中所存数据重要性很低）系统中所存的数据可以很容易再创建）系统中所存的数据可以很容易再创建）数据库不经常被修改）数据库不经常被修改数据库的备份和恢复差异备份差异备份差异数据库备份只记录自上次完整数据库备份差异数据库备份只记录自上次完整数据库备份后发生更改的数据。后发生更改的数据。在执行差异数据库备份时注意如下几点：在执行差异数据库备份时注意如下几点： 创建定期的数据库备份。创建定期的数据库备份。 在每个数据库备份之间定期创建差异数据在每个数据库备份之间定期创建差异数据库备份。库备份。 应该在两个差异数据库备份的时间间隔内应该在两个差异数据库备份的时间间隔内执行事务日志备份，把数据损失的风险降到执行事务日志备份，把数据损失的风险降到最小。最小。数据库的备份和恢复事务日志备份事务日志备份事务日志是自上次备份事务日志后对数据库执事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。行的所有事务的一系列记录。采用事务日志备份，在故障发生时尚未提交的采用事务日志备份，在故障发生时尚未提交的事务将会丢失。所有在故障发生时已经完成的事务将会丢失。所有在故障发生时已经完成的事务都将会被恢复自动恢复。事务都将会被恢复自动恢复。一般情况下，事务日志备份比完整数据库备份一般情况下，事务日志备份比完整数据库备份使用的资源少。使用的资源少。可以使用事务日志备份将数据库恢复到特定的可以使用事务日志备份将数据库恢复到特定的即时点或恢复到故障点时的状态。即时点或恢复到故障点时的状态。作用：作用：事务日志备份有时比完整数据库备份大。事务日志备份有时比完整数据库备份大。数据库的备份和恢复4数据库文件和文件组备份数据库文件和文件组备份SQL Server 2000支持备份或还原数据库中的支持备份或还原数据库中的个别文件或文件组。个别文件或文件组。这是一种相对较完善的备份和还原过程，通常这是一种相对较完善的备份和还原过程，通常用在具有较高可用性要求的超大型数据库用在具有较高可用性要求的超大型数据库（VLDB）中。）中。如果可用的备份时间不足以支持完整数据库备份，如果可用的备份时间不足以支持完整数据库备份，则可以在不同的时间备份数据库的子集。则可以在不同的时间备份数据库的子集。注意注意：文：文件备份和还原操作必须与事务日志备件备份和还原操作必须与事务日志备份一起使用。份一起使用。还可以从完整数据库备份集中还原文件和文件组。还可以从完整数据库备份集中还原文件和文件组。数据库的备份和恢复9.4 执行备份执行备份1 备份前的准备备份前的准备为了将系统安全、完整地备份，应该在具体执行为了将系统安全、完整地备份，应该在具体执行备份之前，根据系统的环境和实际需要制定一个备份之前，根据系统的环境和实际需要制定一个切实可行的备份计划，确保数据库的安全。切实可行的备份计划，确保数据库的安全。备份计划主要考虑备份计划主要考虑以下几个方面：以下几个方面： 确定备份的频率。确定备份的频率。 确定备份的内容。确定备份的内容。 确定备份的介质。确定备份的介质。 确定备份的方式。确定备份采用动态备份确定备份的方式。确定备份采用动态备份还是静态备份。还是静态备份。数据库的备份和恢复 估计备份需要的存储空间量。估计备份需要的存储空间量。 确定备份的人员。应该指定专人负责数据库确定备份的人员。应该指定专人负责数据库的备份，一般只有下列角色的成员才可以备份的备份，一般只有下列角色的成员才可以备份数据库：服务器角色数据库：服务器角色sysadmin、数据库角色、数据库角色db_owner和和db_backupoperator。 是否使用备份服务器。是否使用备份服务器。 确定备份存储的期限和存放地点：备份应该确定备份存储的期限和存放地点：备份应该存储安全的地方并保存适当的期限。存储安全的地方并保存适当的期限。数据库的备份和恢复2 使用企业管理器进行备份使用企业管理器进行备份 展开服务器组，然后展开服务器。展开服务器组，然后展开服务器。 展开展开“数据库数据库”文件夹，右击要备份的数文件夹，右击要备份的数据库名，指向据库名，指向“所有任务所有任务”子菜单，然后单子菜单，然后单击击“备份数据库备份数据库”命令。命令。 在在“名称名称”框内，输入备框内，输入备份集名称。在份集名称。在“描述描述”框中框中输入对备份集的描述（可选）。输入对备份集的描述（可选）。在在“目的目的”选项下，单击选项下，单击“磁磁带带”或或“磁盘磁盘”。如果不能选。如果不能选择，则有可能是因为没有磁带择，则有可能是因为没有磁带设备。然后指定备份目的地。设备。然后指定备份目的地。如果没出现备份目的地，则单如果没出现备份目的地，则单击击“添加添加”以添加现有的目的以添加现有的目的地或创建新目的地。地或创建新目的地。数据库的备份和恢复在在“重写重写”选项下，如单击选项下，如单击“追追加到媒体加到媒体”，则将备份追加到备，则将备份追加到备份设备现有的备份中；如单击份设备现有的备份中；如单击“重写现有媒体重写现有媒体”，将重写备份设，将重写备份设备中现有的备份。备中现有的备份。选择选择“调度调度”复选框，设置备份复选框，设置备份操作在以后执行或定期执行（可操作在以后执行或定期执行（可选）。选）。数据库的备份和恢复9.5 从不同备份中恢复数据库从不同备份中恢复数据库数据库恢复是数据库管理系统管理的另一项重数据库恢复是数据库管理系统管理的另一项重要工作要工作数据库恢复是在非正常状态下进行的，比如数据库恢复是在非正常状态下进行的，比如硬件故障、软件瘫痪以及误操作硬件故障、软件瘫痪以及误操作执行数据库恢复以前，应执行数据库恢复以前，应注意注意以下以下两点两点： 在数据库恢复前，应该删除故障数据库，在数据库恢复前，应该删除故障数据库，以便删除对故障数据库的任何引用。以便删除对故障数据库的任何引用。 数据库恢复之前，必须限制用户对数据库数据库恢复之前，必须限制用户对数据库的访问，数据库的恢复是静态的，应使用企业的访问，数据库的恢复是静态的，应使用企业管理器或系统存储过程管理器或系统存储过程sp_dbotion设置数据库设置数据库为单用户。为单用户。数据库的备份和恢复还原差异数据库备份的顺序为：还原差异数据库备份的顺序为：先还原最新的先还原最新的完整数据库备份，然后还原最后一次的差异数完整数据库备份，然后还原最后一次的差异数据库备份。据库备份。还原事务日志备份的顺序为：还原事务日志备份的顺序为：先还原最新的完先还原最新的完整数据库备份，然后还原最后一次完全备份之整数据库备份，然后还原最后一次完全备份之后创建的所有事务日志备份，按顺序恢复完全后创建的所有事务日志备份，按顺序恢复完全备份之后发生在数据库上的所有操作。备份之后发生在数据库上的所有操作。数据库的备份和恢复9.5 恢复系统数据库恢复系统数据库在备份用户数据库时需要备份系统数据库。这在备份用户数据库时需要备份系统数据库。这使得在系统或数据库发生故障（例如，如果硬使得在系统或数据库发生故障（例如，如果硬盘发生故障）时可以重建系统。需要经常备份盘发生故障）时可以重建系统。需要经常备份的系统数据库包括的系统数据库包括master和和msdb。如果。如果model数据库被修改过，也需要进行定期备份。数据库被修改过，也需要进行定期备份。对对master数据库进行备份的操作与备份用数据库进行备份的操作与备份用户数据库相似，需要注意的是户数据库相似，需要注意的是只能创建只能创建master数据库的完整数据库备份数据库的完整数据库备份。如果。如果master数据库被严重损坏，则可能无法启动数据库被严重损坏，则可能无法启动SQL Server实例。实例。数据库的备份和恢复有两种方法将有两种方法将 master 数据库返回到可用状态：数据库返回到可用状态：从当前备份还原从当前备份还原用重建主控实用工具完全重建用重建主控实用工具完全重建重建主控实用工具重建主控实用工具关闭关闭SQL Server2000，打开运行对话框，输入，打开运行对话框，输入Rebuildm.exe，该工具位于，该工具位于Program FilesMicrosoft SQL Server80ToolsBinn 文件文件夹中夹中数据库的备份和恢复使用重建主控实用工具重建使用重建主控实用工具重建master数据库，会数据库，会导致以前存储在导致以前存储在 master 数据库中的所有数据永数据库中的所有数据永久丢失。久丢失。SQL Server仍能访问其它数据库。仍能访问其它数据库。如果系统可以访问如果系统可以访问master数据库（至少部分可用）数据库（至少部分可用）而能够启动而能够启动SQL Server实例，则可以从完整数据库实例，则可以从完整数据库备份中还原备份中还原master数据库。数据库。数据库的备份和恢复如果由于如果由于master数据库严重损坏而无法启动数据库严重损坏而无法启动SQL Server实例，怎么办？实例，怎么办？首先需要使用重建主控实用工具重建首先需要使用重建主控实用工具重建master数据库，然后才可以用普通方数据库，然后才可以用普通方法还原当前数据库备份。法还原当前数据库备份。数据库的备份和恢复生成生成SQL脚本脚本在企业管理器中，打开数据库，在某个（些）表在企业管理器中，打开数据库，在某个（些）表上点右键上点右键-复制，然后在记事本或查询分析器中复制，然后在记事本或查询分析器中粘贴，就可以获得创建该表的粘贴，就可以获得创建该表的 SQL 语句。语句。 在企业管理器中，打开数据库，在某个（些）表在企业管理器中，打开数据库，在某个（些）表上点右键上点右键-所有任务所有任务-生成生成 SQL 脚本，同样可脚本，同样可以获得创建该表的以获得创建该表的 SQL 语句。语句。 使用使用“生成生成 SQL 脚本脚本”，功能要强大得多，可以删，功能要强大得多，可以删除目的表，可以包含索引，可以包含触发器等等。另除目的表，可以包含索引，可以包含触发器等等。另外，可以说外，可以说“生成生成 SQL 脚本脚本”是智能的，它的目的是智能的，它的目的就是，根据这个数据库的表在另外一个数据库中创建就是，根据这个数据库的表在另外一个数据库中创建一个相同或相似的表。一个相同或相似的表。 数据库的备份和恢复导入导入导出数据导出数据数据库的备份和恢复