【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

/*
 ： 

 = 
*/