SQL第14章数据库的备份与恢复.ppt

《SQL第14章数据库的备份与恢复.ppt》由会员分享，可在线阅读，更多相关《SQL第14章数据库的备份与恢复.ppt（49页珍藏版）》请在三一办公上搜索。

1、第14章 数据库的备份和恢复,14.1 备份概述14.2 备份设备14.3 备份类型14.4 执行备份14.5 从不同的备份中恢复数据库14.6 恢复数据库系统,返回目录,2,14.1 备份概述,14.1.1 概述 1）什么是备份？备份就是对SQL Server数据库或事务日志进行复制，记录了当时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。2）备份权限：系统管理员(服务器角色)数据库所有者(数据库角色)数据库备份执行者(数据库角色),3,数据库备份是指系统管理员定期或不定期地将数据库部分或全部内容复制到磁带或另一个磁盘上保存起来的过程。这些复制的数据拷贝称为后备副本。当数

2、据库中的数据遭到破坏或丢失时，可以利用后备副本进行数据库的恢复，但只能恢复到备份时的状态。要使数据库恢复到发生故障时刻前的状态，必须重新运行自备份以后到发生故障前所有的更新事务。,4,数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障：硬盘损坏会使得存储于其上的数据丢失。（2）软件故障：软件设计上的失误或用户使用的不当。（3）病毒：破坏性病毒破坏系统软件、硬件和数据。（4）误操作：误使用诸如DELETE、UPDATE等命令（5）自然灾害：如火灾、洪水或地震等。（6）盗窃。一些重要数据可能会遭窃。,5,14.1.2 备份的分类备份可分为静态备份和动态备份。静态备份：备份期间不允许

3、对数据库进行任何存取、修改活动的备份方式。动态备份：备份期间允许对数据库进行存取或修改，即备份和用户事务可以并发执行的备份方式。静态备份简单，但降低数据库的可用性。动态备份可克服静态备份的缺点。但是，动态备份结束后，后备副本上的数据并不能保证是正确有效的。,6,14.1.3 何时备份SQL Server的备份可以手工完成，也可以通过SQL Server Agent完成。SQL Server Agent可以在事先设定的任一天的任何时候执行备份计划。数据库备份分为两种，一种是定期进行的备份，另一种是不定期的数据库备份。不定期的备份一般是数据库发生某些改变后进行的。,7,14.1.3 何时备份1.周

4、期性数据库备份一种经常采用的备份方案是：每月、每周、每日都进行一次备份。每月一次的备份，要么在月初，要么在月末，这个备份可以永久保存。每周一次的备份，它应该保存一段时期，比如一年。每日的备份，应该保存一个月。备份应该保存在一个安全地方，比如防火、距离机房较远的地方。,8,2.数据库不定期的备份在数据库发生以下改变后，应该进行数据库的备份。创建、修改、删除数据库前应该备份数据库。创建了用户自定义对象。增加或删除服务器的系统存储过程。修改了master、msdb、model数据库。清除事务日志或执行了不写入事务日志的操作。,9,14.2 备份设备,备份设备（backup device）是指SQL

5、Server中存储数据库和事务日志备份拷贝的存储介质。备份设备可以被定义成本地的磁盘文件、远程服务器上的磁盘文件、磁带或者命名管道。创建备份时，必须选择存放备份数据的备份设备。当建立一个备份设备时，需要给其分配一个逻辑名和一个物理名。物理名是操作系统用来标识备份设备的名称。逻辑名是用来标识物理备份设备的别名或公用名。逻辑设备名称永久地存储在SQL Server的系统表中。,10,14.2.1 备份设备的类型1.磁盘设备磁盘备份设备是指硬盘或其它磁盘存储媒体。可以将本地磁盘或远程磁盘定义成磁盘备份设备，磁盘备份设备根据需要可大可小。在网络上将文件备份到远程计算机上的磁盘，需使用通用命名规则名称，

6、以 ServernameSharenamePathFile 格式指定文件的位置。在网络上备份数据可能受网络数据传输错误的影响，因此备份操作完成后应该进行备份的验证。,11,2.磁带设备磁带备份设备的用法与磁盘设备相同，但必须将磁带设备物理连接到运行 SQL Server 实例的计算机上。SQL Server不支持磁带设备的远程备份。第一个安装到计算机上的磁带设备被分配的名字是.Tape0，第二个磁带设备的名字是.Tape1，以后安装的磁带设备的命名以此类推。,12,3.命名管道备份设备SQL Server系统提供了把备份放在Name Pipe命名管道上的能力，允许第三方软件供应商提供命名管道备

7、份设备来备份和恢复SQL Server数据库。但要注意，命名管道备份设备不能通过企业管理器创建和管理。,13,14.2.2 建立备份设备1.使用T-SQL语句建立备份设备系统存储过程sp_addumpdevice可以用来添加备份设备。其语法形式如下：sp_addumpdevice device_type,logical_name,physical_name参数：device_type：备份设备的类型。三种类型中的一种：disk以硬盘文件作为备份设备；pipe命名管道备份设备；tape磁带备份设备。logical_name：备份设备的逻辑名。physical_name：备份设备的物理名称。如果要

8、添加磁带设备，该参数必须是Windows NT指派给本地磁带设备的物理名称，例如.TAPE0。如果名称包含非字母数字的字符，需要用引号将其引起来。,14,【例14-1】分别创建一个本地磁盘、网络磁盘和磁带备份设备。/*创建本地磁盘备份设备*/EXEC sp_addumpdevice disk,DiskBak_jwgl,c:dumpjwgl.bak/*创建一个网络磁盘备份设备*/EXEC sp_addumpdevice disk,NetBak_jwgl,BackSrvSQLBakjwgl.bak/*创建一个磁带备份设备*/EXEC sp_addumpdevice tape,TapeBak_jwg

9、l,.tape0,15,2.使用企业管理器建立备份设备使用企业管理器建立备份设备采用以下步骤：展开服务器组，然后展开服务器。展开“管理”文件夹，右击“备份”，然后单击“新建备份设备”命令。屏幕将显示如图14-1的“备份设备属性”窗口，并表明是“新设备”。在“名称”框中输入该备份设备的名称，这是备份设备的逻辑名。如果建立一个磁盘备份设备，单击“文件名”，执行下列操作之一以确定备份设备的物理名。如果要建立一个磁带备份设备，单击“磁带驱动器名”，再单击要用作备份设备的磁带设备。（如果窗口中没有列出磁带设备，则表明本地计算机无法检测到磁带设备。）单击“确定”按钮，完成建立备份设备的操作。,16,14.

10、2.2 建立备份设备备份设备的删除(1)在企业管理器中，可以选定备份设备，右击，从弹出的快捷菜单中选择“删除”选项即可。(2)通过调用存储过程sp_dropdevice删除备份设备【例11-2】删除例11-1中建立的备份设备在查询分析器中执行下列语句EXEC sp_dropdevice DiskBak_jwgl,17,14.3 备份类型,SQL Server 支持的备份类型包括（4类）：完整数据库备份 备份包括事务日志在内的整个数据库。差异数据库备份 在完整数据库备份之间执行差异数据库备份。事务日志备份 日志备份序列提供了连续的事务信息链，可支持从数据库、差异或文件备份中快速恢复数据。数据库文

11、件和文件组备份,18,14.3.1 完整数据库备份完整数据库备份是指所有的数据库对象、数据和事务日志都将被备份。备份与还原都只需要很简单的操作。与事务日志备份和差异数据库备份相比：使用的存储空间更多。需要更多的时间。创建频率低。由于完整数据库备份不能频繁地创建，因此，不能最大程度地恢复丢失的数据。,19,14.3.2 差异备份差异数据库备份只记录自上次完整数据库备份后发生更改的数据。差异数据库备份比完整数据库备份小而且备份速度快。在执行差异数据库备份时注意如下几点：创建定期的数据库备份。在每个完整数据库备份之间定期创建差异数据库备份。应该在两个差异数据库备份的时间间隔内执行事务日志备份，把数据

12、损失的风险降到最小。,20,14.3.3 事务日志备份事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。一般情况下，事务日志备份比完整数据库备份使用的资源少。因此可以比完整数据库备份更经常地创建事务日志备份。经常备份将减少丢失数据的危险。有时，事务日志备份比完整数据库备份大。例如，数据库的事务率很高，从而导致事务日志迅速增大。在这种情况下，应更经常地创建事务日志备份。,21,14.3.4 数据库文件和文件组备份SQL Server 2000支持备份或还原数据库中的个别文件或文件组。这是一种相对较完善的备份和还原过程，通常用在具有较高可用性要求的超大型数据库（VLDB）中。如果可

13、用的备份时间不足以支持完整数据库备份，则可以在不同的时间备份数据库的子集。文件备份和还原操作必须与事务日志备份一起使用。,22,14.4 执行备份,14.4.1 备份前的准备备份计划主要考虑以下几个方面：确定备份的频率。确定备份频率要考虑两个因素：一是系统恢复时的工作量，二是系统活动的事务量。确定备份的内容。确定数据库中的哪些数据需要备份。确定备份的介质。确定是使用磁盘、还是磁带作为备份的介质。创建备份前需要初始化备份媒体。,23,确定备份的方式。确定备份采用动态备份还是静态备份。估计备份需要的存储空间量。在执行备份前，应该估计备份需要使用的存储空间量。确定备份的人员。是否使用备份服务器。如果

14、使用备份服务器，出现故障时，系统能迅速地得到恢复。确定备份存储的期限和存放地点：备份应该存储安全的地方并保存适当的期限。,24,14.4.2 使用企业管理器进行备份实际备份操作中，经常使用企业管理器来进行数据库备份，下面将针对不同的备份类型说明在企业管理器中如何进行备份操作。1.在企业管理器中进行完整数据库备份的操作步骤：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击要备份的数据库名，指向“所有任务”子菜单，然后单击“备份数据库”命令。在“名称”框内，输入备份集名称。在“描述”框中输入对备份集的描述（可选）。在“备份”选项下单击“数据库 完全”。在“目的”选项下，单击“磁带”或“磁盘

15、”。如果不能选择，则有可能是因为没有磁带设备。然后指定备份目的地。如果没出现备份目的地，则单击“添加”以添加现有的目的地或创建新目的地。,25,在“重写”选项下，如单击“追加到媒体”，则将备份追加到备份设备现有的备份中；如单击“重写现有媒体”，将重写备份设备中现有的备份。选择“调度”复选框，设置备份操作在以后执行或定期执行（可选）。单击“选项”选项卡，并执行下列一项或多项操作，选项卡显示如图14-5所示。如果是第一次使用备份媒体，或者要更改现有的媒体标签，则在“媒体集标签”框下选择“初始化并标识媒体”复选框，然后输入媒体集名称和媒体集描述。只有在重写媒体时才能对其进行初始化和标识设置。点击“确

16、定”开始数据库备份。,26,2.在企业管理器中进行差异备份的操作步骤：除第4步骤选择“数据库差异”外，其它步骤与完整数据库备份相同。3.在企业管理器中进行事务日志备份的操作步骤：除第4步骤选择“事务日志”外，其它步骤与完整数据库备份相同。4.在企业管理器中进行文件和文件组备份的操作步骤：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击要备份的数据库，指向“所有任务”子菜单，然后单击“备份数据库”命令。在“名称”框内，输入备份集名称。在“描述”框中输入对备份集的描述。（可选）在“备份”下，单击“文件和文件组”，然后单击浏览（“”）按钮。,27,在如图14-6“指定文件组和文件”对话框中，

17、为要备份的每个文件组和文件选择“备份”。通过选定文件组名，可自动选择该文件组中的所有文件。点击“确定”关闭该对话框。图14-6 在文件和文件组备份中指定文件或文件组对话框 在“目的”选项下，单击“磁带”或“磁盘”，然后指定备份目的地。如果没有出现备份目的地，单击“添加”以添加现有的目的地或创建新的目的地。选择一种“重写”方式：“追加到媒体”或“重写现有媒体”。选择“调度”复选框，设置备份操作以后执行或定期执行。（可选）单击“选项”选项卡，进行相关设置（可选）点击“确定”开始备份操作。,14.4.3 使用Transact-SQL语句备份1)数据库备份 可以通过BACKUP语句进行数据库备份，其语

18、法格式如下：BACKUP DATABASE database_name|database_name_varTO backup_device,nWITH,FORMAT,INIT|NOINIT,RESTART,DIFFERENTIAL其中：backup_device：指定备份操作时所要使用的逻辑或物理备份设备。其值为：disk、tape或pipe。FORMAT：表示应将媒体头写入用于此备份操作的所有卷。任何现有的媒体头都被重写。FORMAT选项使整个媒体内容无效，并且忽略任何现有的内容。INIT|NOINIT：INIT表示将重写那个设备上的所有现有的备份集数据，但是保留媒体头；NOINIT表示备份

19、集将追加到指定的磁盘或磁带设备上，以保留现有的备份集。NOINIT是默认设置。RESTART：表示SQL Server将重新启动一个被中断的备份操作。DIFFERENTIAL：指定备份为差异备份，如果不指定该参数则是完全备份。,【例】创建磁盘备份设备：mydevice1和mydevice2,分别对数据库jwgl进行完全备份和差异备份。在查询分析器中执行下列语句：/*执行两种备份方式*/BACKUP DATABASE jwgl to DiskBak_jwgl WITH INITBACKUP DATABASE jwgl to DiskBak_jwgl WITH DIFFERENTIAL,14.4.

20、3 使用Transact-SQL语句备份2）事务日志备份使用BACKUP语句进行事务日志备份，其语法格式如下：BACKUP LOG database_name|database_name_varTO backup_device,n WITH NO_ TRUNCATE,TRUNCATE ONLY|NO LOG其中：NO_ TRUNCATE：表示允许在数据库损坏时备份日志。TRUNCATE ONLY|NO LOG：删除不活动的日志部分，并且截断日志。该选项会释放空间。【例11-4】创建一个备份设备，并将数据库jwgl的事务日志备份到该设备上。BACKUP LOG jwgl to DiskBak_j

21、wgl,31,14.5 从不同备份中恢复数据库,数据库恢复是数据库管理系统管理的另一项重要工作。执行数据库恢复以前，应注意以下两点：在数据库恢复前，应该删除故障数据库，以便删除对故障数据库的任何引用。数据库恢复之前，必须限制用户对数据库的访问，数据库的恢复是静态的，应使用企业管理器或系统存储过程sp_dbotion设置数据库为单用户。,32,14.5.1 使用T-SQL语句进行数据库的恢复1.RESTORE语句的语法还原整个数据库RESTORE DATABASE database_name|database_name_var FROM,.n WITH FILE=file_number|file

22、_number,NORECOVERY|RECOVERY|STANDBY=undo_file_name 还原数据库部分内容的语法RESTORE DATABASE database_name|database_name_var,.n FROM,.n WITH PARTIAL,FILE=file_number|file_number,NORECOVERY,33,还原特定的文件或文件组的语法RESTORE DATABASE database_name|database_name_var,.n FROM,.n WITH FILE=file_number|file_number,NORECOVERY还原事

23、务日志语法RESTORE LOG database_name|database_name_var FROM,.n WITH FILE=file_number|file_number,NORECOVERY|RECOVERY|STANDBY=undo_file_name,STOPAT=date_time|date_time_var,34,参数：database_name|database_name_var：指定了将日志或整个数据库备份还原到后数据库名称。：指定还原操作要使用的逻辑或物理备份设备。：指定包括在数据库还原中的逻辑文件或文件组的名称。可以指定多个文件或文件组。FILE=file_numb

24、er|file_number：标识要还原的备份集。例如，file_number 为 1 表示备份媒体上的第一个备份集，file_number 为 2 表示第二个备份集。NORECOVERY：指示还原操作不回滚任何未提交的事务。如果需要应用另一个事务日志，则必须指定 NORECOVERY 或 STANDBY 选项。如果 NORECOVERY、RECOVERY 和 STANDBY 均未指定，则默认为 RECOVERY。RECOVERY：指示还原操作回滚未提交的事务。在恢复进程后即可随时使用数据库。PARTIAL：指定部分还原操作。STOPAT=date_time|date_time_var：指定将

25、数据库还原到指定的日期和时间时的状态。只有在指定的日期和时间前写入的事务日志记录才能应用于数据库。,35,2.使用SQL语句恢复数据库备份 用SQL语句恢复完整数据库备份使用T-SQL语句RESTORE DATABASE恢复整个数据库时，需要指定要还原数据库的名称和保存数据库备份的备份设备。1）如果要在还原数据库备份后应用事务日志或差异数据库备份，则需要指定NORECOVERY子句。2）如果备份设备上有多个备份集，可以使用FILE子句指定文件号标识需要从哪个备份集中恢复数据库。3）例如：从备份设备DiskBak_sdb中还原整个数据库：RESTORE DATABASE sdb FROM Dis

26、kBak_sdb/*或*/RESTORE DATABASE sdb FROM DiskBak_sdb WITH RECOVERY/*或*/RESTORE DATABASE sdb FROM DiskBak_sdb WITH FILE=1,RECOVERY,36,使用SQL语句从差异数据库备份中还原数据1）首先执行RESTORE DATABASE并指定NORECOVERY子句，以恢复差异数据库备份之前的完整数据库备份。2）然后再执行一次RESTORE DATABASE恢复差异数据库备份，并同时需要指定数据库名称和要从其中恢复差异数据库备份的备份设备。3）如果在执行完差异数据库备份恢复以后还要应用

27、事务日志备份，则还必须同时指定NORECOVERY子句，否则指定RECOVERY子句。还原差异数据库备份的顺序为：先还原最新的完整数据库备份，然后还原最后一次的差异数据库备份。,37,【例14-3】从差异数据库备份中恢复数据。/*从DiskBak_sdb中还原SDB数据库和差异数据库备份*/RESTORE DATABASE sdb FROM DiskBak_sdb WITH NORECOVERYRESTORE DATABASE sdb FROM DiskBak_sdb WITH FILE=3,RECOVERY,38,使用SQL语句还原事务日志备份一般采用以下步骤从事务日志备份恢复数据库：必须首

28、先还原事务日志备份之前的完整数据库备份或差异数据库备份。按顺序恢复自完整数据库备份或差异数据库以后创建的所有事务日志。撤消所有未完成的事务。,39,使用SQL语句还原事务日志备份执行RESTORE LOG语句，可进行由事务日志备份恢复数据库的工作。在RESTORE LOG需要指定事务日志将应用到的数据库名称和要从其中恢复事务日志备份的备份设备。如果恢复当前事务日志备份后还要应用其它事务日志备份，则在执行RESTORE LOG时必须还要指定NORECOVERY子句，否则，指定RECOVERY子句以恢复服务器的运行状态。,40,【例14-4】将多个事务日志备份恢复到数据库JWGL中（假设已经进行过

29、完整备份恢复或差异备份恢复）：RESTORE DATABASE sdb FROM DiskBak_sdb WITH NORECOVERYRESTORE DATABASE sdb FROM DiskBak_sdb WITH FILE=3,NORECOVERYRESTORE LOG sdb FROM DiskBak_sdb WITH FILE=4,NORECOVERYRESTORE LOG sdb FROM DiskBak_sdb WITH FILE=5,RECOVERY,41,使用SQL语句恢复数据库文件和文件组在T-SQL环境下，执行RESTORE DATABASE恢复数据库文件和文件组备份，

30、RESTORE DATABASE语句中，需要指定要恢复的数据库名称、文件和文件组备份所在的备份设备。如果执行完当前文件和文件组备份恢复后还需要对文件进行修改，则必须指定NORECOVERY子句，否则需指定RECOVERY子句。如果在创建文件备份之后对文件进行了修改，则执行 RESTORE LOG 语句以应用事务日志备份。【例14-5】从DiskBak_jwgl中恢复文件和文件组备份：RESTORE DATABASE jwgl FILE=jwgl_dataFROM DiskBak_jwgl,42,14.5.2 企业管理器中恢复数据库备份1.在企业管理器中恢复完整数据库备份在企业管理器中恢复完全数

31、据库备份，按下列步骤进行操作：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击数据库，指向“所有任务”子菜单，然后单击“还原数据库”命令。窗口显示“还原数据库”对话框，如图14-9所示。,43,在“还原为数据库”框中，如果要还原的数据库名称与显示的默认数据库名称不同，需点击下拉框进行选择。若要使用新名称还原数据库，输入新的数据库名称。单击“数据库”。在“显示数据库备份”对应的下拉框中，选择要还原的数据库。在“要还原的第一个备份”对应的下拉框中，选择要还原的备份集。在“还原”列表中，单击要还原的数据库备份。点击“确定”，系统开始数据库的还原。,44,2.使用企业管理器从差异数据库备份恢复

32、数据在SQL Server企业管理器中，使用以下步骤可从差异数据库备份恢复数据：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击数据库，指向“所有任务”子菜单，然后单击“还原数据库”命令。在如上图14-9“还原数据库”对话窗口的“还原为数据库”框中，选择或重新输入还原后的数据库名称。单击“数据库”。在“要还原的第一个备份”列表中，选择要还原的备份集。在“还原”列表中，单击要还原的差异备份。点击“确定”，系统开始数据库的还原。,45,3.使用企业管理器还原事务日志备份在企业管理器中，按照以下步骤进行事务日志备份的恢复操作：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击数据库，指

33、向“所有任务”子菜单，然后单击“还原数据库”命令。在“还原为数据库”框中，如果要还原的数据库名称与显示的默认数据库名称不同，可在其中进行输入或选择。单击“数据库”。在“要还原的第一个备份”列表中，选择要还原的备份集。在“还原”列表中，单击要还原的事务日志备份。点击“确定”，系统开始数据库的还原。,46,4.使用企业管理器恢复数据库文件和文件组在企业管理器中，恢复数据库文件和文件组备份的操作步骤如下：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击数据库，指向“所有任务”子菜单，然后单击“还原数据库”命令。在“还原为数据库”框中，如果要还原的数据库名称与显示的默认数据库名称不同，可在其中

34、进行输入或选择。单击“文件组或文件”。在“还原”列表中，选择每个要还原的文件和文件组。如果创建文件备份后修改了文件，则必须应用在文件备份后创建的事务日志备份。点击“确定”，系统开始数据库的还原。,47,14.6 恢复系统数据库,在备份用户数据库时需要备份系统数据库。这使得在系统或数据库发生故障（例如，如果硬盘发生故障）时可以重建系统。需要经常备份的系统数据库包括master和msdb。如果服务器配置为复制分发服务器时，则需要定期对distribution数据库进行备份。如果model数据库被修改过，也需要进行定期备份。对master数据库进行备份的操作与备份用户数据库相似，需要注意的是只能创建

35、master数据库的完整数据库备份。如果master数据库被严重损坏，则可能无法启动SQL Server实例。有两种方法将 master 数据库返回到可用状态：从当前备份还原用重建主控实用工具完全重建,48,使用重建主控实用工具重建master数据库，但会导致以前存储在 master 数据库中的所有数据永久丢失。SQL Server仍能访问其它数据库。如果系统可以访问master数据库（至少部分可用）而能够启动SQL Server实例，则可以从完整数据库备份中还原master数据库。然而，如果由于master数据库严重损坏而无法启动SQL Server实例，则不能立即还原master数据库的备份，因为SQL Server实例需要处于运行状态才能还原数据库。首先需要使用重建主控实用工具重建master数据库，然后才可以用普通方法还原当前数据库备份。对于msdb、distribution和model数据库备份，可以用普通数据库备份的恢复方法恢复。,49,小结本节课主要介绍数据库的备份与还原方法。有几个备份方法？下一节课，实验。,