第8章 备份与恢复

※8.1备份和恢复概述※8.2备份操作和备份命令※8.3恢复操作和恢复命令※8.4复制数据库※8.5附加数据库8.1.1备份和恢复需求分析数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。（3）病毒。破坏性病毒会破坏系统软件、硬件和数据。（4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或被破坏。（5）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。（6）盗窃。一些重要数据可能会遭窃。因此，必须制作数据库的复本，即进行数据库备份，以在数据库遭到破坏时能够修复数据库，即进行数据库恢复。数据库恢复就是把数据库从错误状态恢复到某一正确状态。1．备份内容数据库中数据的重要程度决定了数据恢复的必要与重要性，也就决定了数据是否及如何备份。数据库需备份的内容可分为数据文件（又分为主要数据文件和次要数据文件）、日志文件两部分。其中，数据文件中所存储的系统数据库是确保SQLServer2005系统正常运行的重要依据，无疑，系统数据库必须被完全备份。2．由谁做备份在SQLServer2005中，具有下列角色的成员可以做备份操作：（1）固定的服务器角色sysadmin（系统管理员）。（2）固定的数据库角色db_owner（数据库所有者）。（3）固定的数据库角色db_backupoperator（允许进行数据库备份的用户）。3．备份介质备份介质是指将数据库备份到的目标载体，即备份到何处。SQLServer2005中，允许使用两种类型的备份介质：（1）硬盘：是最常用的备份介质。硬盘可以用于备份本地文件，也可以用于备份网络文件。（2）磁带：是大容量的备份介质，磁带仅可用于备份本地文件。4．何时备份对于系统数据库和用户数据库，其备份时机是不同的。（1）系统数据库。当系统数据库master、msdb和model中的任何一个被修改以后，都要将其备份。master数据库包含了SQLServer2005系统有关数据库的全部信息，即它是“数据库的数据库”，如果master数据库损坏，那么SQLServer2005可能无法启动，并且用户数据库可能无效。当master数据库被破坏而没有master数据库的备份时，就只能重建全部的系统数据库。由于在SQLServer2005中已废止SQLServer2000中的Rebuildm.exe程序，若要重新生成master数据库，只能使用SQLServer2005的安装程序来恢复。当修改了系统数据库msdb或model时，也必须对它们进行备份，以便在系统出现故障时恢复作业以及用户创建的数据库信息。（2）用户数据库。当创建数据库或加载数据库时，应备份数据库。当为数据库创建索引时，应备份数据库，以便恢复时大大节省时间。当清理了日志或执行了不记日志的T-SQL命令时，应备份数据库，这是因为若日志记录被清除或命令未记录在事务日志中，日志中将不包含数据库的活动记录，因此不能通过日志恢复数据。不记日志的命令有：①BACKUPLOGWITHNO_LOG；②WRITETEXT；③UPDATETEXT；④SELECTINTO；⑤命令行实用程序；⑥BCP命令。5．限制的操作SQLServer2005在执行数据库备份的过程中，允许用户对数据库继续操作，但不允许用户在备份时执行下列操作：（1）创建或删除数据库文件；（2）创建索引；（3）不记日志的命令。6．备份方法SQLServer2005中有两种基本的备份：一是只备份数据库，二是备份数据库和事务日志，它们又都可以与完全或差异备份相结合。另外，当数据库很大时，也可以进行个别文件或文件组的备份，从而将数据库备份分割为多个较小的备份过程。这样就形成了以下四种备份方法：（1）完全数据库备份。这种方法按常规定期备份整个数据库，包括事务日志。当系统出现故障时，可以恢复到最近一次数据库备份时的状态，但自该备份后所提交的事务都将丢失。完全数据库备份的主要优点是简单，备份是单一操作，可按一定的时间间隔预先设定，恢复时只需一个步骤就可以完成。若数据库不大，或者数据库中的数据变化很少甚至是只读的，那么就可以对其进行全量数据库备份。（2）数据库和事务日志备份。这种方法不需很频繁地定期进行数据库备份，而是在两次完全数据库备份期间，进行事务日志备份，所备份的事务日志记录了两次数据库备份之间所有的数据库活动记录。当系统出现故障后，能够恢复所有备份的事务，而只丢失未提交或提交但未执行完的事务。执行恢复时，需要两步：首先恢复最近的完全数据库备份，然后恢复在该完全数据库备份以后的所有事务日志备份。（3）差异备份。差异备份只备份自上次数据库备份后发生更改的部分数据库，它用来扩充完全数据库备份或数据库和事务日志备份方法。对于一个经常修改的数据库，采用差异备份策略可以减少备份和恢复时间。差异备份比全量备份工作量小而且备份速度快，对正在运行的系统影响也较小，因此可以更经常地备份。经常备份将减少丢失数据的危险。使用差异备份方法，执行恢复时，若是数据库备份，则用最近的完全数据库备份和最近的差异数据库备份来恢复数据库；若是差异数据库和事务日志备份，则需用最近的完全数据库备份和最近的差异备份后的事务日志备份来恢复数据库。（4）数据库文件或文件组备份。这种方法只备份特定的数据库文件或文件组，同时还要定期备份事务日志，这样在恢复时可以只还原已损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度。对于被分割在多个文件中的大型数据库，可以使用这种方法进行备份。例如，如果数据库由几个在物理上位于不同磁盘上的文件组成，当其中一个磁盘发生故障时，只需还原发生了故障的磁盘上的文件。文件或文件组备份和还原操作必须与事务日志备份一起使用。1．准备工作数据库恢复的准备工作包括系统安全性检查和备份介质验证。例如，用不同的数据库备份或用不兼容的数据库备份信息覆盖某个已存在的数据库。当系统发现出现了以下情况时，恢复操作将不进行：（1）指定的要恢复的数据库已存在，但在备份文件中记录的数据库与其不同；（2）服务器上数据库文件集与备份中的数据库文件集不一致；（3）未提供恢复数据库所需的所有文件或文件组。恢复数据库时，要确保数据库的备份是有效的，即要验证备份介质，得到数据库备份的信息。这些信息包括：备份文件或备份集名及描述信息。所使用的备份介质类型（磁带或磁盘等）。所使用的备份方法。执行备份的日期和时间。备份集的大小。数据库文件及日志文件的逻辑和物理文件名。备份文件的大小。2．执行恢复数据库的操作可以提供使用图形向导方式或T-SQL语句执行恢复数据库的操作。具体的恢复操作步骤将在8.3节进行详细的介绍。8.2.1创建备份设备1．创建永久备份设备如果要使用备份设备的逻辑名来引用备份设备，就必须在使用它之前创建命名备份设备。当希望所创建的备份设备能够重新使用或设置系统自动备份数据库时，就要使用永久备份设备。若使用磁盘设备备份，那么备份设备实际上就是磁盘文件；若使用磁带设备备份，那么备份设备实际上就是一个或多个磁带。创建该备份设备有两种方法：使用图形向导方式或使用系统存储过程sp_addumpdevice。（1）使用系统存储过程创建命名备份设备。执行系统存储过程sp_addumpdevice可以在磁盘或磁带上创建命名备份设备，也可以将数据定向到命名管道。创建命名备份设备时，要注意以下几点：①SQLServer2005将在系统数据库master的系统表sysdevice中创建该命名备份设备的物理名和逻辑名。②必须指定该命名备份设备的物理名和逻辑名，当在网络磁盘上创建命名备份设备时要说明网络磁盘文件路径名。【例8.1】在本地硬盘上创建一个备份设备。USEmasterGOEXECsp_addumpdevice'disk','mybackupfile','E:\mybackupfile.bak'所创建的备份设备的逻辑名是：mybackupfile。所创建的备份设备的物理名是：E:\mybackupfile.bak。【例8.2】在磁带上创建一个备份设备。USEmasterGOEXECsp_addumpdevice'tape','tapebackupfile','\\.\tape0'（2）使用“对象资源管理器”创建永久备份设备。在“SQLServerManagementStudio”中创建备份设备，步骤是：启动“SQLServerManagementStudio”→在“对象资源管理器”中展开“服务器对象”→选择“备份设备”，在“备份设备”的列表上可以看到上例中使用系统存储过程创建的备份设备，右击鼠标，在弹出的快捷菜单中选择“新建备份设备”菜单项。2．创建临时备份设备临时备份设备，顾名思义，就是只作临时性存储之用，对这种设备只能使用物理名来引用。如果不准备重用备份设备，那么就可以使用临时备份设备。例如，如果只要进行数据库的一次性备份或测试自动备份操作，那么就用临时备份设备。创建临时备份设备时，要指定介质类型（磁盘、磁带）、完整的路径名及文件名称。可使用T-SQL的BACKUPDATABASE语句创建临时备份设备。对使用临时备份设备进行的备份，SQLServer2005系统将创建临时文件来存储备份的结果。语法格式：BACKUPDATABASE{database_name|@database_name_var}TObackup_file[,…n]其中：backup_file::={{backup_file_name|@backup_file_name_evar}|{DISK|TAPE}={temp_file_name|@temp_file_name_evar}3．使用多个备份设备SQLServer可以同时向多个备份设备写入数据，即进行并行的备份。并行备份将需备份的数据分别备份在多个设备上，这多个备份设备构成了备份集。如图8.1所示显示了在多个备份设备上进行备份以及由备份的各组成部分形成备份集。介质集设备1设备2设备3设备4备份集A1A2A3A4数据库A图8.1使用多个备份设备及备份集1．备份整个数据库语法格式：以下是一些使用BACKUP语句进行完全数据库备份的例子。【例8.4】使用逻辑名test1在E盘中创建一个命名的备份设备，并将数据库PXSCJ完全备份到该设备。USEmasterGOEXECsp_addumpdevice'disk','test1','E:\test1.bak'BACKUPDATABASEPXSCJTOtest1本例的执行结果如图8.2所示。图8.2使用BACKUP语句进行完全数据库备份2．差异备份数据库对于需频繁修改的数据库，进行差异备份可以缩短备份和恢复的时间。只有当已经执行了完全数据库备份后才能执行差异备份。进行差异备份时，SQLServer将备份从最近的完全数据库备份后数据库发生了的变化的部分。语法格式：BACKUPDATABASE{database_name|@database_name_var}TObackup_device[,…n][[MIRRORTObackup_device[,...n]][...next-mirror]][WITH[[,]DIFFERENTIAL]/*其余选项与数据库的完全备份相同*/]SQLServer执行差异备份时需注意下列几点：（1）若在上次完全数据库备份后，数据库的某行被修改了，则执行差异备份只保存最后依次改动的值；（2）为了使差异备份设备与完全数据库备份设备能区分开来，应使用不同的设备名。【例8.6】创建临时备份设备并在所创建的临时备份设备上进行差异备份。BACKUPDATABASEPXSCJTODISK='E:\pxscjbk.bak'WITHDIFFERENTIAL3．备份数据库文件或文件组