上篇我编写的差异备份还原过程 ,本篇将介绍日志备份还原过程!
数据库TestYy和表test仍然是跟上篇一样,先准备好基础数据:脚本如下
---初始先清空所有数据
truncate table TestYy.dbo.test
---先定义一个全备份,时间点:10.27 00:00:00
BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_full_1027.bak' WITH INIT
insert into TestYy.dbo.test(name)values('进行日志备份1027_1')
insert into TestYy.dbo.test(name)values('进行日志备份1027_2')
---进行日志备份,时间点:10.27 10:00:00
BACKUP LOG TestYy TO DISK = 'F:\TestYy_log1.bak' WITH INIT
insert into TestYy.dbo.test(name)values('进行日志备份1027_3')
insert into TestYy.dbo.test(name)values('进行日志备份1027_4')
---进行日志备份,时间点:10.27 13:00:00
BACKUP LOG TestYy TO DISK = 'F:\TestYy_log2.bak' WITH INIT
insert into TestYy.dbo.test(name)values('进行日志备份1027_5')
---进行差异备份,时间点:10.28 00:00:00
BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_diff_1.bak' WITH INIT, DIFFERENTIAL
insert into TestYy.dbo.test(name)values('进行日志备份1027_6')
insert into TestYy.dbo.test(name)values('进行日志备份1027_7')
---进行日志备份:10.28 10:00:00
BACKUP LOG TestYy TO DISK = 'F:\TestYy_log3.bak' WITH INIT
insert into TestYy.dbo.test(name)values('进行日志备份1027_8')
----查询数据
select * from TestYy.dbo.test
我们查询下数据,如图
接下来我们进行还原操作!我们先进行还原操作:看还原脚本是如何的,如图
-------还原到10.27 10:00:00-------------------------------------
use master
go
---声明变量
declare @dbName nvarchar(max)='TestYy';
--1.1修改为单用模式
exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
DECLARE @kid varchar(max)
SET @kid=''
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses
WHERE dbid=DB_ID(@dbName) ;
EXEC(@kid) ;
--2.执行还原语句
RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1027.bak' WITH STATS = 10, REPLACE,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log1.bak' WITH STATS = 10,RECOVERY
go
查询结果如图:
那我要还原10.27 13:00:00的呢,又该如何操作呢? 且看脚本
-------还原到10.27 13:00:00-------------------------------------
use master
go
---声明变量
declare @dbName nvarchar(max)='TestYy';
--1.1修改为单用模式
exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
DECLARE @kid varchar(max)
SET @kid=''
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses
WHERE dbid=DB_ID(@dbName) ;
EXEC(@kid) ;
--2.执行还原语句
RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1027.bak' WITH STATS = 10, REPLACE,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log1.bak' WITH STATS = 10,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log2.bak' WITH STATS = 10,RECOVERY
go
结果如图
上述我们讲过,还原都是针对当天的日志还原!如果跨天了 ,我们中间还有个差异备份的 ,那我们如果还原呢
【还原前,我们思考:上篇我们的建议备份计划里说到每天每小时进行一次日志备份,那一天24小时要进行24次日志备份,如果我10月1号全备份,10月30号10点挂了,那我们要还原10月30号9点的备份,通过日志还原,我们得将10月1号到10月30号9点每个小小时的日志备份都进行还原,那就的执行24*29+9次日志还原脚本,这是否有更合理还原办法呢】
做法一:仍保持全备份+日志备份方式进行还原
-------还原到10.28 10:00:00-------------------------------------
use master
go
---声明变量
declare @dbName nvarchar(max)='TestYy';
--1.1修改为单用模式
exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
DECLARE @kid varchar(max)
SET @kid=''
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses
WHERE dbid=DB_ID(@dbName) ;
EXEC(@kid) ;
--2.执行还原语句
RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1027.bak' WITH STATS = 10, REPLACE,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log1.bak' WITH STATS = 10,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log2.bak' WITH STATS = 10,NORECOVERY
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log3.bak' WITH STATS = 10,RECOVERY
go
查看结果如图:
做法二:全备份+差异备份+日志备份进行还原
-------还原到10.28 10:00:00-------------------------------------
truncate table TestYy.dbo.test
use master
go
---声明变量
declare @dbName nvarchar(max)='TestYy';
--1.1修改为单用模式
exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
DECLARE @kid varchar(max)
SET @kid=''
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses
WHERE dbid=DB_ID(@dbName) ;
EXEC(@kid) ;
--2.执行还原语句
RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1027.bak' WITH STATS = 10, REPLACE,NORECOVERY
-------【这里舍弃了10。27的日志备份文件,而直接取10.28号的差异备份文件】
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_diff_1.bak' WITH STATS = 10,NORECOVERY
-------【再加上10月28号09:00的日志备份文件进行还原】
RESTORE DATABASE TestYy FROM DISK = N'F:\TestYy_log3.bak' WITH STATS = 10,RECOVERY
go
结果如图:
综上实验得知:完整的备份和还原计划可这样制定
在利用全备+日志备份时,需要有序并逐个还原所有日志备份。假设要还原周六的数据,则需要上周日的全备和周一到周六的所有日志备份才可以。如果有每天的差异备份,则只需要周日的全备+周五的差异备份+周六的日志备份即可。这样还原起来方便快捷,节省时间成本。
数据正常备份计划
1) 每周星期日的1:00:00执行数据库的完整备份;
2) 每周星期一至星期六每天的1:00:00执行数据库的差异备份;
3) 每天在8:00:00和23:59:59之间、每1小时执行数据库的日志备份;
4) 每个月的最后一个星期日的1:00:00执行数据库的完整备份;
需要还原某天某点的数据时
选择完整备份(不会滚未操作事务),当天的差异备份(如果晚于1点)(不会滚未操作事务),外加日志备份