sql server 备份与恢复系列五 完整模式下的备份与还原

一.概述

  前面介绍了简单恢复模式和大容量恢复模式,这篇继续写完整恢复模式下的备份与还原。在完整恢复模式里最大的优点是只要能成功备份尾日志,就可以还原到日志备份内包含的任何时点(“时点恢复”)。当然对比前二种模式它是牺牲了磁盘I/O性能。

恢复模式

备份策略

数据安全性

I/O性能

简单恢复

完整备份+差异备份

安全最差。最后一次备份之后,所有数据操作丢失。

最优

大容量恢复

完整备份+差异备份+日志备份

折中。批量操作有丢失风险。尾日志备份失败。最后一次备份之后,所有数据操作丢失

折中

完整恢复

完整备份+差异备份+日志备份

相比上面二种最安全。尾日志备份失败。最后一次备份之后,所有数据操作丢失

最差

  在完整恢复模式下,最常见的备份策略,如下图所示:

二. 备份

  在前章中讲到了大容量恢复模式下的备份。备份策略与大容量模式是一样的,同样是完整备份+差异备份+日志备份。这里要突出点是:当误操作发生后,如何还原到误操作之前的一分钟,找出误操作之前的数据。
在”sql server 日志文件结构及误操作数据找回“中有介绍误操作数据找回,但是基于第三方工具ApexSQL Log。虽然该工具方便,但要收费哟。

  我这里有一个BackupTest库,库里有个Employees表

use master
--设置完全模式
ALTER DATABASE BackupTest SET  RECOVERY FULL  
--创建备份设备(有就不要执行)
use master
exec sp_addumpdevice \'disk\', \'BackupTestDevice\',\'F:\SqlService\backup\BackupTestBackup.bak\'
go
--做一次完整备份到备份设备中(备份基准)
backup database  BackupTest to BackupTestDevice

--新增数据
insert BackupTest.dbo.Employees values(\'湖南长沙\')
insert BackupTest.dbo.Employees values(\'湖南湘潭\')
--日志备份
backup log BackupTest to BackupTestDevice

 备份集如下所示:

-- 误操作发生, 忘记加where条件,操作时间是:2018-8-12 10:55  
delete from BackupTest.dbo.Employees 

三.还原(1)

  当误操作发生后,是需要找管理员来进行数据还原。 如果数据库太大,还原是需要很长时间(注意使用副本,不要使用生产库)。 这种情况下就需要等待了。 避免的方法:(1)是做sql审核,不在Managemnet studio里直接操作,避免此类事情发生.(2)是使用粒度更小的备份方式,但相应的复杂些。

--步骤1 备份尾日志
use master
go
backup log BackupTest to BackupTestDevice with norecovery 

go
--步骤2 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复

--步骤3 
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复

--步骤4 用stopat恢复到10:54
restore log BackupTest from BackupTestDevice  with file=21, stopat=\'2018/8/12 10:54\', recovery --事务恢复
--数据又回来了
select * from  BackupTest.dbo.Employees 

  

四.还原(2)

  在前面介绍中,有讲过,完整恢复模式切换到大容量模式,日志链是不会中断。下面来验证

--从完整恢复模式切换到大容量模式
ALTER DATABASE BackupTest SET  RECOVERY bulk_logged 
-- 新增
insert BackupTest.dbo.Employees values(\'湖南株洲\')
--日志备份
backup log BackupTest to BackupTestDevice
-- 删除
delete from BackupTest.dbo.Employees 
-- 尾日志
backup log BackupTest to BackupTestDevice with norecovery 

 备份集如下所示,日志文件ID:22是在大容量模式下备份的,23是尾日志

restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=21,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=22,  recovery 

  当日志还原到文件ID:22时,报错,如下图所示

   跳过文件ID:22, 使用23来提交事务,也会报错,如下所示:

restore log BackupTest from BackupTestDevice  with file=23,  recovery

   经过测试,还原失败,错误是指:与上一次还原到指定时间点有关系。

  下面在测试一个新库TestFULLToBulk

--设置完全模式
ALTER DATABASE TestFULLToBulk SET  RECOVERY FULL  
--做一次完整备份到备份设备中(备份基准)
backup database  TestFULLToBulk to BackupTestDevice
insert TestFULLToBulk.dbo.product values(\'湖南株洲\')
--日志备份
backup log TestFULLToBulk to BackupTestDevice
--设置大容量
ALTER DATABASE TestFULLToBulk SET RECOVERY bulk_logged   

insert TestFULLToBulk.dbo.product values(\'湖南湘潭\')
--日志备份
backup log TestFULLToBulk to BackupTestDevice

  备份集如下:文件ID28是在大容量下进行的备份

  

backup log TestFULLToBulk to BackupTestDevice with norecovery 
go
restore database TestFULLToBulk from BackupTestDevice with file=26, norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=27,  norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=28,  recovery 

  上面还原成功,证明了完整恢复模式切换到大容量模式,日志链是不会中断。

 

版权声明:本文为MrHSR原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/MrHSR/p/9462109.html