共計 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 小編將為大家輸出更多高質量的實用文章！
