第8章备份与恢复

第8章

备份与恢复8.1备份和恢复概述8.2备份操作和备份命令8.3恢复操作和恢复命令8.4附加数据库8.1备份和恢复概述数据库的备份和恢复是数据库管理员维护数据库安全性和完整性必不可少的操作，合理地进行备份和恢复可以将可预见的和不可预见的问题对数据库造成的伤害降到最低。当运行

SQL

Server的服务器出现故障，或者数据库遭到某种程度的破坏时，可以利用以前对数据库所做的备份重建或恢复数据库。

SQLServer2008数据库恢复模式分为三种：完整恢复模式、大容量日志恢复模式、简单恢复模式。◆完整恢复模式。为默认恢复模式。它会完整记录下操作数据库的每一个步骤。使用完整恢复模式可以将整个数据库恢复到一个特定的时间点，这个时间点可以是最近一次可用的备份、一个特定的日期和时间或标记的事务。

◆大容量日志恢复模式。它是对完整恢复模式的补充。简单地说就是要对大容量操作进行最小日志记录，节省日志文件的空间（如导入数据、批量更新、SELECTINTO等操作时）。比如一次在数据库中插入数十万条记录时，在完整恢复模式下每一个插入记录的动作都会记录在日志中，使日志文件变得非常大，在大容量日志恢复模式下，只记录必要的操作，不记录所有日志，这样一来，可以大大提高数据库的性能，但是由于日志不完整，一旦出现问题，数据将可能无法恢复。因此，一般只有在需要进行大量数据操作时才将恢复模式改为大容量日志恢复模式，数据处理完毕之后，马上将恢复模式改回完整恢复模式。

由于大容量日志恢复模式不支持时点恢复，因此必须在增大日志备份与增加工作丢失风险之间进行权衡。

◆简单恢复模式。在该模式下，数据库会自动把不活动的日志删除，因此简化了备份的还原，但因为没有事务日志备份，所以不能恢复到失败的时间点。通常，此模式只用于对数据库数据安全要求不太高的数据库。并且在该模式下，数据库只能做完整和差异备份。

了解了以上数据库还原模式后，一般就应该知道将自己的数据库设置为何种模式了。SQLServer2008提供了四种备份方式：完整备份、差异备份、事务日志备份、文件和文件组备份。

◆完整备份：备份整个数据库的所有内容，包括事务日志。该备份类型需要比较大的存储空间来存储备份文件，备份时间也比较长，在还原数据时，也只要还原一个备份文件。

完整备份在所有模式下都适用。

◆差异备份：差异备份是完整备份的补充，只备份上次完整备份后更改的数据。相对于完整备份分来说，差异备份的数据量比完整数据备份小，备份的速度也比完整备份要快。因此，差异备份通常作为常用的备份方式。在还原数据时，要先还原前一次做的完整备份，然后还原最后一次所做的差异备份，这样才能让数据库里的数据恢复到与最后一次差异备份时的内容相同。

差异备份也适用于所有恢复模式。8.1备份和恢复概述◆事务日志备份：事务日志备份只备份事务日志里的内容。事务日志记录了上一次完整备份或事务日志备份后数据库的所有变动过程。事务日志记录的是某一段时间内的数据库变动情况，因此在进行事务日志备份之前，必须要进行完整备份。与差异备份类似，事务日志备份生成的文件较小、占用时间较短，但是在还原数据时，除了先要还原完整备份之外，还要依次还原每个事务日志备份，而不是只还原最后一个事务日志备份（这是与差异备份的区别）。

事务日志备份仅适用于完整恢复模式和大容量日志恢复模式，不适用于简单模式。

◆文件和文件组备份。如果在创建数据库时，为数据库创建了多个数据库文件或文件组，可以使用该备份方式。使用文件和文件组备份方式可以只备份数据库中的某些文件，该备份方式在数据库文件非常庞大时十分有效，由于每次只备份一个或几个文件或文件组，可以分多次来备份数据库，避免大型数据库备份的时间过长。另外，由于文件和文件组备份只备份其中一个或多个数据文件，当数据库里的某个或某些文件损坏时，可以只还原损坏的文件或文件组备份。

8.1备份和恢复概述了解了以上数据库备份方式后，便可以针对自己的数据库利用以上方式来备份数据库了。合理备份数据库需要考虑几方面，首先是数据安全，其次是备份文件大小，最后是做备份和还原能承受的时间范围。

例如，如果数据库里每天变动的数据量很小，可以每周（周日）做一次完整备份，以后的每天（下班前）做一次事务日志备份，那么一旦数据库发生问题，可以将数据恢复到前一天（下班时）的状态。

当然，也可以在周日时做一次完整备份，周一到周六每天下班前做一次差异备份，这样一旦数据库发生问题，同样可以将数据恢复到前一天下班时的状态。只是一周的后几天做差异备份时，备份的时间和备份的文件都会跟着增加。但这也有一个好处，在数据损坏时，只要恢复完整备份的数据和前一天差异备份的数据即可，不需要去恢复每一天的事务日志备份，恢复的时间会比较短。

8.1备份和恢复概述如果数据库里的数据变动得比较频繁，损失一个小时的数据都是十分严重的损失时，用上面的办法备份数据就不可行了，此时可以交替使用三种备份方式来备份数据库。

例如，每天下班时做一次完整备份，在两次完整备份之间每隔八小时做一次差异备份，在两次差异备份之间每隔一小时做一次事务日志备份。如此一来，一旦数据损坏可以将数据恢复到最近一个小时以内的状态，同时又能减少数据库备份数据的时间和备份数据文件的大小。

在前面还提到过当数据库文件过大不易备份时，可以分别备份数据库文件或文件组，将一个数据库分多次备份。在现实操作中，还有一种情况可以使用到数据库文件的备份。例如在一个数据库中，某些表里的数据变动得很少，而某些表里的数据却经常改变，那么可以考虑将这些数据表分别存储在不同的文件或文件组里，然后通过不同的备份频率来备份这些文件和文件组。但使用文件和文件组来进行备份，还原数据时也要分多次才能将整个数据库还原完毕，所以除非数据库文件大到备份困难，否则不要使用该备份方式。

8.1备份和恢复概述8.2.1备份设备数据库备份设备是指用来存储备份数据的存储介质，常用的备份设备类型包括磁盘和磁带等。磁盘：以硬盘或其它磁盘类设备为存储介质。磁盘备份设备就可以存储在本地机器上，也可以存储在网络的远程磁盘上。如果数据备份存储在本地机器上，在由于存储介质故障或服务器崩溃而造成数据丢失的情况下，备份就没有意义了。因此，要及时将备份文件复制到远程磁盘上。如果采用远程磁盘作为备份设备，要采用统一命名方式（UNC）来表示备份文件，即“\\远程服务器名\共享文件名\路径名\文件名”。磁带：使用磁带作为存储介质，必须将磁带物理地安装在运行SQL

Server的计算机上，磁带备份不支持网络远程备份。8.2备份操作和备份命令对数据库进行备份时，备份设备可以采用物理设备名称和逻辑设备名称两种方式。

物理设备名称：即操作系统文件名，直接采用备份文件在磁盘上以文件方式存储的完整路径名，例如“D:\backup\data_full.bak”。

逻辑设备名称：为物理备份设备指定的可选的逻辑别名。使用逻辑设备名称可以简化备份路径。8.2.1备份设备创建备份设备有两种方法：使用图形向导方式或使用系统存储过程sp_addumpdevice。（1）使用系统存储过程创建命名备份设备。执行系统存储过程sp_addumpdevice可以在磁盘或磁带上创建命名备份设备。【例8.1】

在本地硬盘上创建一个备份设备。USEmasterGOEXECsp_addumpdevice'disk','mybackupfile',‘E:\data\mybackupfile.bak’8.2.1备份设备8.2.1备份设备（2）使用对象资源管理器创建永久备份设备。1）打开【对象资源管理器】，在【服务器对象】节点下找到【备份设备】节点，单击鼠标右键，弹出右键菜单。2）选择【新建备份设备】菜单，弹出新建备份设备窗口。3）输入备份设备逻辑名称，并指定备份设备的物理路径，单击【确定】即可。8.2.2备份命令1．备份整个数据库语法格式：BACKUPDATABASEdatabase_name /*被备份的数据库名*/TO<backup_device>[,...n] /*指出备份目标设备*/[WITH

[BLOCKSIZE={blocksize|@blocksize_variable}] /*块大小*/

[[,]{CHECKSUM|NO_CHECKSUM}]

[[,]{STOP_ON_ERROR|CONTINUE_AFTER_ERROR}]

[[,]DESCRIPTION={'text'|@text_variable}]

[[,]DIFFERENTIAL]

[[,]EXPIREDATE={date|@date_var} /*备份集到期和允许被重写的日期*/

|RETAINDAYS={days|@days_var}]

[[,]PASSWORD={password|@password_variable}]

[[,]{FORMAT|NOFORMAT}]

[[,]{INIT|NOINIT}] /*指定是覆盖还是追加*/

[[,]{NOSKIP|SKIP}]

[[,]MEDIADESCRIPTION={'text'|@text_variable}]

[[,]MEDIANAME={media_name|@media_name_variable}]

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_variable}]

[[,]NAME={backup_set_name|@backup_set_name_var}]

[[,]STATS[=percentage]]

[[,]COPY_ONLY]]8.2.2备份命令【例8.2】

使用逻辑名test1在E盘中创建一个命名的备份设备，并将数据库PXSCJ完全备份到该设备。USEmasterGOEXECsp_addumpdevice'disk','test1','E:\data\test1.bak'BACKUPDATABASEPXSCJTOtest18.2.2备份命令本例的执行结果如图所示。8.2.2备份命令【例8.3】

将数据库PXSCJ备份到多个备份设备。EXECsp_addumpdevice'disk','test2','E:\data\test2.bak'EXECsp_addumpdevice'disk','test3','E:\data\test3.bak'BACKUPDATABASEPXSCJTOtest2,test3 WITHNAME='pxscjbk'

8.2.2备份命令2．差异备份数据库语法格式：BACKUPDATABASE{database_name|@database_name_var} READ_WRITE_FILEGROUPS [,FILEGROUP={logical_filegroup_name|@logical_filegroup_name_var}[,...n]]TO<backup_device>[,…n][[MIRRORTO<backup_device>[,...n]][...next-mirror]][WITH {[[,]DIFFERENTIAL] /*其余选项与数据库的完全备份相同*/ }]【例8.4】

将PXSCJ在test2,test3上进行差异备份。BACKUPDATABASEPXSCJTO

test2,test3WITHDIFFERENTIAL8.2.2备份命令3．事务日志备份将事务日志中从前一次成功备份结束位置开始，到当前事务日志结尾处的内容进行备份。进行事务日志备份使用BACKUPLOG语句。语法格式：BACKUPLOG{database_name|@database_name_var} /*指定被备份的数据库名*/{TO<backup_device>[,...n] /*指定备份目标*/[[MIRRORTO<backup_device>[,...n]][...next-mirror]][WITH { {NORECOVERY|STANDBY=undo_file_name} |NO_TRUNCATE] |/*其余选项与数据库的完全备份相同*/ }}8.2.2备份命令【例8.5】

创建一个命名的备份设备PXSCJLOGBK，并备份PXSCJ数据库的事务日志。EXECsp_addumpdevice'disk','PXSCJLOGBK','E:\data\testlog.bak'BACKUPLOGPXSCJTOPXSCJLOGBK或者也可以将PXSCJ的事务日志继续备份到test2,test3上：BACKUPLOGPXSCJTOtest2,test38.2.3使用对象资源管理器备份在SQLServerManagementStudio中进行备份的步骤如下。第1步：启动SQLServerManagementStudio，在对象资源管理器中选择“管理”，右击鼠标，如图所示，在弹出的快捷菜单上选择“备份”菜单项。第2步：在打开的“备份数据库”窗口中选择要备份的数据库名。8.2.3使用对象资源管理器备份第3步：选择了数据库之后，窗口最下方的目标栏中会列出与PXSCJ数据库相关的备份设备。第4步：在“备份数据库”窗口中，将不需要的备份目标选择后单击“删除”按钮删除，最后备份目标选择为“mybackupfile”，单击“确定”按钮，执行备份操作。8.2.3使用对象资源管理器备份8.3恢复操作和恢复命令8.3.1检查点SQLServer系统在日志中存储有关信息，以便在需要时可以恢复（前滚）或撤销（回滚）构成事务的数据修改。SQLServer系统对修改过的数据缓冲区的内容并不是立即写回磁盘，而是控制写入磁盘的时间，它将在缓冲区内修改过的数据页存入高速缓存一段时间后再写入磁盘，从而实现优化磁盘写入。SQLServer系统定期将所有脏日志和数据页刷新到磁盘，这就称为检查点。SQLServer恢复机制能够通过检查点在检查事务日志时保证数据库的一致性，在对事务日志进行检查时，系统将从最后一个检查点开始检查事务日志，以发现数据库中所有数据的改变。8.3.2数据库的恢复命令1．恢复整个数据库【例8.6】

使用RESTORE语句从一个已存在的命名备份介质PXSCJBK1中恢复整个数据库PXSCJ。首先创建备份设备PXSCJBK1：EXECsp_addumpdevice'disk','PXSCJBK1', 'E:\data\PXSCJBK1.bak'使用BACKUP命令对PXSCJ数据进行完全备份：BACKUPDATABASEPXSCJ TOPXSCJBK18.3.2数据库的恢复命令接着，在恢复数据库之前，用户可以对PXSCJ数据库做一些修改，例如删除其中一个表，以便确认是否恢复了数据库。恢复数据库的命令如下：RESTOREDATABASEPXSCJ FROMPXSCJBK1 WITHFILE=1,REPLACE执行结果如图所示。2．恢复差异数据库备份【例8.7】恢复PXSCJ的差异备份。

BACKUPLOGPXSCJTOtest2,test3WITHNORECOVERY--尾日志备份，进入还原状态GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=1,NORECOVERY--还原完整备份GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=2,REPLACE--还原差异备份GO8.3.2数据库的恢复命令8.3.2数据库的恢复命令3．恢复事务日志【例8.8】恢复例8.5中PXSCJ在test2,test3上的日志备份。BACKUPLOGPXSCJTOtest2,test3WITHNORECOVERY--尾日志备份，进入还原状态GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=1,NORECOVERY--还原完整备份GORESTORELOGPXSCJ FROMtest2,test3 WITHFILE=2,NORECOVERY--还原日志备份GORESTORELOGPXSCJ FR