Now,我们来做下实验
创建数据库:TestYy ,表Test
CREATE TABLE [dbo].[test]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](6000) NOT NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
接下来我们写备份脚本
-----清空表-------------------------------------------- truncate table test -----10.27完整备份-------------------------------------------- insert into test(name)values('10.27完整备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_full_1027.bak' WITH INIT -----10.28差异备份-------------------------------------------- insert into test(name)values('10.28差异备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_diff_1028.bak' WITH INIT, DIFFERENTIAL --加上DIFFERENTIAL代表差异备份 -----10.29完整备份-------------------------------------------- insert into test(name)values('10.29完整备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_full_1029.bak' WITH INIT -----10.30差异备份-------------------------------------------- insert into test(name)values('10.30差异备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_diff_1030.bak' WITH INIT, DIFFERENTIAL --加上DIFFERENTIAL代表差异备份
如上脚本代码,我们再来看看表的数据结构
接下来我们进行操作差异备份还原操作,我们来模拟操作将 :10.27完整备份+ 10.30差异备份进行还原(注意:这期间还有一次10.29完整备份),执行如图:
USE master; -----10.30差异备份还原10月27号的-------------------------------------------- RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1027.bak' WITH STATS = 10, REPLACE,NORECOVERY --先完整还原 RESTORE DATABASE TestYy FROM DISK='F:\TestYy_diff_1030.bak' WITH STATS = 10, RECOVERY --在差异还原
结果如图,没有文件可用于前滚
那我们再试下:10.30差异备份还原10月29号的(中间没有全备份节点了)
结果显示可用还原.
综上,针对备份我们可以这样定计划
在利用全备+日志备份时,需要有序并逐个还原所有日志备份。假设要还原周六的数据,则需要上周日的全备和周一到周六的所有日志备份才可以。如果有每天的差异备份,则只需要周日的全备+周五的差异备份+周六的日志备份即可。这样还原起来方便快捷,节省时间成本。 数据正常备份计划 1) 每周星期日的1:00:00执行数据库的完整备份; 2) 每周星期一至星期六每天的1:00:00执行数据库的差异备份; 3) 每天在8:00:00和23:59:59之间、每1小时执行数据库的日志备份; 4) 每个月的最后一个星期日的1:00:00执行数据库的完整备份; 需要还原某天某点的数据时 选择完整备份(不会滚未操作事务),当天的差异备份(如果晚于1点)(不会滚未操作事务),外加日志备份附测试的脚本:
-----创建脚本-------------------------------------------- CREATE TABLE [dbo].[test]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](6000) NOT NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] -----清空表-------------------------------------------- truncate table test -----10.27完整备份-------------------------------------------- insert into test(name)values('10.27完整备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_full_1027.bak' WITH INIT -----10.28差异备份-------------------------------------------- insert into test(name)values('10.28差异备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_diff_1028.bak' WITH INIT, DIFFERENTIAL --加上DIFFERENTIAL代表差异备份 -----10.29完整备份-------------------------------------------- insert into test(name)values('10.29完整备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_full_1029.bak' WITH INIT -----10.30差异备份-------------------------------------------- insert into test(name)values('10.30差异备份') BACKUP DATABASE TestYy TO DISK = 'F:\TestYy_diff_1030.bak' WITH INIT, DIFFERENTIAL --加上DIFFERENTIAL代表差异备份 USE master; -----10.30差异备份还原10月29号的-------------------------------------------- RESTORE DATABASE TestYy FROM DISK='F:\TestYy_full_1029.bak' WITH STATS = 10, REPLACE,NORECOVERY --先完整还原 RESTORE DATABASE TestYy FROM DISK='F:\TestYy_diff_1030.bak' WITH STATS = 10, RECOVERY --在差异还原 ---杀死进程的脚本 DECLARE @kid varchar(max) SET @kid='' SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses WHERE dbid=DB_ID('TestYy') ; EXEC(@kid) ; --查询数据 select * from TestYy.dbo.test
如果您也喜欢它,动动您的小指点个赞吧