差别备份的恢复问题[MySQL防范]
本文“差别备份的恢复问题[MySQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
A : 情形是这样的
create database test
create table t(a int)
insert into test..t select 1
然后举行一次完好备份
backup database test to disk='c:\test.bak'
insert into test..t select 2
再举行一次完好备份
backup database test to disk='c:\test.bak'
insert into test..t select 3
此时用 restore database test from disk='c:\test.bak' with file=1
后果为 1, 此为精确
用 restore database test from disk='c:\test.bak' with file=2
后果为 1,
2 此也为精确
当表t中为1,2,3的时刻,在插入一条记录后果为1,2,3,4然后举行一次差别备份
backup database test to disk='c:\test.bak' with differential
然后往履行delete from t 删除全部记录
我目前想恢复最后的那次差别备份(后果为1,2,3,4),用语句改若何实现呢?
---------------------------------------------------------------
下面的是具体的历程,在我的电脑上测试成功:
--排除环境,避免现有的数据影响测试后果
exec master..xp_cmdshell 'del c:\text.bak'
if exists(select * from master..sysdatabases where name='test')
drop database test
go
--成立数据库
create database test
go
--翻开成立的数据
use test
go
--成立测试表
create table t(a int)
--切换回master数据库
use master
go
--插入数据1
insert into test..t select 1
go
--然后举行一次完好备份
backup database test to disk='c:\test.bak'
go
--插入数据2
insert into test..t select 2
go
--再举行一次完好备份
backup database test to disk='c:\test.bak'
go
--插入3,4
insert into test..t select 3
insert into test..t select 4
go
--差别备份:
backup database test to disk='c:\test.bak' with differential
--删除数据库
drop database test
--复原数据库和差别数据库备份
--复原完好备份
restore database test from disk='c:\test.bak' with file=2,norecovery
--复原差别备份的内容
restore database test from disk='c:\test.bak' with file=3,recovery
--显示恢复后的数据
select * from test..t
---------------------------------------------------------------
都已经说的好懂得了,怎么大概会不行呢?
前段时间我就做过近似程序的!
必须阐明的是:在恢复差别备份时,必须恢复最后一次的完好备份!!(牢记)
并且下面的两个语句必须同时履行,即放在一个事件中.
restore database test from disk='c:\test.bak' with file=离你要恢复的差别备份近来一次的完好备份号,norecovery
restore database test from disk='c:\test.bak' with file=你要复原的差别备份号,recovery
具体的备份号可以从下面得到:(你可以认真研究一下backupfile,backupset,backmediaset,backupmediafamily几个表,可以发现规律)
select backup_start_date as 备份时间,position as 备份号,
case type when 'D' then '完好备份' when 'I' then '差别备份' end as 备份范例
from msdb..backupset where database_name='test'
and media_set_id in
(select distinct media_set_id from msdb..backupmediafamily where physical_device_name='c:\test.bak')
order by position
假如还不行的话,可以给我留言~
---------------------------------------------------------------
---履行下面的序列:
create database test
go
use test
go
create table test..t(a int)
insert test..t select 1
backup database test to disk='c:\test.bak'
insert test..t select 2
backup database test to disk='c:\test.bak'
insert test..t select 3
insert test..t select 4
backup database test to disk='c:\test.bak' with differential
delete test..t
go
--下面开始恢复:
restore database test from disk='c:\test.bak' with file=2,norecovery --对应你最后一次的完好备份
restore database test from disk='c:\test.bak' with file=3 --对应你要复原的差别备份
go
select * from test
本文地址: | 与您的QQ/BBS好友分享! |