【Sqlserver】基于备份和日志备份的还原测试

--------------------step1. create database & table 
USE master;

-- Drop database
IF DB_ID('TestBakDB') IS NOT NULL DROP DATABASE TestBakDB;

-- If database could not be created due to open connections, abort
IF @@ERROR = 3702 
   RAISERROR('Database cannot be dropped because there are still open connections.', 127, 127) WITH NOWAIT, LOG;

-- Create database
CREATE DATABASE TestBakDB;
GO

USE TestBakDB;
GO
if(object_id('bak_version','U') is not null ) drop table bak_version;
create table bak_version(
id bigint not null identity(1,1) constraint PK_bak_version primary key ,
val varchar(MAX),
dt datetime2 default (current_timestamp)
);

insert into bak_version(val) values('basic version');

select * from bak_version;

--------------------step2.Full bakup
declare @willBackDB nvarchar(100)=N'TestBakDB';/*Define*/
---声明变量 
declare
@disk_path nvarchar(MAX)=N'N''C:\\DataBak\Full\'/*Edit path*/+@willBackDB+'_backup_'+CONVERT(VARCHAR(10),GETDATE(),120)+'.bak''',
@backup_name nvarchar(MAX)=N'N'''+@willBackDB+'_backup_'+CONVERT(VARCHAR(10),GETDATE(),120)+'''',
@execStr nvarchar(MAX)='';
/*---清理连接
DECLARE @kid varchar(100) 
SET @kid='' 
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses 
WHERE dbid=DB_ID(@willBackDB) 
PRINT @kid 
EXEC(@kid);
*/
---备份数据库
set @execStr=
'BACKUP DATABASE ['+@willBackDB+'] TO 
DISK = '+@disk_path+' WITH NOFORMAT, NOINIT, NAME = '+@backup_name+', SKIP, REWIND, NOUNLOAD, 
COMPRESSION/*压缩选项*/, STATS = 10';
exec (@execStr);

---------验证备份完整性
declare @willBackDB nvarchar(100)=N'TestBakDB';/*Define*/
select backup_set_id,database_name,name,database_creation_date,backup_start_date,backup_finish_date
from msdb..backupset
where database_name = @willBackDB and backup_set_id = 
 (select max(backup_set_id) from msdb..backupset where database_name = @willBackDB)

--------------------step3.Log BackUp
insert into bak_version(val) values('version 1');
select * from bak_version;

DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('TestBakDB'))
IF (@preferredReplica = 1)
BEGIN
 BACKUP LOG [TestBakDB] TO DISK = N'C:\DataBak\Log\TestBakDB_backup_2017_08_03_140758_5520201.trn' WITH NOFORMAT, NOINIT, NAME = N'TestBakDB_backup_2017_08_03_140758_5520201', SKIP, REWIND, NOUNLOAD, STATS = 10
END
GO

insert into bak_version(val) values('version 2');
select * from bak_version;

DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('TestBakDB'))
IF (@preferredReplica = 1)
BEGIN
 BACKUP LOG [TestBakDB] TO DISK = N'C:\DataBak\Log\TestBakDB_backup_2017_08_03_140758_5520201_2.trn' WITH NOFORMAT, NOINIT, NAME = N'TestBakDB_backup_2017_08_03_140758_5520201', SKIP, REWIND, NOUNLOAD, STATS = 10
END
GO

/*
日志备份：上次完整备份或事务日志备份后数据库的所有备份

还原一个数据库=从上次完整备份到最近一次日志备份的所有日志
*/