数据库备份与恢复技术.ppt
72页
主讲教师：钱 哨交通部管理干部学院计算机系交通部管理干部学院计算机系交通部管理干部学院计算机系交通部管理干部学院计算机系 钱哨教案钱哨教案钱哨教案钱哨教案第三章 . 数据库备份与恢复技术SQL Server 2005数据库实践教程数据库实践教程——管理与维护篇管理与维护篇1 第一节【学习目标】n数据库备份概述；n掌握如何分别在可视化及命令行方式建立和删除数据库磁盘备份设备；n掌握在可视化状态下进行完全、差异、日志数据备份的方法，重点掌握通过命令行进行完全、差异、日志数据备份的方法；n掌握在可视化状态下进行完全、差异、日志数据恢复数据的方法，重点掌握通过命令行进行完全、差异、日志数据恢复数据的方法；2 数据库备份概述 “备份备份”：：是数据的副本，用于在系统发生故障后还原和恢复数据 1、数据库备份并不是简单地将表中的数据复制，而是将数据库中的所有信息，包括表数据、视图、索引、约束条件，甚至是数据库文件的路径、大小、增长方式等信息也备份2、创建备份的目的创建备份的目的是为了可以恢复已损坏的数据库但是，备份和还原数据需要在特定的环境中进行，并且必须使用一定的资源因此，可靠地使用备份和还原以实现恢复需要有一个备份和还原策略备份和还原策略。
3 数据库备份概述 备份时候，需要考虑以下因素：1、组织对数据库的备份需求备份需求，尤其是对必要性必要性的防止数据丢失的要求2、每个数据库的特性其大小、使用模式、内容特性及其数据要求等3、资源的约束例如，硬件、人员、存储备份媒体空间以及存储媒体的物理安全性等4 数据库备份概述 1. 数据库磁盘备份设备数据库磁盘备份设备简称备份设备备份设备： 是由SQL Server 2005提前建立的逻辑存储定义设备之所以称为是逻辑设备，是由于在建立备份设备时候需要明确指定具体的磁盘存储路径，即便该磁盘存储路径并不存在，也可以正常建立一个备份设备 实验1：在资源管理器中建立备份设备实验 第一步：在SQL Server管理平台的【对象资源管理器】窗口中展开【服务器对象】的子节点【备份设备】上单击鼠标右键，弹出快捷菜单，如右图所示 5 数据库备份概述 实验1：在资源管理器中建立备份设备实验 第二步：单击新建备份设备选项，打开【备份设备】对话框在【设备名称】文件框中输入“db_school_bakdevice”；在不存在磁带机的情况下，【目标】目标选项自动选中【文件】单选项，在【文件】选项对应的文本框中输入文件路径和名称“C:\back\”，如下图所示。
6 数据库备份概述 实验2：在资源管理器中删除备份设备实验 在SQL Server管理平台的【对象资源管理器】窗口中展开【服务器对象】的子节点【备份设备】在节点【db_school_bakdevice】上单击鼠标右键，弹出快捷菜单中删除该设备，如下图所示7 数据库备份概述 实验3：通过命令方式建立和删除备份设备实验 1. 建立备份设备我们可以通过执行系统存储过程sp_addumpdevice的形式，建立一个磁盘备份设备，基本语法是： EXEC sp_addumpdevice 'device_type' , 'logical_name' , 'physical_name'，，其中各个参数的含义是：device_type：设备类型，‘disk|tape’， “disk”表示磁盘，“tape”表示磁带logical_name：逻辑磁盘备份设备名physical_name：物理磁盘备份设备名例例1：使用：使用T-SQL语句的存储过程语句的存储过程sp_addumpdevice命令行创建磁盘备份设命令行创建磁盘备份设备的物理备份设备名为备的物理备份设备名为“E:\backup\student_bak”，逻辑备份设备名为，逻辑备份设备名为“db_student_bakdevice”。
exec sp_addumpdevice 'disk','db_student_bakdevice','E:\backup\student_bak'8 数据库备份概述 实验3：通过命令方式建立和删除备份设备实验 2.删除备份设备删除一个磁盘备份设备的基本语法是： EXEC sp_dropdevice 'logical_name' , ‘delfile'其中各个参数的含义是：logical_name：逻辑磁盘备份设备名delfile：表示是否同时删除磁盘备份物理设备名例例2：使用：使用T-SQL语句的存储过程语句的存储过程sp_dropdevice命令行删除前面刚创建的磁命令行删除前面刚创建的磁盘备份设备盘备份设备exec sp_dropdevice 'db_student_bakdevice',delfile'9 数据库备份概述 2.数据库备份方法数据库备份方法 数据库备份包括完整备份和差异性备份，这两种备份的区别如下：（1）完整备份： 包含数据库中全部数据全部数据和日志文件日志文件信息，也被称为是全库备份全库备份或者海量备份海量备份对于文件磁盘量较小时候，完全备份的资源消耗并不能显现，但是一旦数据库文件的磁盘量非常大的时候，就会明显的消耗服务器的系统资源。
因此对于完全备份一般需要停止数据库服务器的工作，或在用户访问量较少的时间段进行此项操作 故障发生点故障发生点 完全数据备份完全数据备份 运行事务运行事务 ↓ ↓正常运行正常运行 ─┼───────┼───────────── ─┼───────┼───────────── Ta Ta Tb Tb 数据丢失数据丢失 Tf Tf 重装完整备份重装完整备份 恢复恢复 ─┼─────── ─┼───────┥┥10 数据库备份概述 （1）完整备份： 通过还原数据库，只用一步即可以从完整的备份重新创建整个数据库。
如果还原目标中已经存在数据库，还原操作将会覆盖覆盖现有的数据库； 如果该位置不存在数据库，还原操作将会创建创建数据库还原的数据库将与备份完成时的数据库状态相符状态相符，但不包含任何未提交的事务事务 恢复数据库后，将回滚到未提交的事务事务 小知小知识：： o事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位o事务和程序是两个概念n在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序n一个应用程序通常包含多个事务o事务是恢复和并发控制的基本单位11 数据库备份概述 （1）完整备份： 当执行全库备份时，SQL Server将备份在备份过程中备份过程中发生的任何活动，以及把任何未提交的事务未提交的事务备份到事务日志在恢复备份时候，SQL Server利用备份文件中捕捉到的部分事务日志来确保数据一致性 12 数据库备份概述 （1）完整备份：实验1：在资源管理器中进行完全数据备份实验第一步：打开资源管理器，鼠标右击school数据库，在展开的菜单中选择任务中的备份项 13 数据库备份概述 （1）完整备份：实验1：在资源管理器中进行完全数据备份实验第二步：在展开的备份数据库界面中，选择备份类型为“完整完整”，备份组件为数据库，在备份目标为备份到磁盘，选择添加磁盘的具体的路径及备份文件名为C:\，如图所示。
点击确定后完成完全数据备份的工作，所生成的C:\文件将在后面数据库恢复中被重新应用 14 数据库备份概述 （1）完整备份：实验2：通过命令行进行完全数据备份实验 第一步：sp_addumpdevice 是系统存储过程，用于创建磁盘备份文件，其基本命令行如下所示：sp_addumpdevice [@devtype=]'device_type',[@logicalname=]'logical_name',[ @physicalname = ] 'physical_name'[,{[@cntrltype = ] controller_type|[@devstatus=]'device_status' }] use master--首先，进入master数据库Go--下面，在C盘下建立文件夹back，然后分别执行下面的三个磁盘备份文件exec sp_addumpdevice 'disk','backup_file1','c:\back\backup_file1.bak'exec sp_addumpdevice 'disk','backup_file2','c:\back\backup_file2.bak'exec sp_addumpdevice ''注意：注意：即便是C盘下面没有back文件夹，该命令也可以成功的执行。
但是如果在实际进行磁盘备份中，由于没有该文件夹，将在执行backup database的时候出现错误15 数据库备份概述 （1）完整备份：实验2：通过命令行进行完全数据备份实验 第二步：将school数据库备份到第一步建立的磁盘备份文件中BACKUP DATABASE{database_name|@database_name_var}
当然，我们也可以不需要使用磁盘备份文件，而通过直接指定磁盘路径的方式实现对数据库文件进行备份BACKUP DATABASE school TO DISK='D:\ '17 数据库备份概述 （2）差异备份： 差异性备份：差异性备份： （（1））是无需完全数据备份，仅仅将变化的数据变化的数据存储并追加到数据库备份文件中的过程 （（2））差异性备份仅记录自上次完整备份后上次完整备份后更改过的数据，但是比完整备份更小、更快，可以简化频繁的备份操作，减少数据丢失的风险 （（3））差异性备份必须基于完整备份必须基于完整备份，因此差异性备份的前提是进行至进行至少一次的完全数据备份少一次的完全数据备份 故障发生点故障发生点 完全数据备份完全数据备份 运行事务运行事务 差异备份差异备份 ↓ ↓正常运行正常运行 ─┼───┼─────┼────────── ─┼───┼─────┼────────── Ta Tb Tc Ta Tb Tc 数据丢失数据丢失 Tf Tf 重装完整备份重装完整备份 差异数据还原差异数据还原 恢复恢复 ─┼───┼─────┥ ─┼───┼─────┥18 数据库备份概述 （2）差异备份：在还原差异性备份之前，必须先还原其完全数据备份先还原其完全数据备份。
如果按给定备份的要求进行一系列差异性备份，则在还原时只需还原一次完全数据备份和最近的只需还原一次完全数据备份和最近的差异性备份即可差异性备份即可执行差异性备份的前提和基本条件如下：n 用于经常被修改的数据库；n 要求一个完全数据备份，这是执行差异性备份的前提；n 备份自上次完全数据备份以来的数据库变化； 执行差异性备份的语法与完全数据备份基本一致，仅仅的区别是在后面写上执行差异性备份的语法与完全数据备份基本一致，仅仅的区别是在后面写上WITH DIFFERENTIAL参数即可参数即可 19 数据库备份概述 （2）差异备份：实验1：通过命令行进行差异数据备份实验 BACKUP DATABASE school TO DISK='D:\' WITH DIFFERENTIAL --或者 backup database school to backup_file2 WITH DIFFERENTIAL20 数据库备份概述 （2）差异备份：实验2：在管理平台中进行差异数据备份实验 打开备份向导在“备份数据库”窗口中，选择备份类型为“差异”在备份的目标中，指定备份到的磁盘文件位置（本例中为C:\back\文件），如图所示。
然后单击“确定”按钮备份完成后，可以找到C:\back\文件差异备份文件要比完全备份文件小得多，因为它仅备份自上次完整备份后更改过的数据 21 数据库备份概述 （3）日志文件备份：日志文件备份定义：日志文件备份定义：当数据库文件发生信息更改时候，其基本的操作记录将通过日志文件进行记录，对于这一部分操作信息进行的备份对于这一部分操作信息进行的备份就是日志文件备份 运运行行事事务务 故障发生点故障发生点 日志备份日志备份 运行事务运行事务 ↓ ↓正常运行正常运行 ─┼───────┼───────────── ─┼───────┼───────────── Ta Ta Tb Tb Tf Tf重装后备副本重装后备副本 利用日志文件恢复利用日志文件恢复恢复恢复 ━━━━━━╋━━━━━━╋ ━━ ━━ ━━ ┥┥22 数据库备份概述 （3）日志文件备份：执行日志文件备份的前提和基本条件是要求一个完全数据备份，备份日志文件的语法形式是：BACKUP LOG { database_name | @database_name_var }{TO < backup_device > [ ,...n ] [ WITH [ , ] { INIT | NOINIT } ][ [ , ] NO_TRUNCATE| TRUNCATE_ONLY ]}该命令中的基本参数的意义是：1、NO_LOG 选项（1）无须备份复制日志，即删除不活动的日志部分删除不活动的日志部分，并且截断日志。
该选项会释放空间因为并不保存日志备份，所以没有必要指定备份设备2）NO_LOG 和TRUNCATE_ONLY 是同义的，使用NO_LOG 或TRUNCATE_ONLY 备份日志后，记录在日志中的更改不可恢复3）为了恢复数据库的准确性，在执行日志文件应用该参数命令的同时，请立即执行BACKUP DATABASE命令，进行完全数据备份工作，以防止数据的意外丢失 23 数据库备份概述 （3）日志文件备份：该命令中的基本参数的意义是：2、 NO-TRUNCATE 选项（1）当数据库文件被损坏或者丢失，应该使用NO-TRUNCATE选项备份事务日志；（2）该选项可以完全备份所有数据库的最新活动信息执行后MSSQL将进行下面的活动：n 保存整个事务日志，即使无法访问数据库n 不清理已提交事务日志的事务n 可以将数据库恢复到系统出现故障的时刻n 使用TRUNCATE-ONLY选项，或者NO_LOG选项，一般在以下情况发生：n 事务日志已满，清理日志文件n 需要截断事务日志24 数据库备份概述 （3）日志文件备份：该命令中的基本参数的意义是：2、 NO-TRUNCATE 选项（3）该参数是只能够与BACKUP LOG命令一起使用的；（4）该参数使用的意义是，指定不截断日志，并使数据库引擎尝试执行备份，而不考虑数据库的状态。
5）使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据该选项允许在数据库损坏时备份日志实质上，当数据库遭受严重损坏后，该命令是最后的解决办法，即无论怎样都现将发生的任何操作信息先备份到日志中，尝试进行后期尽可能的还原工作 25 数据库备份概述 （3）日志文件备份：实验1：在管理平台中进行日志文件备份实验 打开备份向导在“备份数据库”窗口中，选择备份类型为“事务日志”在备份的目标中，指定备份到的磁盘文件位置（本例中为文件），如图所示然后单击“确定”按钮备份完成后，可以找到文件 26 数据库备份概述 （3）日志文件备份：实验2：通过命令行进行日志文件备份实验 --备份事务日志，追加到现有日志文件backup log school to disk='' WITH NOINIT--清空日志文件backup log school with no_log--备份事务日志，重写现有日志文件，并尽可能的将所有发生的操作信息到日志文件中BACKUP LOG school TO DISK='' WITH INIT,NO_TRUNCATE --如果不想要日志或者是日志已没有什么作用时,可以考虑以下的实现方案: backup log DBNAME with [no_log|truncate_only][no_truncate]27 第二节【学习目标】n掌握在可视化状态下进行完全、差异、日志数据恢复数据的方法n重点掌握通过命令行进行完全、差异、日志数据恢复数据的方法；n熟练掌握案例及案例所示的命令行过程 28 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验1：在管理平台中利用完全数据备份还原数据库实验 第一步：首先新建一个空的school数据库，而后用鼠标右键单击“对象资源管理器”中的“school”数据库对象。
在弹出的快捷菜单中选择“任务”→“还原” →“数据库”选项，如图所示 29 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验1：在管理平台中利用完全数据备份还原数据库实验 第二步：在“还原数据库”窗口中，选择还原的数据库为“school”，选择用于还原的备份集为在备份操作中备份的完整数据集，如图所示 30 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验1：在管理平台中利用完全数据备份还原数据库实验 在“还原数据库”窗口中选择选项，在还原选项中选“覆盖现有数据库”复选框，如图所示，按“确定”按钮还原操作完成后，打开“school”数据库，可以看到其中的数据进行了还原在school中看不到进行完整备份后新增加的school数据，因为还原过程进行了完整备份的还原 31 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 第一步：在实验1的基础上，将school数据库的student表中插入一条学生记录后（假设姓名是关羽，如图所示），选择一次差异数据备份，备份至文件中。
如图所示 32 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 第二步：删除school数据库，而后先进性一次完全数据备份，但是过程和实验1却不完全一致在还原数据库的常规选项中的操作过程相同，但是在“选项”中，必须设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，如图所示即将数据库临时“挂起”，处于恢复状态 33 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 注意：此时student表中是没有关羽同学的，当然由于school数据库被挂起，任何用户现在还无法使用该数据库随后，我们需要在完全数据还原的基础上，进行差异性数据库还原如图所示 34 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 然后同样在还原选项中选“覆盖现有数据库”复选框，并设置其恢复状态为“回滚未提交事务，使数据库处于可用状态”，完成差异数据还原工作。
注意，如果你在完全数据库恢复过程中，其恢复状态没有选择第二项，而是默认的第一项，那么你这样操作在进行差异性数据还原的过程中将会出现下面的操作失误问题如图所示成功后，查看student表，发现“关羽”同学已经被恢复了 35 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第一步：在实验1的基础之上，我们建立了完全数据备份文件而后在school数据库的student表中依然插入一条学生信息（假设该学生是“关羽”），随后进行school数据库的事务日志备份工作，将备份文件存储在文件中，如图3-19所示 36 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第一步：在实验1的基础之上，我们建立了完全数据备份文件而后在school数据库的student表中依然插入一条学生信息（假设该学生是“关羽”），随后进行school数据库的事务日志备份工作，将备份文件存储在文件中，如图3-19所示 37 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第二步：而后再次向student表中再插入一条学生记录（假设这次是“貂蝉”同学），如图所示的数据。
重复第一步的事务日志备份工作，此时在文件中已经保存了两次数据插入的日志信息（一次是张飞，一次是貂蝉）随后删除school数据库，准备进行数据库的日志还原工作 38 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第三步：和差异数据库还原工作一样，我们必须首先进行完全数据恢复，但是在数据库恢复界面的“选项”中，必须设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，从而使得数据库暂时处于挂起的状态接下来，在“挂起”的数据库中再次选择还原数据库，只是这次我们选择还原的设备文件是，即保存日志信息的文件，如图所示我们可以看到，用于还原的备份集有两条数据，分别是插入张飞和貂蝉时候我们进行的日志文件备份选择第一条日志信息，并在当前界面的“选项”中，同样设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，再次使得数据库暂时处于挂起的状态注意：此时实质上已将将张飞同学的信息恢复了，但是貂蝉同学的信息并未恢复 39 3.2 SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 问题：问题：如果在备份集中，直接恢复第二条不就可以了吗？为什么还要非常麻烦地一条一条进行日志文件还原恢复呢？ 如果按照这样的逻辑，我们可以继续刚才的实验，只是会弹出图的错误对话框，还原失败，证明上面的思维方式是错误的！日志文件是有严格的时间轴顺序的，一旦违背时间轴还原，将由于找不到前面的还原信息点而产生错误。
40 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（1）） 数据库还原前准备数据库还原前准备n 第一点：限制数据库的访问，避开网络流量较大的时间段限制 db_owner、dbcreator 或 sysadmin 角色成员访问数据库；n 第二点：备份事务日志 ，为了保证数据库的一致性，进行还原之前一定要最后进行日志文件的备份工作，从而捕捉最新的事务日志备份和数据库脱机之间的数据更改41 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 RESTORE DATABASE { database_name | @database_name_var } [ FROM < backup_device > [ ,...n ] ] [ WITH[ RESTRICTED_USER ][ [ , ] FILE = { file_number | @file_number } ] [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] [ [ , ] REPLACE ][ [ , ] RESTART ][ [ , ] STATS [ = percentage ] ] ] 42 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 参数意义是：n DATABASE 指定从备份还原整个数据库。
如果指定了文件和文件组列表，则只还原那些文件和文件组{database_name | @database_name_var}是将日志或整个数据库还原到的数据库如果将其作为变量 (@database_name_var) 提供，则可将该名称指定为字符串常量 (@database_name_var = database name) 或字符串数据类型（ntext 或 text 数据类型除外）的变量 43 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 参数意义是：n RECOVERY 参数的含义 该选项是系统的默认选项该选项用于恢复最后一个事务日志用于恢复最后一个事务日志或者完全数据库完全数据库恢复恢复，可以保证数据库的一致性当使用该选项时，系统取消事务日志中任何未提交的事务，并提交任何完成的事务在数据库恢复进程完成之后，就可以使用数据库如果必须使用增量备份恢复数据库，就不能使用该选项如果必须使用增量备份恢复数据库，就不能使用该选项 44 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 参数意义是：n NORECOVERY参数含义 当需要恢复多个备份多个备份时，应使用NORECOVERY选项。
这时，系统既不取消事务日志中任何未提交的事务，也不提交任何已完成的事务在数据库恢复之前，数据库是不能使用的 45 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 参数意义是：n FROM参数含义 指定从中还原备份的备份设备如果没有指定 FROM 子句，则不会发生备份还原，而是恢复数据库可用省略 FROM 子句的办法尝试恢复通过 NORECOVERY 选项还原的数据库，或切换到一台备用服务器上如果省略 FROM 子句，则必须指定 NORECOVERY、RECOVERY 或 STANDBY 46 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （（2）数据库还原的基本命令）数据库还原的基本命令 参数意义是：n FILE = { file_number | @file_number }参数含义 标识要还原的备份集例如，file_number 为 1 表示备份媒体上的第一个备份集，file_number 为 2 表示第二个备份集 47 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第一步：创建基本的备份设备；共3个：，分别保存完全数据备份；差异数据备份；日志文件备份。
但是，DBA疏忽，将family数据库文件的完全数据备份和差异数据备份也保存到中 use mastergoexec sp_addumpdevice 'disk','backup_file1','c:\back\backup_file1.bak'exec sp_addumpdevice 'disk','backup_file2','c:\back\backup_file2.bak'exec sp_addumpdevice ''48 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第二步：对数据库school和family进行完全的数据库备份 backup database family to backup_file1backup database school to backup_file1向school数据库的student表里面插入一条张飞同学的数据insert into student(sno,sname,sex) values(666,'张飞','男')而后进行差异数据库的备份工作backup database family to backup_file2 WITH DIFFERENTIALbackup database school to backup_file2 WITH DIFFERENTIAL49 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第四步：再次插入两条数据，每次插入数据进行一次日志文件的备份工作 。
insert into student(sno,sname,sex) values(777,'吕布','男')backup log school to backup_log with noinit(这里的参数指定init和NO_TRUNCATE，表示追加性的和允许在数据库损坏时备份日志)insert into student(sno,sname,sex) values(888,'貂蝉','女')backup log school to backup_log with noinit ---重写日志文件50 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第四步：在特定的备份设备上检索所有备份集的所有备份首部信息 RESTORE HEADERONLY from backup_file1RESTORE HEADERONLY from backup_file2RESTORE HEADERONLY from backup_log--返回由备份集内包含的原始数据库和日志文件列表RESTORE FILELISTONLY from backup_file1RESTORE FILELISTONLY from backup_file2RESTORE FILELISTONLY from backup_log--我们都知道，在完全和差异数据备份中，是先保存了family数据库而后保存的是school数据库，则如果不用file参数指定显示那个数据库，则默认仅仅显示第一个保存的数据库，即family数据库信息。
51 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第五步：下面查看第二个备份的school数据库备份相关信息，学习with file参数的用法 RESTORE FILELISTONLY from backup_file1 WITH FILE =2RESTORE FILELISTONLY from backup_file2 WITH FILE =2返回由有关给定备份设备所标识的备份媒体的信息组成的结果集RESTORE LABELONLY from backup_file1 with file=2RESTORE LABELONLY from backup_file2 with file=2RESTORE LABELONLY from backup_log with file=152 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第六步：完全数据备份恢复实验 --首先删除数据库drop database school--然后再创建数据库create database school--从文件恢复数据库 RESTORE DATABASE school FROM DISK = 'C:\back\school1' WITH MOVE 'school_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\school.mdf' ,MOVE 'school_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\school.ldf' ,replace--或者你可以进行完全数据备份的恢复工作RESTORE DATABASE school from backup_file1 WITH FILE =2,replace53 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第七步：差异数据备份恢复实验 。
还是先删除数据库，而后新建数据库--第一步：先还原完全数据恢复，但是先必须挂起来RESTORE DATABASE school from backup_file1 WITH FILE =2,noRECOVERY,replace--第二步：然后恢复差异性数据库的备份RESTORE DATABASE school from backup_file2 WITH FILE =2 ,RECOVERY,replace--至此，张飞同学被恢复注意NORECOVERY与RECOVERY的差别54 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第八步：以日志文件形式还原数据库 还是先删除数据库，而后新建数据库--第一步：先还原完全数据恢复，但是先必须挂起来RESTORE DATABASE school from backup_file1 WITH FILE =2,noRECOVERY,replace--第二步：首先查看一下日志文件的备份具体时间信息；RESTORE HEADERONLY from backup_log55 3.2 SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原实验：数据库备份与还原命令综合实验 第八步：以日志文件形式还原数据库 。
--第三步：而后开始根据具体的最后备份时间，进行日志文件的还原工作RESTORE LOG school FROM backup_log WITH noRECOVERY, STOPAT =' 2009/11/11 14:39:00' (特别注意就是NORECOVERY与recovery的时间问题)use schoolselect * from studentUse master56 3.3 数据库备份与恢复技术实训 n 通过SQL Server数据库备份实验，掌握和深入理解三种基本恢复模型的创建过程，学习并掌握备份设备存储备份的过程；n 通过SQL Server备份方法实验，学习并熟练掌握完全、差异和日志文件的数据备份方法；n 通过SQL Server文件和文件组备份实验，学习并熟练掌握文件和文件组备份的备份过程；n 通过SQL Server数据还原实验，学习并熟练掌握完全、差异和日志文件的数据备份恢复数据的方法，掌握文件和文件组备份的备份恢复数据的方法 57 3.3 数据库备份与恢复技术实训 实训1：SQL数据库恢复模型创建实验 本次实验的主要目的是熟悉数据库的三种基本恢复模型的创建过程，即完全恢复模型、大容量日志记录恢复模型和简单恢复模型实验：alter database d1 set recovery simple --设置数据库恢复模型alter database d1 set recovery bulk_logged --设置大容量日志记录恢复模型alter database d1 set recovery full --设置简单恢复模型实验2：SQL Server备份设备实验 SQL Server备份设备包括物理设备和逻辑设备，本次实验的主要目的是创建物理设备实验（包括：本地磁盘、磁带机备份和网络永久磁盘备份），创建逻辑设备（包括：永久备份文件和临时备份文件）备份工作。
58 3.3 数据库备份与恢复技术实训 实训2：SQL Server备份设备实验 exec sp_addumpdevice 'disk','bak2','c:\back_device\bak2.bak' --创建永久磁盘备份设备创建永久磁盘备份设备exec sp_addumpdevice 'disk','bak3','c:\back_device\bak3.bak'exec sp_addumpdevice 'disk','bak4','\\192.168.10.1\backup\bak4.bak' --创建网络永久磁盘备份设备，需要注意是本次实验机房的一台计算机的创建网络永久磁盘备份设备，需要注意是本次实验机房的一台计算机的IP地址，该地址的计地址，该地址的计算机需要共享算机需要共享backup文件夹用于网络文件的创建文件夹用于网络文件的创建exec sp_addumpdevice 'disk','bak5','\\192.168.10.1\backup\bak5.bak'exec sp_dropdevice 'bak5' --删除备份设备删除备份设备backup database d3 to bak3 --将数据库备份到备份设备将数据库备份到备份设备backup database d4 to bak4restore headeronly from bak2 --查看备份设备中的内容查看备份设备中的内容backup database d3 to disk='c:\back_file\d3.bak' --将数据库备份到临时备份文件将数据库备份到临时备份文件backup database d4 to disk='c:\back_file\d4.bak'restore database d3 from bak3 --从备份设备还原数据库从备份设备还原数据库 restore database d4 from disk='c:\back_file\d4.bak' --从备份文件还原数据库从备份文件还原数据库59 3.3 数据库备份与恢复技术实训 实训3：使用多个备份文件存储备份实验 SQL Server可同时向多个备份文件进行写操作，如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
这多个备份文件必须用同类型的媒体，并放到一个媒体集中另一方面，媒体集中的文件必须同时使用，而不能单独使用我们当然可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用 backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份备份D4并形成并形成Media Setbackup database d3 to bak4 --失败，因失败，因Media set中文件必须同时使用中文件必须同时使用backup database d3 to bak4,bak5,bak6 --成功，将成功，将D3也备份到也备份到Media Set中中restore headeronly from bak4,bak5,bak6 --查看查看Media Set中的备份内容中的备份内容backup database d4 to bak4 with medianame='bak4',format --重新划分重新划分Media Setbackup database d3 to bak5,bak6 with medianame='bak56',formatbackup database d1 to bak1 with init --with init重写备份设备中内容重写备份设备中内容backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中将内容追加到备份设备中restore headeronly from bak160 3.3 数据库备份与恢复技术实训 实训4：SQL Server备份方法实训 --实验实验1：首先开始进行完全数据备份：首先开始进行完全数据备份backup database d1 to bak1 with init backup database d1 to bak1 with noinit --注意，参数init和noinit的差异性--实验实验2：下面开始进行差异备份，第一次备份时应做完全备份：下面开始进行差异备份，第一次备份时应做完全备份backup database d2 to bak2 with init,name='d2_full' --建立表b1create table b1(c1 int not null,c2 char(10) not null)--每次插入更新的数据后，都进行差异数据备份backup database d2 to bak2 with differential,name='d2_diff1'insert b1 values(1,'a')backup database d2 to bak2 with differential,name='d2_diff2'insert b1 values(2,'b')backup database d2 to bak2 with differential,name='d2_diff3'insert b1 values(3,'c')backup database d2 to bak2 with differential,name='d2_diff4'restore headeronly from bak2 61 3.3 数据库备份与恢复技术实训 实训4：SQL Server备份方法实训 --实验实验3：下面开始进行日志备份，第一次备份时应做完全备份：下面开始进行日志备份，第一次备份时应做完全备份backup database d3 to bak3 with init,name='d3_full' create table b1(c1 int not null,c2 char(10) not null)--每次插入更新的数据后，都进行日志文件备份每次插入更新的数据后，都进行日志文件备份backup log d3 to bak3 with name='d3_log1'insert b1 values(1,'a')backup log d3 to bak3 with name='d3_log2'insert b1 values(2,'b')backup log d3 to bak3 with name='d3_log3'insert b1 values(3,'c')backup log d3 to bak3 with name='d3_log4'restore headeronly from bak362 3.3 数据库备份与恢复技术实训 实训4：SQL Server备份方法实训 --实验实验4：下面的实验包括：完全数据备份，差异数据备份和日志文件备份：下面的实验包括：完全数据备份，差异数据备份和日志文件备份create table b1(c1 int not null,c2 char(10) not null)backup log d4 to bak4 with name='d4_log1'insert b1 values(1,'a')backup log d4 to bak4 with name='d4_log2'insert b1 values(2,'b')backup database d4 to bak4 with differential,name='d4_diff1'insert b1 values(3,'c')backup log d4 to bak4 with name='d4_log3'insert b1 values(4,'d')backup log d4 to bak4 with name='d4_log4'insert b1 values(5,'d')backup database d4 to bak4 with differential,name='d4_diff2'restore headeronly from bak463 3.3 数据库备份与恢复技术实训 实训4：SQL Server备份方法实训 --实验实验5：下面的：下面的实验为实验为日志清除日志清除实验实验backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录backup log d4 to bak4 with no_truncate --在D4数据库损坏时马上备份当前数据库日志(DEMO)-- 问题问题：：请请在机房完成下面的在机房完成下面的实验实验第一步：完全备份数据库+修改第1次数据；第二步：差异备份数据库+修改第2次数据；第二步：日志文件备份数据库+修改第3次数据；第四步：停止SQL，删除数据库数据文件+重启SQL；请你再还原数据库，可否还原到修改第3次的数据呢？64 3.3 数据库备份与恢复技术实训 实训5：SQL Server文件和文件组备份实训 文件和文件组备份主要用于超大型数据库的备份，其目的是只备份选定的文件或者文件组，但）必须同时作日志备份。
在还原时用文件/文件组备份和日志备份进行还原，其特点是备份量少，恢复速度快 create database d5 on primary(name=d5_data1,filename='c:\data\d5\d5_data1.mdf',size=2MB),filegroup FG2 --创建数据库时创建filegroup FG2(name=d5_data2,filename='c:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中size=2Mb)log on(name=d5_log1,filename='e:\data\d5\d5_log1.ldf',size=2Mb)65 3.3 数据库备份与恢复技术实训 实训5：SQL Server文件和文件组备份实训 --下面开始调用刚刚创建的数据库下面开始调用刚刚创建的数据库d5，学习修改数据库，学习修改数据库use d5goalter database d5add file(name=d5_data3,filename='c:\data\d5\d5_data5.ndf',size=2MB)to filegroup FG2 --将将d5_data3加到文件组加到文件组FG2中中alter database d5 add filegroup FG3 --增加文件组增加文件组FG3alter database d5 --将将d5_data4加到文件组加到文件组FG2中中add file(name=d5_data4,filename='c:\data\d5\d5_data4.ndf',size=2MB)to filegroup FG3--查询数据库查询数据库d5Exec sp_helpdb d5create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同将不同表放到不同filegroup中中create table t2(c1 int not null,c2 char(10) not null) on FG2create table t3(c1 int not null,c2 char(10) not null) on FG3backup database d5 to bak5 with init,name='d5_full' 66 3.3 数据库备份与恢复技术实训 实训5：SQL Server文件和文件组备份实训 --文件组备份文件组备份backup database d5 filegroup='primary' to bak5 with name='d5_primary'backup log d5 to bak5 with name='d5_log1'backup database d5 filegroup='FG2' to bak5 with name='d5_FG2'backup log d5 to bak5 with name='d5_log2'backup database d5 filegroup='FG3' to bak5 with name='d5_FG3'backup log d5 to bak5 with name='d5_log3'--注意：下面开始进行文件备份注意：下面开始进行文件备份backup database d5 to bak6 with init,name='d5_full' backup database d5 file='d5_data1' to bak6 with name='d5_data1'backup log d5 to bak6 with name='d5_log1'backup database d5 file='d5_data2' to bak6 with name='d5_data2'backup log d5 to bak6 with name='d5_log2'backup database d5 file='d5_data3' to bak6 with name='d5_data3'backup log d5 to bak6 with name='d5_log3'backup database d5 file='d5_data4' to bak6 with name='d5_data4'backup log d5 to bak6 with name='d5_log4'restore headeronly from bak667 3.3 数据库备份与恢复技术实训 实训6：SQL Server数据还原实训 --实验实验1、验证备份设备、验证备份设备restore headeronly from bak3restore filelistonly from bak3 with file=1restore labelonly from bak3restore verifyonly from bak3--实验实验2、从备份中还原、从备份中还原restore headeronly from bak1--实验实验3、从完全备份中恢复、从完全备份中恢复restore database d1 from bak1 with file=2 68 3.3 数据库备份与恢复技术实训 实训6：SQL Server数据还原实训 --实验实验4、从差异备份中恢复、从差异备份中恢复restore headeronly from bak2 restore database d2 from bak2 with file=1,norecovery restore database d2 from bak2 with file=5,recovery--实验实验5、从日志备份中恢复、从日志备份中恢复restore headeronly from bak3 restore database d3 from bak3 with file=1,norecoveryrestore log d3 from bak3 with file=2,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore log d3 from bak3 with file=4,norecoveryrestore log d3 from bak3 with file=5,recovery69 3.3 数据库备份与恢复技术实训 实训6：SQL Server数据还原实训 --实验实验6、恢复到指定时间、恢复到指定时间restore database d3 from bak3 with file=1,norecovery restore log d3 from bak3 with file=2,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore log d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000'--实验实验7、还原文件组备份、还原文件组备份restore database d5 filegroup='FG2' from bak5 with file=4,norecovery restore log d5 from bak5 with file=5,norecoveryrestore log d5 from bak5 with file=7,recovery--实验实验8、还原文件备份、还原文件备份restore headeronly from bak6 restore database d5 file='d5_data3' from bak6 with file=6,norecoveryrestore log d5 from bak6 with file=7,norecoveryrestore log d5 from bak6 with file=9,recovery70 3.3 数据库备份与恢复技术实训 实训6：SQL Server数据还原实训 --实验实验9、删除现有数据库，从备份中重建数据库、删除现有数据库，从备份中重建数据库restore database d5 from bak6 with replace create database d6 --move to将数据库文件移动到新位置将数据库文件移动到新位置on primary(name=d6_data,filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_Data.MDF',size=2MB)log on(name=d6_log,filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_log.ldf',size=2MB)gobackup database d6 to bak6 with initdrop database d6restore database d6 from bak6with move 'd6_data' to 'e:\data\d6\d6_data.mdf',move 'd6_log' to 'e:\data\d6\d6_log.ldf'--查看数据库信息查看数据库信息Exec sp_helpdb d671 本章总结n 了解数据库备份的基本概念以及备份的基本类别是什么？n 掌握如何分别在可视化及命令行方式建立和删除数据库磁盘备份设备。
重点考核命令行方式建立和删除数据库磁盘备份设备；n 掌握在可视化状态下进行完全、差异、日志数据备份的方法，重点掌握通过命令行进行完全、差异、日志数据备份的方法；n 理解完全、差异、日志数据备份的区别和联系；n 掌握在可视化状态下进行完全、差异、日志数据恢复数据的方法，重点掌握通过命令行进行完全、差异、日志数据恢复数据的方法；n 熟练掌握备份与恢复的案例及案例所示的命令行过程。
