数据库原理电子教案：第九章-数据库备份恢复.doc

第9章 数据库备份恢复
课题：数据库备份恢复
目的要求：
掌握数据库备份的方法
掌握数据库恢复的方法
掌握数据导入与导出方法
了解数据库的附加与分离方法
重点难点：
数据库的备份与恢复方法
课外作业布置：
复习本节课内容，完成本章习题
9.1 备份和恢复概述
9.1.1 备份和恢复需求分析
数据库中的数据，在一般的情况下不会出现问题，但是一旦出现一些特殊的情况，如果没有对数据库进行备份，那么数据库就很容易被破坏，给工作带来很大的麻烦。下面列出几种常见的情况：
l SQL Server服务器瘫痪或者电脑崩溃；
l 偶然或者恶意的删除数据；
l 设备遭到破坏；
l 从一台机器拷贝数据到另外一台机器；
l 一些不可避免的因素，例如停电、火灾等。
对于数据库管理员来讲，备份是非常重要的任务，备份能从数据灾难中恢复受到破坏的数据。
9.1.2 数据库备份的基本概念
进行数据库备份的工作主要是由数据库管理员来完成的。数据库备份是指对数据库的完整备份，包括所有的数据以及数据库对象。
在对数据库进行完全备份时所有未完成的事务或者发生在备份过程中的事务都不会被 备份。
备份是指制作数据库结构、对象和数据的拷贝，以便在数据库遭到破坏的时候能够修复数据库；
还原是指将数据库备份加载到服务器中的过程。需要使用数据库的备份和还原的情况：

o 存储媒体损坏
o 用户操作错误
o 整个服务器崩溃
o 需要在不同的服务器之间移动数据库时
数据备份的类型
在SQL Server 2005中有四种备份类型，分别为：数据库备份（Database Backups）、事务日志备份（Transaction Log Backup）、差异备份（Differential Database Backups）以及文件和文件组备份（File and File Group Backup）。
1．数据库备份
数据库备份是指对数据库的完整备份，包括所有的数据以及数据库对象。数据库备份一般在下列要求或条件下使用：
1）数据不是非常重要，尽管在备份之后还原之前数据被修改，但这种修改是可以忍受的。
2）通过批处理或其他方法，在数据库还原之后可以很轻易地重新实现在数据损坏前发生的修改。
3）数据库变化的频率不大。
2．事务日志备份
事务日志备份是指对数据库发生的事务进行备份，包括从上次进行事务日志备份、差异备份和数据库完全备份之后，所有已经完成的事务。在以下情况下常选择事务日志备份：
1）不允许在最近一次数据库备份之后发生数据丢失或损坏的情况。
2）存储备份文件的磁盘空间很小或者留给进行备份操作的时间有限。
3）准备把数据库还原到发生失败的前一点。
4）数据库变化较为频繁的情况
3．差异备份
差异备份是指将最近一次数据库备份以来发生的数据变化备份起来，因此，差异备份实际上是一种增量数据库备份。与完整数据库备份相比，差异备份由于备份的数据量较小，所以备份和还原所用的时间较短。通过增加差异备份的备份次数，可以降低丢失数据的风险，但是它无法象事务日志备份那样提供到失败点的无数据损失备份。
4．文件或文件组备份
文件或文件组备份是指对数据库文件或数据库文件组进行备份，它不像完整的数据库备份那样同时也进行事务日志备份。在使用文件或文件组进行还原时，要求有一个自上次备份以来的事务日志备份来保证数据库的一致性。所以，在进行完文件或文件组备份后，应再进行事务日志备份，否则备份在文件或文件组备份中的所有数据库变化将无效。
5.备份策略
在实际中为了最大限度地减少数据库还原时间以及降低数据损失数量，一般经常综合使用数据库备份、事务日志备份和差异备份，从而采用下面的备份方案：
o 有规律地进行数据库备份，比如每晚进行备份。
o 较小的时间间隔进行差异备份，比如三个小时或四个小时。
o 在相临的两次差异备份之间进行事务日志备份，可以每10分钟或30分钟一次。
9.1.3 数据库恢复概念
o 一旦数据库出现问题，那么系统管理员就要使用数据库恢复技术使损坏的数据库恢复到备份时的那个状态。
o 数据库恢复模式是指通过使用数据库备份和事务日志备份将数据库恢复到发生失败的时刻，因此几乎不造成任何数据丢失。这成为对付因存储介质损坏而数据丢失的最佳方法。
还原模式
在SQL Server 2005中有三种数据库还原模式，它们分别是简单还原、完全还原和批日志还原。
1．简单还原
指在进行数据库还原时仅使用了数据库备份或差异备份，而不涉及事务日志备份。
2．完全还原
通过使用数据库备份和事务日志备份，将数据库还原到发生失败的时刻，因此几乎不造成任何数据丢失。
3．批日志还原
在性能上要优于简单还原和完全还原模式。它能尽最大努力减少批操作所需要的存储空间。
9.2 备份操作和备份命令
9.2.1 创建备份设备
o 在进行备份以前首先必须创建备份设备，备份设备是用来存储数据库事务日志或文件和文件组备份的存储介质。备份设备可以是硬盘、磁带或管道。
o SQL Server只支持将数据库备份到本地磁带机，而不是网络上的远程磁带机。
o 当使用磁盘时，SQL Server允许将本地主机硬盘和远程主机上的硬盘作为备份设备，备份设备在硬盘中是以文件的方式存储的。
1.使用企业管理器创建备份设备的操作步骤如下。
文件名默认为：C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\名称
2.使用系统存储过程sp_addumpdevice建立备份设备
格式：
sp_addumpdevice 'device_type' , 'logical_name' , 'physical_name' 
o 其中：device_type：备份设备的类型，数据类型为 varchar(20)，没有默认设置，可以是disk（磁盘文件）,pipe（命名管道）,tape（磁带）。
o logical_name：备份设备的逻辑名称，该逻辑名称用于 BACKUP 和 RESTORE 语句中，logical_name 的数据类型为 sysname，没有默认值，并且不能为 NULL。
o physical_name：备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。physical_name 的数据类型为 nvarchar(260)，没有默认值，并且不能为 NULL。
例1：创建一个磁盘备份设备
exec sp_addumpdevice ‘disk’,’student’,’d:\db\student.bak’
例2：创建一个远程磁盘备份设备
exec sp_addumpdevice ‘disk’,’networkdevice’,’\\servername\sharename\path\filename’
例3：创建一个磁带备份设备
exec sp_addumpdevice ‘tape’,’tapedump0’,’ \\.\tape0’
9.2.2 备份命令
o 1.使用备份命令的完整语法
BACKUP DATABASE { 数据库名 | @数据库变量名 }
TO <备份设备> [ ,…n ]
[ WITH
[ [ , ] PASSWORD = {密码 | @密码变量 } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = 百分比n ] ]
]
参数说明
o NOUNLOAD：指定不在备份后从磁带驱动器中自动卸载磁带。
o UNLOAD：指定在备份完成后自动倒带并卸载磁带。
o RESTART：指定SQL Server重新启动一个被中断的备份操作。
o STATS [= 百分比n]：每当另一个n 结束时显示一条消息，它被用于测量进度。如果省略 ，SQL Server 将每完成 10 个百分点显示一条消息。
【例9-1】利用student备份设备对STUDENT数据库进行备份。
backup database student to st
【例9-2】建立一个备份设备NW，并利用此设备对Northwind数据库进行备份。
sp_addumpdevice ‘disk’,’NW’,’d:\db\Northwindbak.bak’
backup database northwind to NW
补充知识：
1、数据库差异备份：
BACKUP DATABASE { 数据库名 | @数据库名变量 }
TO <备份设备> [ , … n ]
[ WITH
[ […… ,] DIFFERENTIAL ]
/*其余选项与相数据库的完全备份同*/
]
【例6】创建临时备份设备并在所创建的临时备份设备上进行差异备份。
BACKUP DATABASE student TO
DISK ='e:\db_temp\stu.bak' WITH DIFFERENTIAL
2、事务日志备份：
BACKUP LOG < 数据库名 | @数据库名变量 >
TO <备份设备> [ , … n ]
[ WITH
/*选项与相数据库的完全备份同*/]
【例7】创建一个命名的备份设备STULOGBK，并备份student数据库的事务日志。
USE master
EXEC sp_addumpdevice 'disk','STULOGBK', 'e:\db_temp\stulog.bak'
BACKUP LOG student TO STULOGBK
9.2.3 备份设备的删除
用系统存储过程sp_dropdevice，命令：
sp_dropdevice ‘备份设备逻辑名’[，’delfile’]
例4：删除前面建立的student备份设备
exec sp_dropdevice ’student’
说明：备份设备已经删除，但备份文件仍然存在
例5：删除前面建立的student备份设备，连同备份文件一起删除
exec sp_dropdevice ’student’,’delfile’
9.3 恢复操作和恢复命令
9.3.1 数据库恢复的过程
1. 准备工作
数据库恢复的准备工作包括系统安全性检查和备份介质验证。
系统发现出现了以下情况时，恢复操作将不进行：
(1)指定的要恢复的数据库已存在，备份文件中记录的数据库与其不同；
2)服务器上数据库文件集与备份中的数据库文件集不一致；
(3)未提供恢复数据库所需的所有文件或文件组。
这些信息包括：
备份文件或备份集名及描述信息；所使用的备份介质类型（磁带或磁盘等）；所使用的备份方法；执行备份的日期和时间；备份集的大小；数据库文件及日志文件的逻辑和物理文件名；备份文件的大小。
2. 执行恢复数据库的操作
（1）进行安全检查
当出现以下集中情况时，系统将不能恢复数据库：
o 使用与被恢复的数据库名称不同的数据库名去恢复数据库；
o 服务器上的数据库文件组与备份的数据库文件组不同；
o 需恢复的数据库名或文件名与备份的数据库名或文件名不同；
（2重建数据库
o 完全数据库备份中恢复数据库时，SQL Server将重建数据库文件，并把所重建的数据库文件置于备份数据库时这些文件所在的位置，所有的数据库对象都将自动重建，用户无需重建数据库的结构。
o 在SQL Server中，恢复数据库的语句是RESTORE。
9.3.2 检查点
SQL Server系统定期将所有脏日志和数据页刷新到磁盘，这就称为检查点。
脏数据在临时更新（脏读）中产生。事务A更新了某个数据项X，但是由于某种原因，事务A出现了问题，于是要把A回滚。但是在回滚之前，另一个事务B读取了数据项X的值(A更新后)，A回滚了事务，数据项恢复了原值。事务B读取的就是数据项X的就是一个“临时”的值，就是脏数据。
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
检查点语法为CHECKPOINT语句。
语法格式：
CHECKPOINT
注释：CHECKPOINT 语句可在后来的恢复中节省时间，方法是创建一个点以确保所有对数据和日志页的修改都写到磁盘上。
检查点也会在下列情况中出现：
（1）当用ALTER DATABASE更改了某数据库选项时，检查点在更改选项的数据库中的行。
（2）当服务器停止时，在服务器上的每个数据库中执行检查点。
9.3.2 数据库的恢复命令
1．使用恢复的完整语法
RESTORE DATABASE { 数据库名 | @数据库名变量 }
[ FROM < 备份设备 > [ ,…n ] ]
[ WITH
[ [ , ] PASSWORD = { 密码 | @密码变量名 } ]
[ [ , ]{ NORECOVERY | RECOVERY | STANDBY = 恢复文件名} ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
]
2．参数说明
① 数据库名：指定从备份还原整个数据库。
② {数据库名|@数据库名变量}：将日志或整个数据库还原到的数据库
③ FROM：指定从中还原备份的备份设备。
④ 备份设备：指定还原操作要使用的逻辑或物理备份设备。
⑤ n：表示可以指定多个备份设备和逻辑备份设备的占位符。
⑥ PASSWORD={密码|@密码就是名}：提供备份集的密码。
⑦ NORECOVERY：指示还原操作不回滚任何未提交的事务。
⑧ UNLOAD：指定在还原完成后自动倒带并卸载磁带。
⑨ RESTART：指定SQL Server重新启动被中断的还原操作。
【例9-2】对STUDENT数据库进行恢复。
restore database student from st
9.4 导 入 导 出
9.4.1 导入导出概述
导入数据是指从SQL Server的外部数据源中检索出数据，并且将数据插入到SQL Server 表的过程。导出数据是将SQL Server实例中的数据在某些特定的环境中，需要分析成某些用户指定格式的过程，例如将SQL Server表的内容复制到Microsoft Access数据库中。
当需要定期将数据从 SQL Server 实例导出时，可以将数据先导出到文本文件，然后由应用程序读取。例如，可以将 SQL Server 实例中的数据转换为 Excel 电子表格格式，并将其存储在便携式计算机中以便在商务旅行中使用。
9.4.2 使用实用程序导入导出数据
例：将工作簿“工资清单.xls ”中的工作表“2008年”导入SQL中，作为数据库Student中的表“表1”。
用命令方式实现：
select * into 表1
from OpenRowSet('microsoft.jet.oledb.4.0',
‘Excel 5.0;database=F:\2009资料\数据库设计\工资系统\工资清单.xls','select * from [2008年$]')
9.4.3 附加与分离数据库
附加：将数据库备份文件加入到SQL中
分离：将数据库从SQL中脱离出来
方法：用企业管理器或者在查询分析器中用命令实现
附加方法1：用建立数据库的方式附加。
CREATE DATABASE [数据库名] ON
( FILENAME = N'主文件名.mdf' ),
( FILENAME = N '日志文件名.ldf' )
FOR ATTACH ;
例：附加数据库book。
CREATE DATABASE book ON
( FILENAME = N'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_Data.MDF' ),
( FILENAME = N'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_log.ldf' )
FOR ATTACH
附加方法2：用系统存储过程实现。
EXEC sp_attach_db @dbname = '数据库名称', @filename1 = 'MDF路径',@filename2= 'LOG路径'
例：EXEC sp_attach_db 'book',
'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_Data.MDF',
'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_log.ldf'
分离方法：用系统存储过程实现。
EXEC sp_detach_db @dbname = '数据库名称'
例：分离数据库book
sp_detach_db book 或 sp_detach_db 'book' 第30教案 实训15：数据库备份恢复
实训15：数据库备份恢复
（一）、实训目的
通过实训掌握数据库备份的方法
通过实训数据库恢复的方法
通过实训掌握数据导入与导出方法
通过实训掌握程序流程结构的分类
（二）、实训内容
例1：创建一个磁盘备份设备
exec sp_addumpdevice ‘disk’,’student’,’d:\db\student.bak’
例2：创建一个远程磁盘备份设备
exec sp_addumpdevice ‘disk’,’pc4’,’\\605-10\sharename\plan\stu’
例3：创建一个磁带备份设备
exec sp_addumpdevice ‘tape’,’tapedump0’,’ \\.\tape0’
例4：利用student备份设备对STUDENT数据库进行备份。
backup database student to st
例5：创建一个命名的备份设备STULOGBK，并备份student数据库的事务日志。
USE master
EXEC sp_addumpdevice 'disk','STULOGBK', 'e:\db_temp\stulog.bak'

BACKUP LOG student TO STULOGBK
例6：删除前面建立的student备份设备
exec sp_dropdevice ’student’
说明：备份设备已经删除，但备份文件仍然存在
例7：删除前面建立的student备份设备，连同备份文件一起删除
exec sp_dropdevice ’student’,’delfile’
例8：对STUDENT数据库进行恢复。
restore database student from st
例9：将工作簿“工资清单.xls ”中的工作表“2008年”导入SQL中，作为数据库Student中的表“表1”。
用命令方式实现：
select * into 表1
from OpenRowSet('microsoft.jet.oledb.4.0',
‘Excel 5.0;database=F:\2009资料\数据库设计\工资系统\工资清单.xls','select * from [2008年$]')
例10：附加数据库book。
CREATE DATABASE book ON
(FILENAME = N'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_Data.MDF' ),
(FILENAME = N'F:\2009年上学期个人资料\数据库教学\数据库\Book\bookdb_log.ldf' )
FOR ATTACH