1、备份与还原,Page 2/28,本章目标,了解备份和还原的概念 掌握SQL Server的备份方法 掌握备份策略的制定 掌握运用备份还原数据库的方法,Page 3/28,为什么需要备份,数据丢失的原因 程序错误 人为错误 计算机失败 磁盘失败 灾难（如火灾、地震）和偷窃 什么是备份 数据的副本 用于在系统发生故障后还原和恢复数据 文件复制备份,Page 4/28,恢复的概念,数据库恢复概念基于事务完整性ACID属性中的D，即事务持续性，持续性意味着事务提交后将是永久性的，即使发生硬件故障。sql server 使用先写事务日志来实现事务持续性，每个事务都先写入事务日志，然后才写入数据文件。这为
2、恢复计划提供了以下好处：事务日志确保能够将每个事务恢复到服务器停机前的状态事务日志使得能够在处理事务时进行备份事务日志降低了硬件故障的影响，因为可以将事务日志和数据文 件放在不同的磁盘子系统中恢复策略应该基于对事务丢失的容忍度，涉及选择各种恢复选项、备份方法、指定备份计划以及对数据库存储在远离现场的地方。,Page 5/28,恢复模式2-1,SQL Server的备份和恢复功能非常灵活，提供三种可选择的恢复模型。可以根据恢复需求，使用下列恢复模型来配置事务日志。,Page 6/28,恢复模式2-2,Page 7/28,简单恢复模型,简单恢复模型适合于只需要确保每个事务的原子性，而无需确保其连续
3、性的数据库 简单恢复模型命令SQL在检查点截断（清空）事务日志，事务日志只将事务保留到它被写入数据文件为止，此后，其他事务将以循环方式重用该事务所占空间。由于这种事务日志是临时性的，因此不需要备份。这种恢复模型的优点是事务日志小，但很可能丢失最后一次完整备份或差异备份后的所有事务基于简单恢复模型的恢复计划可能是：每周执行一次完整备份，在非周末的晚上执行差异备份。 使用简单恢复计划还原时，采用如下步骤： 1、还原最新的完全备份 2、还原最新（可选）的差异备份,Page 8/28,完整恢复模式,提供最完美的恢复计划，使用这种模型时，所有事务（包括大容量日志操作）都写入事务日志中，这种模型的主要优点
4、是，可以将数据库中提交的每个事务都还原到故障发生的状态。 实践提示：生产数据库，建议使用完整恢复模型，事务日志放到与数据文件不同的容错磁盘系统中。 典型备份计划： 每周执行两次完整数据库备份，其它时间每天晚上执行差异备份，另外，整个白天执行事务日志备份，从每天两次到15分钟一次。使用完整恢复计划还原时，采用如下步骤： 1、备份当前事务日志 2、还原最新的完整备份 3、如果在最后一次完整备份后执行了差异备份，则还原最新的差异备份 4、依次还原最后一次完整备份或差异备份后的所有事务日志备份,Page 9/28,大容量日志恢复模型,大容量日志恢复模型类似于完整恢复模型，但不将下列操作写入日志：bul
5、k insertDML命令 select * into tableBLOB操作：witetext 和updatetext创建索引 事务日志中标记发生大容量日志操作，并记录他们所影响的区，备份日志时，将这些区复制到日志中，代替大容量日志标记。 应用：仅当数据库执行大容量日志操作，且提高这些操作的性能非常重要，才能使用这种模型,Page 10/28,设置恢复模型,对于新的数据库，将使用系统数据库model的恢复模型，默认情况下企业版和标准版使用完整恢复模型，个人版和desktop版使用简单恢复模型，但可以通过修改系统数据库model来设置恢复模型 DDL： alter database datab
6、asename set recovery option option包括：full bulk_logged simple,Page 11/28,设置恢复模型,简单恢复模式完整恢复模式大容量日志恢复模式,Page 12/28,备份类型,Page 13/28,用户数据库备份策略,恢复到分钟：如果可能，每周运行两次完整备份，使用每晚的差异备份和在业务进行期间每10分钟事务日志备份，不使用“在检查点截断日志”选项，因为将不能恢复某些事务，要提高备份速度，尽可能使用多个备份设备。 恢复到工作点：如果可能，每周运行两次完整备份，使用每晚的差异备份和在业务进行期间每10分钟事务日志备份，不使用“在检查点截断
7、日志”选项，使用命名事务将命名标记插入事务日志中，要提高备份速度，尽可能使用多个备份设备。 恢复到小时：如果可能，每周运行两次完整备份，使用每晚的差异备份和在业务进行期间每10分钟事务日志备份，不使用“在检查点截断日志”选项，使用命名事务将命名标记插入事务日志中，要提高备份速度，尽可能使用多个备份设备。 每日改变的恢复：每周至少运行一次完整备份，使用每晚的差异备份和在业务进行期间没4小时备份事务日志，不使用“在检查点截断日志”选项。 只读：计划每30日做一个数据库完整备份，并且无论何时数据库被修改，都要补充一次额外的完整备份,Page 14/28,备份还原策略,Page 15/28,示例1,完
8、整备份和还原 只能选择任意的一个完整数据库备份进行还原 还原到10:00 还原到11:00 还原到12:00,Page 16/28,示例2,完整备份+差异备份与还原 如果需要还原到11:00时的数据库状态 完整数据库备份1+差异数据库备份2 如果需要还原到12:30时的数据库状态 完整数据库备份2+差异数据库备份3,Page 17/28,示例3,完整备份+日志备份与还原 如果需要还原到11:00时的数据库状态 完整备份1+日志备份1+日志备份2 如果需要还原到12:30时的数据库状态 完整备份2+日志备份3 完整备份1+日志备份1+日志备份2+日志备份3 如果需要恢复到10:45时的状态 完整
9、备份1+日志备份1+日志备份2 指定到10:45的恢复即时点,Page 18/28,备份设备,SQL Server支持的媒体 磁盘 磁带 备份设备 物理 如： C:BackupsAccountingFull.bak 逻辑 存储在 SQL Server 内的系统表中如：Accounting_Backup,Page 19/28,创建备份设备,使用系统存储过程创建备份设备 在SQL Server 中，可以使用sp_addumpdevice语句创建备份设备，其语法形式如下：sp_addumpdevice 磁盘类型,备份设备的逻辑名称,备份设备的物理名称【例】创建备份设备backup_xscjuse m
10、asterexec sp_addumpdevice disk ,backup_xscj, e:backbackup_xscj.bak,Page 20/28,数据备份,T-SQL创建完全备份语法:backup database 数据库名 to 设备逻辑名with init /noinit-init 表示重写-noinit 表示追加(默认)【例】把xscj数据库完全备份到backup_xscj设备里backup database xscj to backup_xscj with noinit,Page 21/28,备份到文件,T-SQL备份到文件语法: backup database 数据库名 t
11、o disk=物理路径【例】备份xscj数据库到临时设备xscj_backup.bak里backup database xscj to disk=c:xscj_backup.bak,Page 22/28,差异备份,T-SQL创建差异备份语法:backup database 数据库名 to 永久备份设备逻辑名with differential【例】追加xscj数据库的差异备份到backup_xscj设备里backup database xscj to backup_xscj with differential, noinit,Page 23/28,日志备份,T-SQL创建日志备份语法:backu
12、p log 数据库名 to 设备逻辑名with init/noinit【例】追加xscj数据库的日志备份到backup_xscj设备里backup log xscj to backup_xscj with noinit注意:还原模型为简单时,不允许做日志备份.,Page 24/28,备份故障点日志,备份故障点日志：备份服务器最后一次Down机时刻的日志语法:backup log 数据库名 to 备份设备逻辑名 with no_truncate 【例】xscj数据库down掉后,在恢复xscj数据库前,做故障 点备份backup log xscj to backup_xscj with noin
13、it, no_truncate,Page 25/28,截断日志,当事务存储在独立的设备中时，每次修改数据都将导致事务日志增长 解决方案是备份事务日志的非活动部分，然后将其删除。默认情况下，备份事务日志也将截断日志。 例如：如果磁盘满，则需要在没有数据库备份情况下截断事务日志，在不进行备份的情况下无法截断日志，然而可以使用backup nolog或backup truncateonly 来截断日志 Backup log test with truncate_only,Page 26/28,恢复数据库,TransactSQL恢复数据库 从完全备份中恢复数据库 语法:restore database
14、 数据库名 from 永久备份设备逻辑名【例】先把xscj数据库备完全份到xscj_full设备里,再恢复数据库.backup databse xscj to xscj_fullrestore database xscj from xscj_full,Page 27/28,恢复数据库,TransactSQL恢复数据库 恢复数据库的日志备份 语法:restore log 数据库名 from 永久备份设备逻辑名【例】恢复xscj_backup 里的日志备份内容restore log xscj from xscj_backup,Page 28/28,恢复数据库,TransactSQL恢复数据库 从备
15、份文件恢复数据库 语法:restore database 数据库名 from disk=物理路径【例】从备份文件xscj_backup.bak中恢复数据库restore database xscj from disk=c:xscj_backup.bak,Page 29/28,分步恢复数据库,TransactSQL恢复数据库语法:restore database 数据库名 from 永久备份设备名with file=n ,recovery/norecoveryn 表示第几个文件 在备份设备中从1开始数recovery 使数据库可以运行,但无法还原其它事务 norecovery 是数据库不在运行,
16、但可以还原其它务,Page 30/28,分步恢复数据库,【例】从backup_xscj数据库中分布恢复数据库restore database xscj from backup_xscj with file=1 ,norecoveryrestore database xscj from backup_xscj with file=2 ,norecoveryrestore log from xscj from backup_xscj with file=3 recovery,Page 31/28,恢复计划,实际恢复模式取决于损坏类型和使用的恢复计划 恢复模型：简单 数据文件损坏：1 重新启动服务器
17、2 还原完整备份3 还原最新的差异备份 事务日志损坏重新启动服务器，将自动创建一个1MB的新事务日志,Page 32/28,恢复计划,完整或大容量日志 数据文件损坏：1 使用选项no_truncate备份当前的事务日志2 还原完整备份3 还原最新的差异备份4 还原最后一次差异或完整备份后的全部事务日志，这将恢复所有已提交的事务日志文件损坏1 还原完整备份2 还原最新的差异备份3 还原最后一次差异或完整备份后的全部事务日志，这将恢复所有已提交的事务， 最后一次备份后发生的事务将丢失,Page 33/28,案例分析,案例1：完整数据库备份与还原 案例2：完整+差异备份与还原方案 案例3：完整+日志
18、备份与还原方案,Page 34/28,案例1：完整备份与还原,1:创建备份设备test1 use master exec sp_addumpdevice disk,test1,d:test1.bak go 2:完整备份 backup database xscj to test1 with noinit go 3:删除xscj数据库 4：还原数据库xscj restore database xscj from test1 go,Page 35/28,案例2：完整+差异备份与还原,1：创建备份设备test2，test3. use master exec sp_addumpdevice disk,t
19、est2,d:test2.bak use master exec sp_addumpdevice disk,test3,d:test3.bak 2:完整备份 backup database xscj to test2 with noinit 3：修改数据库（例如添加一个表） use xscj go create table test2 (test1 varchar(20) not null) 4：差异备份 backup database xscj to test3 with differential, noinit 5：删除xscj数据库 6：还原数据库 restore database xs
20、cj from test2 with norecovery go restore database xscj from test3 with recovery,Page 36/28,3:完整+日志备份与还原方案,1：创建备份设备test4，test5. use master exec sp_addumpdevice disk,test4,d:test4.bak go use master exec sp_addumpdevice disk,test5,d:test5.bak 2:完整备份 backup database xscj to test4 with noinit 3：修改数据库（例如添
21、加一个表） use xscj go create table test3 (test1 varchar(20) not null) 4：日志备份 backup log xscj to test5 with noinit 5：删除xscj数据库 6：还原数据库 restore database xscj from test4 with norecovery go restore log xscj from test5 with recovery,代理服务,Page 38/28,代理服务概述,SQL Server 代理是一个任务规划器和警报管理器，在实际应用时，可以将那些周期性的工作定义成一个任务
22、，在SQL Server代理的帮助下自动执行；在自动执行时，若出现故障，则SQL Server代理自动通知操作员，操作员获得通知后及时排除故障。如此在任务、操作员、警报三者之间既相互独立，又相互联系、相互补充，构成了自动完成某些任务的有机整体。,Page 39/28,SQL Server代理服务配置,SQL Server 代理允许自动处理不同的管理任务，启动后可以利用SQL Server 管理平台对其进行配置，其具体步骤如下： 打开SQL Server 管理平台，展开指定的服务器，用右键单击SQL Server 代理图标，从快捷菜单中选择属性选项，则出现SQL Server 代理属性对话框，选
23、择常规页框，选择高级页框,图 SQL Server 代理属性常规页框,SQL Server 代理属性高级页框,Page 40/28,SQL Server代理服务配置,3. 选择警报系统页框。使用此页可以查看和修改由SQL Server代理警报所发送的消息的设置。 4. 选择作业系统页框,SQL Server 代理属性警报系统页框,图SQL Server 代理属性作业系统页框,Page 41/28,SQL Server代理服务配置,5. 选择连接页框，如图所示。使用此页可查看和修改 SQL Server代理服务与SQL Server 之间的连接设置。 6. 选择历史记录页框，如图所示。使用此页可
24、以查看和修改用于管理SQL Server代理服务历史记录日志的设置。,图SQL Server 代理属性连接页框,图 SQL Server 代理属性历史记录页框,Page 42/28,定义操作员,使用SQL Server 管理平台可以创建一个操作员，其具体步骤如下： 打开SQL Server管理平台，展开SQL Server 代理，右击操作员图标，从快捷菜单中选择新建操作员选项，则出现新建操作员属性对话框 选择通知页框，使用此页可设置向操作员通知的警报和作业。,新建操作员属性常规页框,新建操作员属性通知页框,Page 43/28,作业管理,自动处理一个任务的第一步是创建对应的作业。一般来说，如果
25、要创建作业，必须执行以下三个步骤：（1）定义作业步（2）如果该作业不是用户指定执行，创建作业执行的计划时间（3）通知操作员作业的状态,Page 44/28,作业步骤定义,作业步骤是作业对数据库或服务器执行的操作。每个作业必须至少有一个作业步骤。作业步骤可以为：（1）可执行程序和操作系统命令；（2）Transact-SQL 语句，包括存储过程和扩展存储过程；（3）Microsoft ActiveX 脚本；（4）复制任务；（5）Analysis Services 任务；（6）Integration Services 包。,Page 45/28,创建作业,这里使用SQL Server 管理平台创建作
26、业，其具体步骤如下： 打开已经启动的SQL Server代理，用右键单击作业图标，从快捷菜单中选择新建作业选项，则出现新建作业属性对话框，如图所示。 每个作业必须有一个或者多个步骤，所以，除了定义作业属性外，在保存作业前，还至少要定义一个作业步骤。如图,图新建作业属性对话框,图 新建作业步骤常规页框,Page 46/28,创建作业,3. 选择新建作业步骤对话框中的高级页框，如图12-11所示。 4. 选择新建作业中的计划页框，如图12-12所示。,新建作业步骤高级页框,新建作业计划对话框,Page 47/28,创建作业,5. 选择新建作业中的通知页框，如图所示，可以为现有的作业设置作业执行状态通知。,图 新建作业属性通知页框,Page 48/28,本章总结,了解备份和还原的概念 掌握SQL Server的备份方法 掌握备份策略的制定 掌握运用备份还原数据库的方法,Page 49/28,您的潜力，我们的动力,THANK YOU !,
