本篇内容介绍了“SqlServer备份和恢复的方法”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情...
本篇内容介绍了“SqlServer备份和恢复的方法”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
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
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
“SqlServer备份和恢复的方法”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注捷杰建站网站,小编将为大家输出更多高质量的实用文章!