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"，共同学习，共同成长！！！