SQL SERVER 2005 支持三种方式的备份/恢复
1、简单恢复模式下的备份/恢复模
2、完整恢复模式下的备份/恢复
3、大容量日志恢复模式下的备份/恢复
1、简单恢复模式下的备份/恢复模
2、完整恢复模式下的备份/恢复
3、大容量日志恢复模式下的备份/恢复
【注】Microsoft关于备份/恢复的详解:
以下是一次完整恢复模式下的备份/恢复测试
一、备份
1、设置完整恢复模式
use master
go
alter database wwww set recovery full;
use master
go
alter database wwww set recovery full;
2、备份数据库
backup database wwww
to disk='d:/test/db/wwww.bak'
with init;
backup database wwww
to disk='d:/test/db/wwww.bak'
with init;
3、备份日志文件
backup log wwww
to disk='d:/test/db/log.bak';
backup log wwww
to disk='d:/test/db/log.bak';
4、对数据库做第一次修改,创建表t1
use wwww
go
select * into t1 from guangd;
use wwww
go
select * into t1 from guangd;
5、备份日志文件
use master
go
backup log wwww
to disk='d:/test/db/log01.bak';
use master
go
backup log wwww
to disk='d:/test/db/log01.bak';
6、对数据库做第二次修改,创建表t2
use wwww
go
select * into t2 from guangd;
use wwww
go
select * into t2 from guangd;
7、备份日志文件
use master
go
backup log wwww
to disk='d:/test/db/log02.bak';
use master
go
backup log wwww
to disk='d:/test/db/log02.bak';
8、对数据库做第三次修改,创建表t3
use wwww
go
select * into t3 from guangd;
use wwww
go
select * into t3 from guangd;
9、备份尾日志
use master
go
backup log wwww
to disk='d:/test/db/logtail.bak'
with norecovery;
use master
go
backup log wwww
to disk='d:/test/db/logtail.bak'
with norecovery;
二、删除数据库(假设数据库数据完全丢失)
use master
go
drop database wwww
三、将数据库恢复至第一次修改后的状态
1、还原数据库
use master
go
restore database wwww
from disk='d:/test/db/wwww.bak'
with norecovery;
use master
go
restore database wwww
from disk='d:/test/db/wwww.bak'
with norecovery;
2、还原日志文件
use master
go
restore log wwww
from disk='d:/test/db/log.bak'
with norecovery;
use master
go
restore log wwww
from disk='d:/test/db/log.bak'
with norecovery;
3、还原第一次修改后的日志文件
use master
go
restore log wwww
from disk='d:/test/db/log01.bak'
with norecovery;
use master
go
restore log wwww
from disk='d:/test/db/log01.bak'
with norecovery;
4、恢复数据库
use master
go
restore database wwww
with recovery;
本文出自 51CTO.COM技术博客
use master
go
restore database wwww
with recovery;