--创建数据库
create database db_oa
--周日进行全备
backup database db_oa to disk='c:\full.bak'
--创建表
create table test(id int identity primary key , str1 varchar(800))
--周一差异备份
BACKUP DATABASE db_oa TO disk='c:\diff.bak' WITH DIFFERENTIAL
--插入一条数据
insert into test select '差异后'
--周二日志备份
backup log db_oa to disk='c:\log1.bak'
--插入第二条数据
insert into test select '日志后'
--周三差异备份
BACKUP DATABASE db_oa TO disk='c:\diff2.bak' WITH DIFFERENTIAL
--插入第三条数据
insert into test select '差异后'
--周四日志备份
backup log db_oa to disk='c:\log2.bak'
--周五不小心drop掉了表
drop table test
--要求恢复数据库最新的记录
--恢复方案有：
--恢复一：日+1+2+3+4
use master
go
restore database db_oa from disk='c:\full.bak' with norecovery
restore database db_oa from disk='c:\diff.bak' with norecovery
restore log db_oa from disk='c:\log1.bak' with norecovery
restore database db_oa from disk='c:\diff2.bak' with norecovery
restore log db_oa from disk='c:\log2.bak' with recovery
--恢复二：日+3+4
use master
go
restore database db_oa from disk='c:\full.bak' with norecovery
restore database db_oa from disk='c:\diff2.bak' with norecovery
restore log db_oa from disk='c:\log2.bak' with recovery
--恢复三：日+2+4
use master
go
restore database db_oa from disk='c:\full.bak' with norecovery
restore log db_oa from disk='c:\log1.bak' with norecovery
restore log db_oa from disk='c:\log2.bak' with recovery
--查看
use db_oa
go
select * from test
--结果
/*
1 差异后
2 日志后
3 差异后
*/
--结论：恢复日志备份时，要求必须有全备和连续的日志备份，说明日志备份上下次之间是连接的。
-- 恢复差异备份时，全备+最近的差异即可，说明差异备份记录的是最近的全备到该差异备份的事物。