SqlServer
备份和恢复
(
二
)
备份
---
创建测试数据库chen20181123
create
database
chen20181123
on
(
name
=
chen_data
,
filename
=
'D:\hrtest\DB\testdata\chen20181123_data.mdf'
,
size
=
10
MB
,
filegrowth
=
1
MB
)
log
on
(
name
=
chen_log
,
filename
=
'D:\hrtest\DB\testdata\chen20181123_log.ldf'
,
size
=
1
MB
,
filegrowth
=
10
MB
);
---
创建测试数据
use
chen20181123
create
table
t1
(
id
int
,
a
varchar
(
100
));
insert
into
t1
values
(
1
,
'a'
);
insert
into
t1
values
(
2
,
'b'
);
insert
into
t1
values
(
3
,
'c'
);
---
数据库全备
BACKUP
DATABASE
chen20181123
TO
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
WITH
COMPRESSION
GO
insert
into
t1
values
(
4
,
'd'
);
insert
into
t1
values
(
5
,
'e'
);
---
数据库差异备份
BACKUP
DATABASE
chen20181123
TO
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_1.bak'
WITH
COMPRESSION
,
DIFFERENTIAL
;
GO
insert
into
t1
values
(
7
,
'f'
);
insert
into
t1
values
(
8
,
'g'
);
---
数据库日志备份
BACKUP
LOG
chen20181123
TO
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_2.trn'
WITH
COMPRESSION
;
insert
into
t1
values
(
9
,
'f'
);
insert
into
t1
values
(
10
,
'g'
);
---19:51
delete
t1
;
恢复场景
---
恢复全备+差异备份 恢复
restore
filelistonly
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
;
RESTORE
DATABASE
chen20181123_1
FROM
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
WITH
NORECOVERY
,
MOVE
'chen_data'
TO
'D:\hrtest\DB\testdata\chen20181123_1_data.mdf'
,
MOVE
'chen_log'
TO
'D:\hrtest\DB\testdata\chen20181123_1_log.ldf'
;
RESTORE
DATABASE
chen20181123_1
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_1.bak'
WITH
RECOVERY
;
select
*
from
chen20181123_1.dbo.t1
;
---5
---
恢复全备+差异备份+日志备份 恢复
USE
MASTER
---drop database chen20181123_2;
RESTORE
DATABASE
chen20181123_2
FROM
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
WITH
NORECOVERY
,
MOVE
'chen_data'
TO
'D:\hrtest\DB\testdata\chen20181123_2_data.mdf'
,
MOVE
'chen_log'
TO
'D:\hrtest\DB\testdata\chen20181123_2_log.ldf'
;
RESTORE
DATABASE
chen20181123_2
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_1.bak'
WITH
NORECOVERY
;
RESTORE
LOG
chen20181123_2
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_2.trn'
WITH
RECOVERY
;
select
*
from
chen20181123_2.dbo.t1
;
---7
---20:33
BACKUP
LOG
chen20181123
TO
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_3.trn'
WITH
COMPRESSION
;
---
恢复全备+差异备份+日志备份+新日志备份 恢复
USE
MASTER
RESTORE
DATABASE
chen20181123_3
FROM
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
WITH
NORECOVERY
,
MOVE
'chen_data'
TO
'D:\hrtest\DB\testdata\chen20181123_3_data.mdf'
,
MOVE
'chen_log'
TO
'D:\hrtest\DB\testdata\chen20181123_3_log.ldf'
;
RESTORE
DATABASE
chen20181123_3
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_1.bak'
WITH
NORECOVERY
;
RESTORE
LOG
chen20181123_3
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_2.trn'
WITH
NORECOVERY
;
RESTORE
LOG
chen20181123_3
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_3.trn'
WITH
RECOVERY
;
select
*
from
chen20181123_3.dbo.t1
;
---0
---
恢复全备+差异备份+日志备份+新日志备份+基于时间点不完全恢复
USE
MASTER
---drop database chen20181123_5;
RESTORE
DATABASE
chen20181123_5
FROM
DISK
=
'D:\hrtest\DB\testdata\backup\chen20181123_full.bak'
WITH
NORECOVERY
,
MOVE
'chen_data'
TO
'D:\hrtest\DB\testdata\chen20181123_5_data.mdf'
,
MOVE
'chen_log'
TO
'D:\hrtest\DB\testdata\chen20181123_5_log.ldf'
;
RESTORE
DATABASE
chen20181123_5
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_1.bak'
WITH
NORECOVERY
;
RESTORE
LOG
chen20181123_5
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_2.trn'
WITH
NORECOVERY
;
RESTORE
LOG
chen20181123_5
from
disk
=
'D:\hrtest\DB\testdata\backup\chen20181123_3.trn'
WITH
RECOVERY
,
STOPAT
=
'2018-11-23 19:50:00'
;
select
*
from
chen20181123_5.dbo.t1
;
---9
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
