第16章-备份和恢复数据库.ppt-道客多多

资源描述

1、第16章备份和恢复数据库,内容,16.1 备份数据库16.2 恢复数据库,16.1 备份数据库,为什么要进行数据备份？备份内容谁来做备份工作？什么时候需要备份？如何进行备份？ SQL Server的备份类型备份策略的规划,为什么要进行数据备份？,为保证数据库系统连续正常运转，以及从故障状态尽快恢复。防止数据丢失（错误的更新、病毒、物理损害、自然灾害）,一旦出现问题，能够根据备份进行恢复。数据转移的一种方式。,备份内容,用户数据库系统数据库事务日志,谁来做备份工作？,Sysadmin服务器角色成员 db_owner数据库角色成员 db_backupoperator数据库角色成员

2、,什么时候需要备份？,系统数据库（修改之后立即备份）当修改了master 数据库时当修改了msdb 数据库时当修改了model 数据库时用户数据库：一般是周期性做数据库创建之后，马上备份日志被清空之后，马上备份执行了不记入日志的操作之后，马上备份,系统数据库master,Master数据库中存储着SQL Server服务器配置参数、用户登录帐户、系统存储过程等重要内容,所以需要备份。执行任何影响master数据库的操作后,都要备份该数据库, 这些操作包括：（1）数据库操作类：CREATE DATABASE、ALTER DATABASE、DROP DATABASE。（2

3、）服务器管理类：sp_addserver、sp_addlinkedserver、sp_dropserver、sp_configure、sp_serveroption。（3）服务器登录帐户管理类：sp_addlogin、sp_addremotelogin、sp_droplogin、sp_dropremotelogin、sp_grantlogin、sp_password。（4）备份设备管理：sp_addumpdevice、sp_dropdevice。（5）数据库配置类：sp_dboption、sp_renamedb。,Msdb数据库支持SQL Server代理，为任务调度提供存储空间，所以要

4、定期备份。用户数据库中包含了用户加载的数据信息, 是数据库应用程序操作的主体, 所以要定期备份。事务日志记录用户对数据库的更改操作, 利用日志可以精确地恢复数据库,所以要定期备份。,其他,如何进行备份？,备份到备份设备上设备类型： Tape Disk 如果备份到磁盘上，则可以备份到备份设备上（永久备份设备，需事先创建），也可以直接备份到磁盘上。,备份设备,永久备份设备，在备份之前需要预先建立可以利用企业管理器创建永久备份设备,16-1 创建备份设备第一步,16-2 创建备份设备第二步,利用T-SQL创建备份设备,永久备份设备也可由sp_addumpdevice创建,已创建的永久备份

5、设备记录在master数据库的sysdevices表中,格式:sp_addumpdevice disk | tape, logical_name, physical_name logical_name: 备份设备逻辑名 physical_name:备份设备物理名(文件路径名)。 disk 磁盘设备；tape 磁带设备在系统表sysdevices中没有相应记录的备份设备称为临时备份设备(是一个磁盘文件)。,例子：,下面语句创建一个磁盘备份设备： USE master GO EXEC sp_addumpdevice disk,教学管理_FULL, d:servermssqlbackup教学管理

6、备份设备.dat GO,备份类型,完全备份（海量备份）差异备份日志备份,完全备份（海量备份）,对数据库中的全部信息进行备份。备份不影响数据库的活动（动态备份），同时备份：在备份过程中发生的一切活动未提交事务的执行信息,差异备份,备份：上次完全备份之后发生变化的数据。差异备份过程中发生的活动未提交事务的执行信息差异备份比完全备份节约时间,日志备份,备份从上次日志备份之后的日志记录默认时，日志备份完成后要截断日志：解决日志文件越来越大的问题。须将数据库的还原模型设置为“完全方式”或“大容量日志记录”的方式，才能备份日志。,在数据库选项中设置故障还原模式,使用企业管理器进行

7、备份,下面我们简单介绍一下用企业管理器进行备份（备份到设备或磁盘文件）：,使用企业管理器进行备份,使用企业管理器进行备份,使用企业管理器进行备份,使用企业管理器进行备份,使用T-SQL命令进行备份,1、数据库备份BACKUP DATABASE，格式为：BACKUP DATABASE dbname | dbname_var TO dump_device1, dump_device2,dump_device32 WITH option, .n dbname|dbname_var指定要备份的数据库； dump_device1,dump_device2,dump_device32指定逻辑备份设备名；,

8、options指定一些备份时使用的选项，例如： DESCRIPTION= text | text_var ：给出备份描述文本，最长255个字符； DIFFERENTIAL：增量备份方式。 PASSWORD= password | password_var ：设置密码，恢复时须提供正确密码。 INIT | NOINIT ： INIT：重写媒体； NOINIT：追加媒体。 NAME=backup_set_name|backup_set_name_var：说明备份集名称,使用T-SQL命令进行备份,SQL Server采用在线备份方式进行备份。在备份期间,用户不能执行下列操作：. 数据文件操作，如在

9、ALTER DATABASE中使用ADD FILE或REMOVE FILE子句；. 改变数据库或数据文件长度, 如：DBCC SHRINKFILE, DBCC SHRINKDATABASE；. 执行CREATE INDEX语句；. 执行批拷贝、SELECT INTO、WRITETEXT或UPDATETEXT等非日志操作语句。,使用T-SQL命令进行备份,例如，USE masterGOEXEC sp_addumpdevicedisk,教学管理_FULL,d:servermssqlbackup教学管理备份设备.datGOBACKUP DATABASE 教学管理 to 教学管理_FULLGO,使用T

10、-SQL命令进行备份,2、备份数据库文件或文件组备份文件或文件组时，需要将数据库还原模式设置为完全模式，BACKUP语句的语法格式为：BACKUP DATABASE dbname | dbname_var FILE= 逻辑文件名 | 逻辑文件名变量 | FILEGROUP= 逻辑文件组名 | 逻辑文件组名变量 TO dump_device1, dump_device2, dump_device32 WITH option, .n ,使用T-SQL命令进行备份,例如，下面语句把pubs数据库中的数据文件pubs备份到文件E:backuppubs.dat中-将数据库还原模式设置为完全模式ALTER

11、 DATABASE pubs SET RECOVERY FULL GOBACKUP DATABASE pubsFILE=pubsTO DISK=E:backuppubs.dat,使用T-SQL命令进行备份,3、备份事务日志备份事务日志时，BACKUP语句的语法格式为：BACKUP LOG dbname | dbname_var WITH NO_LOG | TRUNCATE_ONLY | TO dump_device1,dump_device2,dump_device32 WITH option, .n WITH NO_LOG或WITH TRUNCATE_ONLY:说明不做日志备份，只删除事务

12、日志中的无效部分，并且截断日志，释放它们所占用的磁盘空间。这时不需指定备份设备，因为并不保存事务日志。,使用T-SQL命令进行备份,在数据库备份期间不能备份事务日志，但在数据文件备份的同时可以备份事务日志。例如(pubs数据文件备份正在执行):BACKUP LOG pubsTO DISK=e:backuppubs_log.dat 在以下几种情况下，应禁止备份数据库日志：（1）在数据库完整备份以前(因为SQL Server 在完成事务日志备份时将自动截断事务日志 )；（2）截断了事务日志，但没有再次执行数据库备份；（3）向数据库添加文件或从中删除文件之后没有再次备份数据库。,使用T-SQ

13、L命令进行备份,备份策略,制定备份策略时，应考虑以下内容：备份内容和方式备份频率备份数据存储介质备份内容和方式组合可以如下： Full DB Full DB + log Full DB + Differential DB + Log,备份频率,确定备份频率要考虑两个因素：1) 允许丢失的工作量。越频繁, 可能丢失的工作量越小;2) 数据库的事务量。太频繁则会影响事务的执行。具体可考虑以下问题：（1）如果没有数据库,企业的业务能支持多久？如果数据库对每天的事务处理都至关重要,那么就必须经常备份; （2）对不变的历史数据可以只备份一次,但要多复制几个备份，以免备份介质损坏而造成数据丢

14、失；,（3）执行恢复需要多长时间？是海量备份的恢复省时？还是增量备份的恢复省时？如果是长期的增量备份，显然是海量备份恢复省时;如果只有一、两次增量备份,一般是增量备份恢复省时;所以，可以考虑按周做海量备份，每天做增量备份等（4）如果数据库的使用频率和更新频率非常高，可以考虑每天做一次海量备份, 做几次增量备份;相反，如果数据库的更新频率不太高, 只需要每周、甚至每月做一次海量备份。,备份频率,选择备份介质,备份介质类型包括：磁盘、磁带等。磁盘：容量小，价格高，存取快磁带：容量大，价格低，存取慢,数据一致性检查,在执行备份前应该检查数据库中数据的一致性，这样才能保证所备份数据是正确的。 1

15、、检查点机制检查点机制是自动把已完成事务对数据库的修改从缓冲区写入数据库文件的一种手段。每次启动检查点进程时，它把所有脏页（缓冲区中被修改的页面）数据写进数据库文件。自动检查点进程执行的时间间隔是SQL Server根据系统活动情况和recovery interval(最长恢复时间)配置选项计算出来的；可用CHECKPOINT语句强制执行一个检查点检查； CHECKPOINT语句的权限属于sysadmin、db_owner或db_backupoperator角色的成员，且不能授予他人。,2、DBCC-检查数据库的一致性在执行备份前，应该执行DBCC语句检查数据库的一致性，从而排除错误

16、。用于检查数据一致性的DBCC语句包括： (1) DBCC CHECKDB：检查数据库中数据的一致性; (2) DBCC CHECKALLOC或DBCC NEWALLOC：检查数据库页面分配的正确性; (3) DBCC CHECKCATALOG：检查系统表的一致性; (4) DBCC CHECKCONSTRAINTS：检查约束的一致性; (5) DBCC CHECKFILEGROUP：检查文件组的一致性; (6) DBCC CHECKIDENT：检查标识值的一致性; (7) DBCC CHECKTABLE：检查表的一致性。,数据一致性检查,使用完全备份的例子,创建数据库，实施完全备份,实施

17、完全备份,实施完全备份,每天12:00进行完全备份, 出错时间: Tuesday 上午11:50 丢失1天工作量,Sunday,Monday,Tuesday,使用全库+日志备份例子,每天12:30进行完全备份, 每天11:45, 12:00, 12:15 进行日志备份, 出错时间: Monday 11:50丢失5分钟工作量,使用完全+差异+日志备份例子,Monday,Tuesday,完全备份,差异备份,差异备份,.,Monday 上午11:30进行完全备份，以后每15分钟进行一次日志备份，每1小时进行一次差异备份,16.2 恢复数据库,自动恢复手工恢复,自动恢复,1.每次启动SQL Ser

18、ver时自动执行恢复； 2.检查每个数据库：先修复master数据库，之后是model数据库，在清除tempdb数据库中的临时数据后，再修复msdb、pubs、distribution等数据库，最后修复用户数据库。 3. 重做已提交的事务；回滚未提交的事务。,手工恢复,当系统出现故障时，由系统管理员或数据库所有者执行RESTORE命令进行恢复。数据库的备份权限可由数据库所有者授予其他用户，但恢复操作权限不能授予他人。包括四个方面：恢复前的准备恢复顺序恢复用户数据库恢复系统数据库,恢复前的准备,设置限制访问数据库选项（右击数据库属性选项限制访问）,如日志没有损坏，则备份日志；,恢复

19、顺序,1. 恢复最近的全量数据库备份； 2. 恢复最近的差异数据库备份(如有)； 3. 恢复自差异数据库备份以后的所有日志备份(按备份的先后顺序)。,恢复顺序,Restore语句,RESTORE语句有以下几种语法格式：使用数据库备份来恢复整个数据库；使用数据文件备份来恢复数据库中的部分内容、数据库中的文件或文件组；使用事务日志备份来恢复数据库。,RESTORE DATABASE 数据库名 | 数据库名变量 FROM ,n WITH RESTRICTED_USER , FILE= 备份号 | 备份号变量 , PASSWORD= 口令 | 口令变量 , MEDIANAME= 介质名 | 介质

25、中的各种参数用于设置数据库恢复操作选项，这些参数包括： RESTRICTED_USER：说明恢复后的数据库只允许db_owner 、dbcreator或sysadmin成员访问，即数据库恢复后将设置为受限访问模式。 FILE= 备份号 | 备份号变量：在一个备份介质上，可能存在多个备份数据集，使用FILE参数指出恢复时使用第几个备份数据集。 PASSWORD= 口令 | 口令变量：说明备份集所使用的密码。如果备份时添加了密码，则从备份集执行数据恢复操作时必须提供密码。,Restore语句参数说明（不讲）,MEDIANAME= 介质名 | 介质名变量：指出备份集合的介质名称。必须与备份卷上

26、的介质名称相匹配，否则，RESTORE语句将禁止执行。若不给出此参数，RESTORE语句将跳过该项检查。 MEDIAPASSWORD= 介质口令 | 介质口令变量：指出介质集口令。若在格式化介质集时设置了口令，则访问该介质集上的所有备份集都必须提供口令。 MOVE 逻辑文件名 TO OS文件名：将逻辑文件名指定的数据文件移动到OS文件名指定的文件位置。默认时，逻辑文件名被恢复到它原来的位置。 NORECOVERY：恢复后不回滚未提交的事务。 RECOVERY：恢复后回滚未提交的事务。,Restore语句参数说明（不讲）,STANDBY=撤销文件名：指出撤消文件名，使用该文件可以撤消已经

27、执行的恢复操作。若未指出NORECOVERY、RECOVERY或STANDBY参数时，则默认RECOVERY。 KEEP_REPLICATION：要求恢复操作在将发布的数据库恢复到创建它的服务器以外的其它服务器时，保留复制设置。若指定了该选项，则不能选择NORECOVERY选项。 UNLOAD：恢复完成后自动绕带，并卸载磁带。 NOUNLOAD：恢复完成后不自动卸带，这是默认设置。 NOREWIND：恢复完成后使磁带保持打开状态。 REWIND：恢复完成后释放磁带并倒带。,Restore语句参数说明（不讲）,REPLACE：关闭恢复前的安全检查，重建所有数据库及相关文件，不管与其同名的数据库文

28、件是否存在。若未指定此选项，则恢复前要进行安全检查，若发现下列情况，将放弃恢复操作。服务器上存在同名数据库；数据库名称与备份集中记录的数据库名称不同。 RESTART：从上次中断点开始重新执行恢复操作。使用此选项时，其他参数设置应与上次恢复时一样。 STATS=percentage：说明每完成多少恢复工作量显示一个统计信息。默认时，每恢复10%返回一统计值。 STOPAT=date_time|date_time_var ：只恢复指定时刻之前的数据库内容，只适用于从日志备份中恢复数据库。,Restore语句参数说明（不讲）,STOPATMARK=mark_nameAFTER datetime

29、：将数据库恢复到mark_name指定的事务标记处，包括mark_name指定的事务。 STOPBEFOREMARK=mark_nameAFTER datetime：将数据库恢复到mark_name指定的事务标记之前，即不包括mark_name指定的事务。若无AFTER datetime，恢复将在mark_name指定的第一个事务标记处停止；若有AFTER datetime，恢复将执行到datetime时间点之后的第一个事务标记处。,Restore语句参数说明（不讲）,恢复用户数据库,恢复用户数据库时SQL Server执行以下操作： 1) 所有备份数据被拷贝到数据库； 2) 数据库备份操作时

30、所有已写入数据库但未完成的事务被回滚，从而保持数据库中数据的一致性; 下面举例说明怎样用RESTORE恢复用户数据库; 例1、从备份设备中恢复教学管理数据库RESTORE DATABASE 教学管理FROM 教学管理_FULL,例2、从全量备份和增量备份中恢复教学管理数据库。设备中第5个备份集是增量备份。RESTORE DATABASE 教学管理 FROM 教学管理_FULL WITH NORECOVERY -恢复后不回滚未提交的事务RESTORE DATABASE 教学管理 FROM 教学管理_FULL WITH FILE=5,恢复用户数据库,例3、从备份文件中恢复数据库中的数据文件pubs

31、RESTORE DATABASE pubs FILE =pubs FROM DISK=E:backuppubs.dat 例4、把pubs数据库恢复到2002年12月1日下午6:00以前的状态：RESTORE DATABASE pubs FROM pubsDevice WITH NORECOVERY -恢复后不回滚未提交的事务 RESTORE LOG pubs FROM pubsDevice WITH RECOVERY, -恢复后回滚未提交的事务STOPAT=2002-12-1 6:00 PM,恢复用户数据库,系统数据库控制SQL Server的运行,当它们受到损坏时,轻则影响系统的性能,重则造

32、成系统崩溃,使SQL Server无法启动。系统数据库的恢复方法较特殊,不同于一般数据库。下面分别介绍master和msdb的恢复方法,恢复系统数据库恢复master数据库,master数据库的恢复步骤为： (1)若master损坏严重，已致使SQL Server无法启动，则要运行rebuildm.exe程序来重构master。否则，进入第二步; Rebuildm.exe在重构master的同时也重构msdb和model，所以重构后需恢复这些数据库。重构master将导致所有用户数据丢失，故仅在系统无法启动时才考虑重构master。,恢复系统数据库恢复master数据库,(2)在命令行

33、状态下执行sqlservr m命令，以单用户方式启动SQL Server。停止所有其他SQL Server服务和SQL Server数据库应用程序; (3)用RESTORE从备份数据中恢复master。例如：USE master GO RESTORE DATABASE master FROM MasterDevice GO,恢复系统数据库恢复master数据库,(4)若执行了第一步,则需恢复msdb和model等系统DB; (5)若上次备份master后用户又执行了引起master改变的语句，则执行后续操作; (6)若自上次备份master后用户又创建了数据库,则需调用sp_attach_db

34、将这些数据库附加到服务器中,并恢复到它们以前的数据状态。其语法格式为：sp_attach_db dbname=dbname,filenamei=filenamei ,16 其中，dbname为待附加的数据库名; filenamei是所附加数据库对应的物理文件路径名(最多指定16个文件, 即1i16)。,恢复系统数据库恢复master数据库,例如，下面语句将MyDB1数据库附加到SQL Server服务器中：sp_attach_db MyDB1, C:dataMyDB1.mdf, C:dataMyDB1_2.ndf, C:dataMyDB1_log.ldf, C:dataMyDB1_log2.ldf (7)在完成以上各步后,以正常方式重启SQL Server,恢复系统数据库恢复master数据库,SQL Server在重构master时,将删除msdb数据库，这将导致msdb上的任务调度信息丢失,所以当msdb损坏或在重构master后,应恢复msdb。恢复msdb数据库的步骤为： (1)停止SQL Server代理服务的运行。 (2)使用RESTORE语句，象恢复普通数据库一样恢复msdb数据库。例如：USE master GO RESTORE DATABASE msdb FROM MsdbDevice GO,恢复系统数据库恢复msdb数据库,

展开阅读全文