SqlServer备份和恢复的方法

本篇内容介绍了“SqlServer备份和恢复的方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！SqlServer备份和恢复备份—创建测试数据库chen20181123create
database
chen20181123on(name=chen_data,filename=’D:hrtestDBtestdatachen20181123_data.mdf’,size=10MB,filegrowth=1MB)log
on(name=chen_log,filename=’D:hrtestDBtestdatachen20181123_log.ldf’,size=1MB,filegrowth=10MB);—创建测试数据use
chen20181123create
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
chen20181123TO
DISK=’D:hrtestDBtestdatabackupchen20181123_full.bak’
WITH
COMPRESSIONGOinsert
into
t1
values(4,’d’);insert
into
t1
values(5,’e’);—数据库差异备份BACKUP
DATABASE
chen20181123TO
DISK=’D:hrtestDBtestdatabackupchen20181123_1.bak’
WITH
COMPRESSION,DIFFERENTIAL;GOinsert
into
t1
values(7,’f’);insert
into
t1
values(8,’g’);—数据库日志备份BACKUP
LOG
chen20181123
TO
DISK=’D:hrtestDBtestdatabackupchen20181123_2.trn’
WITH
COMPRESSION;insert
into
t1
values(9,’f’);insert
into
t1
values(10,’g’);—19:51delete
t1;恢复场景
—恢复全备+差异备份 恢复restore
filelistonly
from
disk=’D:hrtestDBtestdatabackupchen20181123_full.bak’;RESTORE
DATABASE
chen20181123_1
FROM
DISK
=
‘D:hrtestDBtestdatabackupchen20181123_full.bak’
WITH
NORECOVERY,
MOVE
‘chen_data’
TO
‘D:hrtestDBtestdatachen20181123_1_data.mdf’,
MOVE
‘chen_log’
TO
‘D:hrtestDBtestdatachen20181123_1_log.ldf’;RESTORE
DATABASE
chen20181123_1
from
disk=’D:hrtestDBtestdatabackupchen20181123_1.bak’
WITH
RECOVERY;select
*
from
chen20181123_1.dbo.t1;
—5—恢复全备+差异备份+日志备份 恢复USE
MASTER—drop database chen20181123_2;RESTORE
DATABASE
chen20181123_2
FROM
DISK
=
‘D:hrtestDBtestdatabackupchen20181123_full.bak’
WITH
NORECOVERY,
MOVE
‘chen_data’
TO
‘D:hrtestDBtestdatachen20181123_2_data.mdf’,
MOVE
‘chen_log’
TO
‘D:hrtestDBtestdatachen20181123_2_log.ldf’;RESTORE
DATABASE
chen20181123_2
from
disk=’D:hrtestDBtestdatabackupchen20181123_1.bak’
WITH
NORECOVERY;RESTORE
LOG
chen20181123_2
from
disk=’D:hrtestDBtestdatabackupchen20181123_2.trn’
WITH
RECOVERY;select
*
from
chen20181123_2.dbo.t1;
—7—20:33BACKUP
LOG
免费云主机域名
chen20181123
TO
DISK=’D:hrtestDBtestdatabackupchen20181123_3.trn’
WITH
COMPRESSION;—恢复全备+差异备份+日志备份+新日志备份 恢复USE
MASTERRESTORE
DATABASE
chen20181123_3
FROM
DISK
=
‘D:hrtestDBtestdatabackupchen20181123_full.bak’
WITH
NORECOVERY,
MOVE
‘chen_data’
TO
‘D:hrtestDBtestdatachen20181123_3_data.mdf’,
MOVE
‘chen_log’
TO
‘D:hrtestDBtestdatachen20181123_3_log.ldf’;RESTORE
DATABASE
chen20181123_3
from
disk=’D:hrtestDBtestdatabackupchen20181123_1.bak’
WITH
NORECOVERY;RESTORE
LOG
chen20181123_3
from
disk=’D:hrtestDBtestdatabackupchen20181123_2.trn’
WITH
NORECOVERY;RESTORE
LOG
chen20181123_3
from
disk=’D:hrtestDBtestdatabackupchen20181123_3.trn’
WITH
RECOVERY;select
*
from
chen20181123_3.dbo.t1;
—0—恢复全备+差异备份+日志备份+新日志备份+基于时间点不完全恢复USE
MASTER—drop database chen20181123_5;RESTORE
DATABASE
chen20181123_5
FROM
DISK
=
‘D:hrtestDBtestdatabackupchen20181123_full.bak’
WITH
NORECOVERY,
MOVE
‘chen_data’
TO
‘D:hrtestDBtestdatachen20181123_5_data.mdf’,
MOVE
‘chen_log’
TO
‘D:hrtestDBtestdatachen20181123_5_log.ldf’;RESTORE
DATABASE
chen20181123_5
from
disk=’D:hrtestDBtestdatabackupchen20181123_1.bak’
WITH
NORECOVERY;RESTORE
LOG
chen20181123_5
from
disk=’D:hrtestDBtestdatabackupchen20181123_2.trn’
WITH
NORECOVERY;RESTORE
LOG
chen20181123_5
from
disk=’D:hrtestDBtestdatabackupchen20181123_3.trn’
WITH
RECOVERY,STOPAT=’2018-11-23 19:50:00′;select
*
from
chen20181123_5.dbo.t1;
—9“SqlServer备份和恢复的方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注百云网站，小编将为大家输出更多高质量的实用文章！

相关推荐: [AlwaysOn] 创建SQL Server高可用性组T-SQL语法：REPLICA ON子句

REPLICA ON 指定从1到5个SQL Server实例来承载新可用性组中的可用性副本。Specifies from one to five SQL server instances to host availability replicas in the…