第11章 数据备份与还原

SQLServer2008数据库应用教程

第11章数据备份与还原123了解备份所需要的常用设备掌握备份数据的操作方法学习目标了解备份的概念熟悉复制数据库的操作技巧掌握数据的导入与导出掌握恢复数据的操作方法456主要内容11.1备份与还原概述11.2备份数据操作11.3还原数据操作11.1备份与还原概述用户使用数据库是因为要利用数据库来管理和操作数据，数据对于用户来说是非常宝贵的资产。数据存放在计算机上，但是即使是最可靠的硬件和软件也会出现系统故障或产品故障。所以，应该在意外发生之前做好充分的准备工作，以便在意外发生之后有相应的措施能快速地恢复数据库的运行，并使丢失的数据量减少到最小。

11.1.1备份与还原的基本概念1.备份

备份是制作数据库的副本，即将数据库中的部分或全部内容复制到其它的存储介质（如磁盘）上保存起来的过程，以便在数据库遭到破坏时能够修复数据库。2.还原

还原指将数据库备份加载到服务器中，使数据库恢复到备份时的正常状态。这一状态是由备份决定的，但是为了维护数据库的一致性，在备份中未完成的事务不能进行还原。11.1.2数据备份的类型在SQLServer2008中有四种备份类型：1.完整数据库备份完整数据库备份就是备份整个数据库。它备份数据库文件、这些文件的地址以及事务日志的某些部分。这是任何备份策略中都要求完成的第一种备份类型，因为其它所有备份类型都依赖于完整备份。2.差异数据库备份差异备份是指将从最近一次完全数据库备份以后发生改变的数据。3.事务日志备份它并不备份数据库本身，只记录事务日志的适当部分，明确地说，自从上一个事务以来已经发生了变化的部分。4.数据库文件和文件组备份当一个数据库很大时，对整个数据库进行备份可能会花很多的时间，这时可以采用文件和文件组备份，即对数据库中的部分文件或文件组进行备份。11.1.3恢复模式

备份和还原操作是在某种“恢复模式”下进行。恢复模式是一个数据库的属性，它用于控制数据库和还原操作的基本行为，它控制了将事务记录在日志中的方式、事务日志是否需要备份和可用的还原操作等。选择不同的恢复模式可以简化恢复计划、简化备份和恢复的过程。在SQLServer2008中可以使用的“恢复模式”有以下三种：1.简单恢复模式

在简单恢复模式下，简略的记录大多数事务，所记录的信息只是为了确保在系统崩溃或还原数据备份之后数据库的一致性。2.完整恢复模式

在完整恢复模式下，完整的记录了所有的事务，并保留所有事务的日志记录。3.大容量日志恢复模式

与完整恢复模式（完整记录所有事务）相反，大容量日志恢复模式只对大容量操作（如索引创建和大容量加载）进行最小记录，这样可以大大提高数据库的性能，常用作完整恢复模式的补充。11.2备份数据操作1.备份设备的分类备份设备就是用来存储数据库、事务日志或文件和文件组备份的存储介质。常见的备份设备可以分为3种类型：磁盘备份设备、磁带备份设备和物理设备和逻辑设备。1）磁盘备份设备

磁盘备份设备就是存储在硬盘或其它磁盘媒体上的文件，与常规操作系统文件一样。2）磁带备份设备

磁带备份设备的用法与磁盘设备相同，不过磁带设备必须物理连接到运行SQLServer2008实例的计算机上。3）逻辑备份设备

物理备份设备名称主要用来供操作系统对备份设备进行引用和管理，如：C:\Backups\GradeMis\Full.bak。11.2.1备份设备2.创建备份设备

在SQLServer2008中创建设备的方法有两种：一是通过图形化工具创建，二是使用系统存储过程sp_addumpdevice创建。1）使用图形界面方式创建备份设备使用图形界面方式创建备份设备的操作步骤如下：（1）在【对象资源管理器】中，单击服务器名称以展开服务器树。（2）展开【服务器对象】节点，然后用鼠标右键单击【备份设备】选项。（3）从弹出的菜单中选择【新建备份设备】命令，打开【备份设备】窗口。（4）在【备份设备】窗口，输入设备名称并且指定该文件的完整路径，这里创建一个名称为“学生成绩管理系统备份”的备份设备。（5）单击【确定】按钮，完成备份设备的创建。展开【备份设备】节点，就可以看到刚刚创建的名称为“学生成绩管理系统备份”备份设备。11.2.1备份设备2.创建备份设备2）使用系统存储过程SP_ADDUMPDEVICE创建备份设备SP_ADDUMPDEVICE的基本语法如下：SP_ADDUMPDEVICE[@devtype=]‘device_type’,[@logicalname=]‘logical_name’,[@physicalname=]‘physical_name’[,{[@cntrltype=]controller_type|[@devstatus=]‘device_status’}

]【例11.1】创建一个名称为Test的备份设备。代码如下：USEmasterGOEXECsp_addumpdevice‘disk’,‘Test’,‘C:\test.bak’11.2.1备份设备3.管理备份设备1）查看备份设备

可以通过两种方式查看服务器上的所有备份设备，一种是通过使用图形界面方式，另一种是通过系统存储过程SP_HELPDEVICE。（1）使用图形界面方式查看备份设备

使用图形界面方式查看所有备份设备，操作步骤如下：

①在【对象资源管理器】中，单击服务器名称以展开服务器树。

②展开【服务器对象】→【备份设备】节点，就可以看到当前服务器上已经创建的所有备份设备。（2）使用系统存储过程SP_HELPDEVICE

使用系统存储过程SP_HELPDEVICE也可以查看服务器上每个设备的相关信息。11.2.1备份设备2）删除备份设备

如果不再需要的备份设备，可以将其删除，删除备份设备后，其上的数据都将丢失，删除备份设备也有两种方式，一种是使用图形界面方式，另一种是使用系统存储过程SP_DROPDEVICE。（1）使用图形界面方式

使用图形界面方式可以删除备份设备。例如将备份设备Test删除，操作步骤如下：

①在【对象资源管理器】中，单击服务器名称以展开服务器树。

②展开【服务器对象】→【备份设备】节点，右击要删除的备份设备Test，在弹出的命令菜单中选择【删除】命令，打开【删除对象】窗口。③在【删除对象】窗口单击【确定】按钮，即完成对该备份设备的删除操作。（2）使用系统存储过程SP_DROPDEVICE

使用SP_DROPDEVICE系统存储过程将服务器中备份设备删除，并能删除操作系统文件。具体语句如下所示：SP_DROPDEVICE‘备份设备名’[,‘DELETE’]11.2.2备份命令1.创建完整备份对数据库进行完整备份的语法如下：BACKUPDATABASEdatabase_nameTO<backup_device>[n][WITH[[,]NAME=backup_set_name][[,]DESCRIPITION=’TEXT’][[,]{INIT|NOINIT}][[,]{COMPRESSION|NO_COMPRESSION}]11.2.2备份命令【例11.2】使用BACKUP命令对数据库“学生成绩管理系统”做一次完整备份，备份设备为以前创建好的“学生成绩管理系统备份”本地磁盘设备，并且此次备份覆盖以前所有的备份。代码如下：BACKUPDATABASE学生成绩管理系统TODISK=‘学生成绩管理系统备份’WITHINIT,NAME=‘学生成绩管理系统完整备份’,DESCRIPTION=‘thisisthefullbackupof学生成绩管理系统’11.2.2备份命令2.创建差异备份差异备份的语法如下所示：BACKUPDATABASEdatabase_nameTO<backup_device>[n]WITHDIFFERENTIAL[[,]NAME=backup_set_name][[,]DESCRIPITION=’TEXT’][[,]{INIT|NOINIT}][[,]{COMPRESSION|NO_COMPRESSION}]11.2.2备份命令【例11.3】对数据库“学生成绩管理系统”做一次差异备份。代码如下：BACKUPDATABASE学生成绩管理系统TODISK=‘学生成绩管理系统备份’WITHDIFFERENTIAL,NOINIT,NAME=‘学生成绩管理系统差异备份’,DESCRIPTION=‘thisisdifferentialbackupof学生成绩管理系统ondisk’11.2.2备份命令3.创建事务日志备份语法格式如下：BACKUPLOGdatabase_nameTO<backup_device>[n]WITH[[,]NAME=backup_set_name][[,]DESCRIPITION=’TEXT’][[,]{INIT|NOINIT}][[,]{COMPRESSION|NO_COMPRESSION}]11.2.2备份命令【例11.4】对数据库“学生成绩管理系统”做事务日志备份，要求追加到现有的备份设备“学生成绩管理系统备份”上。完成上述备份，可以使用如下代码：BACKUPLOG学生成绩管理系统TODISK=‘学生成绩管理系统备份’WITHNOINIT,NAME=‘学生成绩管理系统事务日志备份’,DESCRIPTION=‘thisistransactionbackupof学生成绩管理系统ondisk’11.2.2备份命令4.创建文件组备份语法如下所示：BACKUPDATABASEdatabase_name<file_or_filegroup>[n]TO<backup_device>[n]WITHoptions11.2.2备份命令5.备份压缩

数据库执行备份操作，需要占用一定的磁盘空间。如果公司的数据库非常庞大，那么对数据库的备份需要的空间将会十分惊人。对于数据库管理员来说，这是一件十分头疼的事情。幸运的是，在SQLServer2008中新增了数据压缩功能。通常，数据压缩总是和节约硬盘、更小的物理文件、备份次数的减少联系在一起。而对于SQLServer2008的数据压缩而言，主要的目的是实际的表的尺寸的减小。

数据压缩的某些好处包括以下几点：通过减少I/O和提高缓存命中率来提升查询性能提供对真实DW实际数据2倍到7倍的压缩比率和其它特点是正交的对数据和索引都可用

使用BACKUP语句的WITHCOMPRESSION选项，可以实现压缩备份的功能。11.2.3使用管理工具备份1.创建完整备份

例如，需要对“学生成绩管理系统”进行一次完整备份，使用图形化工具对其进行完整备份的操作步骤如下：（1）启动【SQLServerManagementStudio】，连接服务器。（2）在对象资源管理器中，展开【数据库】节点，右击【学生成绩管理系统】数据库，在弹出的菜单中选择【属性】，打开【数据库属性】窗口。（3）在【选项】页面，确保恢复模式为完整恢复模式，如图11.9所示。图11.9选择恢复模式11.2.3使用管理工具备份（4）单击【确定】按钮应用修改结果。（5）右击数据库“学生成绩管理系统”，从弹出的菜单中选择【任务】→【备份】命令，打开【备份数据库】窗口，如图11.10所示。图11.10备份数据库11.2.3使用管理工具备份（6）在【备份数据库】窗口中，从【数据库】下拉菜单中选择“学生成绩管理系统”数据库；【备份类型】项选择“完整”，保留【名称】文本框的内容不变。（7）设置备份到磁盘的目标位置，通过单击【删除】按钮，删除已存在默认生成的目标，然后单击【添加】按钮，打开【选择备份目标】对话框，启用【备份设备】选项，选择以前建立的“学生成绩管理系统备份”备份设备。（8）单击【确定】按钮返回【备份数据库】窗口，就可看到【目标】下面的文本框将增加一个“学生成绩管理系统备份”备份设备。（9）单击【选项】选项，打开【选项】页面，选中【覆盖所有现有备份集】选项，该选项用于初始化新的设备或覆盖现在的设备；选中【完成后验证备份】复选框，该选项用来核对实际数据库与备份副本，并确保它们在备份完成之后一致。具体设置情况。（10）单击【确定】按钮，完成对数据库的备份。11.2.3使用管理工具备份2.创建差异备份

下面使用SQLServerManagementStudio在上一节创建的永久备份设备“学生成绩管理系统备份”上创建一个数据库“学生成绩管理系统”的一个差异备份。操作过程如下：（1）启动【SQLServerManagementStudio】，连接服务器。（2）在对象资源管理器中，展开【数据库】节点，右击【学生成绩管理系统】数据库，在弹出的菜单中选择【任务】→【备份】命令，打开【备份数据库】窗口。（3）在【备份数据库】窗口，从【数据库】下拉菜单中选择“学生成绩管理系统”数据库；【备份类型】项选择“差异”；保留【名称】文本框的内容不变；在【目标】项下面确保列了【学生成绩管理系统备份】设备。如图11.14所示。11.2.3使用管理工具备份图11.14备份数据库11.2.3使用管理工具备份（4）单击【选项】选项，打开【选项】页面，启用【追加到现有备份集】选项，以免覆盖现有的完整备份；选中【完成后验证备份】复选框，该选项用来核对实际数据库与备份副本（或者称为拷贝），并确保它们在备份完成之后一致。具体设置情况如图11.15所示。（5）完成设置后，单击【确定】开始备份，完成备份将弹出备份完成窗口。图11.15差异备份的【选项】页面11.2.3使用管理工具备份3.创建事务日志备份

下面使用SQLServerManagementStudio工具在前面创建的永久备份设备“学生成绩管理系统备份”上创建一个数据库“学生成绩管理系统”的一个事务日志备份。操作过程如下：（1）启动【SQLServerManagementStudio】，连接服务器。（2）在对象资源管理器中，展开【数据库】节点，右击【学生成绩管理系统】数据库，在弹出的命令菜单中选择【任务】→【备份】命令，打开【备份数据库】窗口。（3）在【备份数据库】窗口，从【数据库】下拉菜单中选择【学生成绩管理系统】数据库；【备份类型】项选择“事务日志”；保留【名称】文本框的内容不变；在【目标】项下面确保出现“学生成绩管理系统系统备份”设备。如图11.17所示。11.2.3使用管理工具备份图11.17创建事务日志备份11.2.3使用管理工具备份（4）单击【选项】选项，打开【选项】页面，启用【追加到现有备份集】选项，以免覆盖现有的完整和差异备份；选中【完成后验证备份】复选框，该选项用来核对实际数据库与备份副本（或者称为拷贝），并确保它们在备份完成之后一致，并且选择【截断事务日志】选项。具体设置情况如图11.18所示。（5）完成设置后，单击【确定】开始备份，完成后将弹出备份完成窗口。图11.18事务日志备份的【选项】页面11.2.3使用管理工具备份4.创建文件组备份

使用SQLServerManagementStudio工具执行文件组备份的具体步骤如下所示：（1）启动【SQLServerManagementStudio】，连接服务器。（2）在对象资源管理器中，展开【数据库】节点，右击【学生成绩管理系统】数据库，在弹出的命令菜单中选择【任务】→【备份】命令，打开【备份数据库】窗口。（3）在【备份数据库】窗口的备份组件下选择【文件和文件组】就打开【选择文件和文件组】对话框，如图11.20所示。图11.20选择文件组11.2.3使用管理工具备份（4）在【选择文件和文件组】对话框中，选择要备份的文件和文件组。单击【确定】按钮返回。（5）在【备份数据库】窗口的【常规】页面，选择数据库为【学生成绩管理系统】，备份类型为“完整”，并选择备份设备，具体设置。（6）选中【选项】选项，打开【选项】页面，启用【追加到现有备份集】选项，以免覆盖现有的完整备份；选择【完成后验证备份】选项即可。（7）设置完成后，单击【确定】按钮开始备份，完成后将弹出成功消息。11.2.3使用管理工具备份5.备份压缩

在SQLServer2008中，默认情况下不对备份进行压缩，如果需要的话，就可以进行具体的配置，启用备份压缩功能。1）在服务器上配置备份压缩在数据库引擎服务器上，可以对默认的备份压缩功能进行修改，具体的步骤如下所示：（1）启动【SQLServerManagementStudio】，连接服务器。（2）右击【服务器】，在弹出的命令菜单中选择【属性】，打开【服务器属性】窗口。（3）单击【数据库设置】选项，在【数据库设置】页面启用【压缩备份】复选框。如图11.23所示。11.2.3使用管理工具备份图11.23启用压缩备份（4）单击【确定】按钮，完成对服务器的配置。11.2.3使用管理工具备份2）在备份数据库时启用备份压缩功能

除了在服务器上进行配置之外，还可以在用户备份数据库的时候，选择【压缩备份】选项，如图11.24所示。图11.24启用压缩备份11.3还原数据操作

还原数据库，就是让数据库根据备份的数据回到备份时的状态。当恢复数据库时，SQLServer会自动将备份文件中的数据全部拷贝到数据库，并回滚任何未完成的事务，以保证数据库中的数据的完整性。11.3.1常规还原

使用SQLServerManagementStudio工具还原数据库的操作步骤如下：（1）启动【SQLServerManagementStudio】，连接服务器。（2）在对象资源管理器中，展开【数据库】节点，右击【学生成绩管理系统】数据库，在弹出的命令菜单中选择【任务】→【还原】→【数据库】命令，打开【还原数据库】窗口。（3）在【还原数据库】窗口中选中【源设备】单选按钮，然后单击

弹出一个【指定备份】对话框，在【备份媒体】选项中选择【备份设备】选项，然后单击【添加】按钮，选择之前创建的“学生成绩管理系统备份”备份设备，如图11.25所示。图11.25选择备份设备11.3.1常规还原（4）选择完成后，单击【确定】按钮返回。在【还原数据库】窗口，就可以看到该备份设备中的所有的数据库备份内容，复选【选择用于还原的备份集】下面的“完整”、“差异”和“事务日志”3种备份，可这使数据库还原到最近一次备