20181123-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"