共计 3159 个字符，预计需要花费 8 分钟才能阅读完成。
本篇内容介绍了“SqlServer 备份和恢复的方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
SqlServer 备份和恢复
备份
— 创建测试数据库 chen20181123
create
database
chen20181123
on
(name=chen_data,
filename= D:\hrtest\DB\testdata\chen20181123_data.mdf ,
size=10MB,
filegrowth=1MB)
log
on
(name=chen_log,
filename= D:\hrtest\DB\testdata\chen20181123_log.ldf ,
size=1MB,
filegrowth=10MB);
— 创建测试数据
use
chen20181123
create
table
t1(id
int,a
varchar(100));
insert
into
values(1, a
insert
into
values(2, b
insert
into
values(3, c
— 数据库全备
BACKUP
DATABASE
chen20181123
TO
DISK= D:\hrtest\DB\testdata\backup\chen20181123_full.bak
COMPRESSION
GO
insert
into
values(4, d
insert
into
values(5, e
— 数据库差异备份
BACKUP
DATABASE
chen20181123
TO
DISK= D:\hrtest\DB\testdata\backup\chen20181123_1.bak
COMPRESSION,DIFFERENTIAL;
GO
insert
into
values(7, f
insert
into
values(8, g
— 数据库日志备份
BACKUP
chen20181123
DISK= D:\hrtest\DB\testdata\backup\chen20181123_2.trn
WITH
COMPRESSION;
insert
into
values(9, f
insert
into
values(10, g
—19:51
delete
t1;
恢复场景
— 恢复全备 + 差异备份 恢复
restore
filelistonly
disk= D:\hrtest\DB\testdata\backup\chen20181123_full.bak
RESTORE
DATABASE
chen20181123_1
DISK
D:\hrtest\DB\testdata\backup\chen20181123_full.bak
NORECOVERY,
chen_data
D:\hrtest\DB\testdata\chen20181123_1_data.mdf ,
chen_log
D:\hrtest\DB\testdata\chen20181123_1_log.ldf
RESTORE
DATABASE
chen20181123_1
disk= D:\hrtest\DB\testdata\backup\chen20181123_1.bak
RECOVERY;
select
from
chen20181123_1.dbo.t1;
—5
— 恢复全备 + 差异备份 + 日志备份 恢复
USE
MASTER
—drop database chen20181123_2;
RESTORE
DATABASE
chen20181123_2
DISK
D:\hrtest\DB\testdata\backup\chen20181123_full.bak
NORECOVERY,
chen_data
D:\hrtest\DB\testdata\chen20181123_2_data.mdf ,
chen_log
D:\hrtest\DB\testdata\chen20181123_2_log.ldf
RESTORE
DATABASE
chen20181123_2
disk= D:\hrtest\DB\testdata\backup\chen20181123_1.bak
NORECOVERY;
RESTORE
chen20181123_2
disk= D:\hrtest\DB\testdata\backup\chen20181123_2.trn
RECOVERY;
select
from
chen20181123_2.dbo.t1;
—7
—20:33
BACKUP
chen20181123
DISK= D:\hrtest\DB\testdata\backup\chen20181123_3.trn
WITH
COMPRESSION;
— 恢复全备 + 差异备份 + 日志备份 + 新日志备份 恢复
USE
MASTER
RESTORE
DATABASE
chen20181123_3
DISK
D:\hrtest\DB\testdata\backup\chen20181123_full.bak
NORECOVERY,
chen_data
D:\hrtest\DB\testdata\chen20181123_3_data.mdf ,
chen_log
D:\hrtest\DB\testdata\chen20181123_3_log.ldf
RESTORE
DATABASE
chen20181123_3
disk= D:\hrtest\DB\testdata\backup\chen20181123_1.bak
NORECOVERY;
RESTORE
chen20181123_3
disk= D:\hrtest\DB\testdata\backup\chen20181123_2.trn
NORECOVERY;
RESTORE
chen20181123_3
disk= D:\hrtest\DB\testdata\backup\chen20181123_3.trn
RECOVERY;
select
from
chen20181123_3.dbo.t1;
—0
— 恢复全备 + 差异备份 + 日志备份 + 新日志备份 + 基于时间点不完全恢复
USE
MASTER
—drop database chen20181123_5;
RESTORE
DATABASE
chen20181123_5
DISK
D:\hrtest\DB\testdata\backup\chen20181123_full.bak
NORECOVERY,
chen_data
D:\hrtest\DB\testdata\chen20181123_5_data.mdf ,
chen_log
D:\hrtest\DB\testdata\chen20181123_5_log.ldf
RESTORE
DATABASE
chen20181123_5
disk= D:\hrtest\DB\testdata\backup\chen20181123_1.bak
NORECOVERY;
RESTORE
chen20181123_5
disk= D:\hrtest\DB\testdata\backup\chen20181123_2.trn
NORECOVERY;
RESTORE
chen20181123_5
disk= D:\hrtest\DB\testdata\backup\chen20181123_3.trn
RECOVERY,STOPAT= 2018-11-23 19:50:00
select
from
chen20181123_5.dbo.t1;
—9
“SqlServer 备份和恢复的方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
