本篇内容介绍了“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备份和恢复的方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
