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 

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