SqlServer備份和恢復(二)

 

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"，共同學習，共同成長！！！