sqlserver2005数据库t备份与恢复

1、SQL Server 2005数据备份与恢复1数据库备份概述 “备份”：是数据的副本，用于在系统发生故障后还原和恢复数据。 1、数据库备份并不是简单地将表中的数据复制，而是将数据库中的所有信息，包括表数据、视图、索引、约束条件，甚至是数据库文件的路径、大小、增长方式等信息也备份。2、创建备份的目的是为了可以恢复已损坏的数据库。但是，备份和还原数据需要在特定的环境中进行，并且必须使用一定的资源。因此，可靠地使用备份和还原以实现恢复需要有一个备份和还原策略。2数据库备份概述 备份时候，需要考虑以下因素：1、组织对数据库的备份需求，尤其是对必要性的防止数据丢失的要求。2、每个数据库的特性。其大小、使

2、用模式、内容特性及其数据要求等。3、资源的约束。例如，硬件、人员、存储备份媒体空间以及存储媒体的物理安全性等。3数据库备份概述 1. 数据库磁盘备份设备简称备份设备： 是由SQL Server 2005提前建立的逻辑存储定义设备。之所以称为是逻辑设备，是由于在建立备份设备时候需要明确指定具体的磁盘存储路径，即便该磁盘存储路径并不存在，也可以正常建立一个备份设备。 实验1：在资源管理器中建立备份设备实验 第一步：在SQL Server管理平台的【对象资源管理器】窗口中展开【服务器对象】的子节点【备份设备】上单击鼠标右键，弹出快捷菜单，如右图所示。 4数据库备份概述 实验1：在资源管理器中建立备份

3、设备实验 第二步：单击新建备份设备选项，打开【备份设备】对话框。在【设备名称】文件框中输入“db_school_bakdevice”；在不存在磁带机的情况下，【目标】目标选项自动选中【文件】单选项，在【文件】选项对应的文本框中输入文件路径和名称“C:backschool_back.bak”，如下图所示。5数据库备份概述 实验2：在资源管理器中删除备份设备实验 在SQL Server管理平台的【对象资源管理器】窗口中展开【服务器对象】的子节点【备份设备】。在节点【db_school_bakdevice】上单击鼠标右键，弹出快捷菜单中删除该设备，如下图所示。63.1数据库备份概述 实验3：通过命令

4、方式建立和删除备份设备实验 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命令行创建磁盘备份设备的物理备份设备名为“

5、E:backuptest.bak”，逻辑备份设备名为“test”。exec sp_addumpdevice disk,test,E:backuptest.bak7数据库备份概述 实验3：通过命令方式建立和删除备份设备实验 删除备份设备删除一个磁盘备份设备的基本语法是： EXEC sp_dropdevice logical_name , delfile其中各个参数的含义是：logical_name：逻辑磁盘备份设备名。delfile：表示是否同时删除磁盘备份物理设备名。-例2：使用T-SQL语句的存储过程sp_dropdevice命令行删除前面刚创建的磁盘备份设备。exec sp_dropdev

6、ice test,delfile8数据库备份概述 2.数据库备份方法 数据库备份包括完整备份和差异性备份，这两种备份的区别如下：（1）完整备份： 包含数据库中全部数据和日志文件信息，也被称为是全库备份或者海量备份。对于文件磁盘量较小时候，完全备份的资源消耗并不能显现，但是一旦数据库文件的磁盘量非常大的时候，就会明显的消耗服务器的系统资源。因此对于完全备份一般需要停止数据库服务器的工作，或在用户访问量较少的时间段进行此项操作。 故障发生点 完全数据备份 运行事务 正常运行 Ta Tb 数据丢失 Tf 重装完整备份 恢复 9数据库备份概述 （1）完整备份： 通过还原数据库，只用一步即可以从完整的备

7、份重新创建整个数据库。如果还原目标中已经存在数据库，还原操作将会覆盖现有的数据库； 如果该位置不存在数据库，还原操作将会创建数据库。还原的数据库将与备份完成时的数据库状态相符，但不包含任何未提交的事务。 恢复数据库后，将回滚到未提交的事务。 小知识： 事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位事务和程序是两个概念在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序一个应用程序通常包含多个事务事务是恢复和并发控制的基本单位10数据库备份概述 （1）完整备份： 当执行全库备份时，SQL Server将备份在

8、备份过程中发生的任何活动，以及把任何未提交的事务备份到事务日志。在恢复备份时候，SQL Server利用备份文件中捕捉到的部分事务日志来确保数据一致性。 11数据库备份概述 （1）完整备份：实验1：在资源管理器中进行完全数据备份实验第一步：打开资源管理器，鼠标右击school数据库，在展开的菜单中选择任务中的备份项。 12数据库备份概述 （1）完整备份：实验1：在资源管理器中进行完全数据备份实验第二步：在展开的备份数据库界面中，选择备份类型为“完整”，备份组件为数据库，在备份目标为备份到磁盘，选择添加磁盘的具体的路径及备份文件名为C:school_fullback.bak，如图所示。点击确定后

9、完成完全数据备份的工作，所生成的C:school_fullback.bak文件将在后面数据库恢复中被重新应用。 13数据库备份概述 （1）完整备份：实验2：通过命令行进行完全数据备份实验 第一步：sp_addumpdevice 是系统存储过程，用于创建磁盘备份文件，其基本命令行如下所示：sp_addumpdevice devtype=device_type,logicalname=logical_name, physicalname = physical_name,cntrltype = controller_type|devstatus=device_status use master-首先

10、，进入master数据库。Go-下面，在C盘下建立文件夹back，然后分别执行下面的三个磁盘备份文件。exec sp_addumpdevice disk,backup_file1,c:backbackup_exec sp_addumpdevice disk,backup_file2,c:backbackup_exec sp_addumpdevice disk,backup_log,c:backbackup_log.bak注意：即便是C盘下面没有back文件夹，该命令也可以成功的执行。但是如果在实际进行磁盘备份中，由于没有该文件夹，将在执行backup database的时候出现错误。14数据库

11、备份概述 （1）完整备份：实验2：通过命令行进行完全数据备份实验 第二步：将school数据库备份到第一步建立的磁盘备份文件中。BACKUP DATABASEdatabase_name|database_name_var ,.f TO ,.n .,INIT|NOINIT在进行数据库备份的时候，INIT和NOINIT选项参数非常重要。使用 NOINIT 选项，SQL Server 把备份追加到现有的备份文件，也就是在原有的数据备份基础上，继续将现有的数据库追加性的继续备份到该磁盘备份文件中。使用 INIT 选项，SQL Server 将重写备份媒体集上所有数据，即将上次备份的文件抹去，重新将现有

12、的数据库文件写入到该磁盘备份文件中。15数据库备份概述 （1）完整备份：实验2：通过命令行进行完全数据备份实验 backup database test to test with noinitbackup database test to test with init-请反复执行这两句话，那么我们可以很快从磁盘文件的空间变化中发现init和no init的区别：-可见，init由于重新建立磁盘备份，因此文件并没有增长；而由于noinit是追加备份，因此磁盘文件增长非常明显。当然，我们也可以不需要使用磁盘备份文件，而通过直接指定磁盘路径的方式实现对数据库文件进行备份。BACKUP DATABAS

13、E school TO DISK=D: Mydiffbackup.bak163.1数据库备份概述 （2）差异备份： 差异性备份： （1）是无需完全数据备份，仅仅将变化的数据存储并追加到数据库备份文件中的过程。 （2）差异性备份仅记录自上次完整备份后更改过的数据，但是比完整备份更小、更快，可以简化频繁的备份操作，减少数据丢失的风险。 （3）差异性备份必须基于完整备份，因此差异性备份的前提是进行至少一次的完全数据备份。 故障发生点 完全数据备份 运行事务 差异备份 正常运行 Ta Tb Tc 数据丢失 Tf 重装完整备份 差异数据还原 恢复 17数据库备份概述 （2）差异备份：在还原差异性备份之前

14、，必须先还原其完全数据备份。如果按给定备份的要求进行一系列差异性备份，则在还原时只需还原一次完全数据备份和最近的差异性备份即可。执行差异性备份的前提和基本条件如下： 用于经常被修改的数据库； 要求一个完全数据备份，这是执行差异性备份的前提； 备份自上次完全数据备份以来的数据库变化； 执行差异性备份的语法与完全数据备份基本一致，仅仅的区别是在后面写上WITH DIFFERENTIAL参数即可。 18数据库备份概述 （2）差异备份：实验1：通过命令行进行差异数据备份实验 BACKUP DATABASE test TO DISK=C:test.bak WITH DIFFERENTIAL -或者 ba

15、ckup database test to test WITH DIFFERENTIAL19数据库备份概述 （2）差异备份：实验2：在管理平台中进行差异数据备份实验 打开备份向导。在“备份数据库”窗口中，选择备份类型为“差异”。在备份的目标中，指定备份到的磁盘文件位置（本例中为C:backschool.bak文件），如图所示。然后单击“确定”按钮。备份完成后，可以找到C:backschool.bak文件。差异备份文件要比完全备份文件小得多，因为它仅备份自上次完整备份后更改过的数据。 20数据库备份概述 （3）日志文件备份：日志文件备份定义：当数据库文件发生信息更改时候，其基本的操作记录将通过日

16、志文件进行记录，对于这一部分操作信息进行的备份就是日志文件备份。 运行事务 故障发生点 日志备份 运行事务 正常运行 Ta Tb Tf重装后备副本 利用日志文件恢复恢复 21数据库备份概述 （3）日志文件备份：执行日志文件备份的前提和基本条件是要求一个完全数据备份，备份日志文件的语法形式是：BACKUP LOG database_name | database_name_var TO ,.n WITH , INIT | NOINIT , NO_TRUNCATE| TRUNCATE_ONLY 该命令中的基本参数的意义是：1、NO_LOG 选项（1）无须备份复制日志，即删除不活动的日志部分，并且截

17、断日志。该选项会释放空间。因为并不保存日志备份，所以没有必要指定备份设备。（2）NO_LOG 和TRUNCATE_ONLY 是同义的，使用NO_LOG 或TRUNCATE_ONLY 备份日志后，记录在日志中的更改不可恢复。（3）为了恢复数据库的准确性，在执行日志文件应用该参数命令的同时，请立即执行BACKUP DATABASE命令，进行完全数据备份工作，以防止数据的意外丢失。 22数据库备份概述 （3）日志文件备份：该命令中的基本参数的意义是：2、 NO-TRUNCATE 选项（1）当数据库文件被损坏或者丢失，应该使用NO-TRUNCATE选项备份事务日志；（2）该选项可以完全备份所有数据库的

18、最新活动信息。执行后MSSQL将进行下面的活动： 保存整个事务日志，即使无法访问数据库。 不清理已提交事务日志的事务。 可以将数据库恢复到系统出现故障的时刻。 使用TRUNCATE-ONLY选项，或者NO_LOG选项，一般在以下情况发生： 事务日志已满，清理日志文件。 需要截断事务日志。23数据库备份概述 （3）日志文件备份：该命令中的基本参数的意义是：2、 NO-TRUNCATE 选项（3）该参数是只能够与BACKUP LOG命令一起使用的；（4）该参数使用的意义是，指定不截断日志，并使数据库引擎尝试执行备份，而不考虑数据库的状态。（5）使用 NO_TRUNCATE 执行的备份可能具有不完整

19、的元数据。该选项允许在数据库损坏时备份日志。实质上，当数据库遭受严重损坏后，该命令是最后的解决办法，即无论怎样都现将发生的任何操作信息先备份到日志中，尝试进行后期尽可能的还原工作。 24数据库备份概述 （3）日志文件备份：实验1：在管理平台中进行日志文件备份实验 打开备份向导。在“备份数据库”窗口中，选择备份类型为“事务日志”。在备份的目标中，指定备份到的磁盘文件位置（本例中为c:backbackup_log.bak文件），如图所示。然后单击“确定”按钮。备份完成后，可以找到c:backbackup_log.bak文件。 25数据库备份概述 （3）日志文件备份：实验2：通过命令行进行日志文件备

20、份实验 -备份事务日志，追加到现有日志文件backup log school to disk=d:school_log.bak WITH NOINIT-清空日志文件backup log school with no_log-备份事务日志，重写现有日志文件，并尽可能的将所有发生的操作信息到日志文件中BACKUP LOG school TO DISK=c:school_log.bak WITH INIT,NO_TRUNCATE -如果不想要日志或者是日志已没有什么作用时,可以考虑以下的实现方案: backup log DBNAME with no_log|truncate_onlyno_trunc

21、ate26第二节【学习目标】掌握在可视化状态下进行完全、差异、日志数据恢复数据的方法重点掌握通过命令行进行完全、差异、日志数据恢复数据的方法；熟练掌握案例及案例所示的命令行过程 27SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验1：在管理平台中利用完全数据备份还原数据库实验 第一步：首先新建一个空的school数据库，而后用鼠标右键单击“对象资源管理器”中的“school”数据库对象。在弹出的快捷菜单中选择“任务”“还原” “数据库”选项，如图所示 28SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验

22、1：在管理平台中利用完全数据备份还原数据库实验 第二步：在“还原数据库”窗口中，选择还原的数据库为“school”，选择用于还原的备份集为在备份操作中备份的完整数据集，如图所示 29SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验1：在管理平台中利用完全数据备份还原数据库实验 在“还原数据库”窗口中选择选项，在还原选项中选“覆盖现有数据库”复选框，如图所示，按“确定”按钮。还原操作完成后，打开“school”数据库，可以看到其中的数据进行了还原。在school中看不到进行完整备份后新增加的school数据，因为还原过程进行了完整备份的还原。 30S

23、QL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 第一步：在实验1的基础上，将school数据库的student表中插入一条学生记录后（假设姓名是关羽，如图所示），选择一次差异数据备份，备份至backup_文件中。如图所示。 。 31SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 第二步：删除school数据库，而后先进性一次完全数据备份，但是过程和实验1却不完全一致。在还原数据库的常规选项中的操作过程相同，

24、但是在“选项”中，必须设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，如图所示。即将数据库临时“挂起”，处于恢复状态。 32SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还原数据库实验 注意：此时student表中是没有关羽同学的，当然由于school数据库被挂起，任何用户现在还无法使用该数据库。随后，我们需要在完全数据还原的基础上，进行差异性数据库还原。如图所示。 33SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验2：在管理平台中中利用差异数据备份还

25、原数据库实验 然后同样在还原选项中选“覆盖现有数据库”复选框，并设置其恢复状态为“回滚未提交事务，使数据库处于可用状态”，完成差异数据还原工作。注意，如果你在完全数据库恢复过程中，其恢复状态没有选择第二项，而是默认的第一项，那么你这样操作在进行差异性数据还原的过程中将会出现下面的操作失误问题。如图所示。成功后，查看student表，发现“关羽”同学已经被恢复了。 。 34SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第一步：在实验1的基础之上，我们建立了完全数据备份文件。而后在school数据库的st

26、udent表中依然插入一条学生信息（假设该学生是“关羽”），随后进行school数据库的事务日志备份工作，将备份文件存储在backup_log.bak文件中，如图3-19所示。 35SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第一步：在实验1的基础之上，我们建立了完全数据备份文件。而后在school数据库的student表中依然插入一条学生信息（假设该学生是“关羽”），随后进行school数据库的事务日志备份工作，将备份文件存储在backup_log.bak文件中，如图3-19所示。 36SQL S

27、erver 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第二步：而后再次向student表中再插入一条学生记录（假设这次是“貂蝉”同学），如图所示的数据。重复第一步的事务日志备份工作，此时在backup_log.bak文件中已经保存了两次数据插入的日志信息（一次是张飞，一次是貂蝉）。随后删除school数据库，准备进行数据库的日志还原工作。 37SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 第三步：和差异数据库还原工作一样，我们

28、必须首先进行完全数据恢复，但是在数据库恢复界面的“选项”中，必须设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，从而使得数据库暂时处于挂起的状态。接下来，在“挂起”的数据库中再次选择还原数据库，只是这次我们选择还原的设备文件是backup_log.bak，即保存日志信息的文件，如图所示。我们可以看到，用于还原的备份集有两条数据，分别是插入张飞和貂蝉时候我们进行的日志文件备份。选择第一条日志信息，并在当前界面的“选项”中，同样设置其恢复状态为“不对数据库进行任何操作，不回滚未提交事务”，再次使得数据库暂时处于挂起的状态。注意：此时实质上已将将张飞同学的信息恢复了，但是貂蝉同学的信息

29、并未恢复。 38SQL Server 2005的数据库恢复 1、在管理平台中通过数据库备份文件恢复数据库 实验3：在资源管理器中利用日志文件还原数据库实验 问题：如果在备份集中，直接恢复第二条不就可以了吗？为什么还要非常麻烦地一条一条进行日志文件还原恢复呢？ 如果按照这样的逻辑，我们可以继续刚才的实验，只是会弹出图的错误对话框，还原失败，证明上面的思维方式是错误的！日志文件是有严格的时间轴顺序的，一旦违背时间轴还原，将由于找不到前面的还原信息点而产生错误。39SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （1） 数据库还原前准备 第一点：限制数据库的访问，避开网络流

30、量较大的时间段。限制 db_owner、dbcreator 或 sysadmin 角色成员访问数据库； 第二点：备份事务日志 ，为了保证数据库的一致性，进行还原之前一定要最后进行日志文件的备份工作，从而捕捉最新的事务日志备份和数据库脱机之间的数据更改。40SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （2）数据库还原的基本命令 RESTORE DATABASE database_name | database_name_var FROM ,.n WITH RESTRICTED_USER , FILE = | , NORECOVERY | RECOVERY | STA

31、NDBY = undo_ , REPLACE , RESTART , STATS = percentage 41SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （2）数据库还原的基本命令 参数意义是： DATABASE 指定从备份还原整个数据库。如果指定了文件和文件组列表，则只还原那些文件和文件组。database_name | database_name_var是将日志或整个数据库还原到的数据库。如果将其作为变量 (database_name_var) 提供，则可将该名称指定为字符串常量 (database_name_var = database name) 或字符串数据类型（ntext 或 text 数据类型除外）的变量。 42SQL Server 2005的数据库恢复 2、通过命令行进行数据库还原 （2）数据库还原的基本命令 参数意义是： RECOVERY 参数的含义 该选项是系统的默认选项。该选项用于恢复最后一个事务日志或者完全数据库恢复，可以保证数据库的一致性。当使用该选项时，系统取消事务日志中任何未提交的事务，并提交任何完成的事务。在数据库恢复进程完成之后，就可以使用数据库。如果必须使用增量备份恢复数据库，就不能使用该选项。 43SQL Server 20