sql server 备份与恢复系列五 完整模式下的备份与还原 - 花阴偷移
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
上面还原成功,证明了完整恢复模式切换到大容量模式,日志链是不会中断。