통계용 DB (스크립트 기반 Logshipping) 구성

2021. 9. 14. 21:24

1. 라이브 DB와 독립적으로 구성 필요 (2차백업서버에서 파일을 가져와 주기적 복원 수행)
2. 가변적인 요구사항 (복원 및 조회 가능 시점/주기, 특정 시점에 특정 액션 수행 등)에 대한 맞춤형 구현 (기존 logshipping 핸들링 불가)

동작 흐름

스크립트 설명

- 추가 설명

서로 다른 도메인간 인증 필요시 아래의 스크립트 참고

read-host -assecurestring | convertfrom-securestring | out-file 경로\인증키파일명
$password = get-content 경로\인증키파일명 | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "Dst. 계정명",$password

invoke-command -computername Dst.서버명 -credential $credentials -scriptblock {get-culture}

배치명	스크립트(or SP)명	설명
(생략) Powershell robocopy 사용	-	TRN 백업 이동(2차백업서버 → 복원 DB 서버)
SQLServerRestore	D:\Restore\InsertHeaderInfo.bat	복원 대상 백업파일 체크를 위해 SQL_InsertHeaderInfo.sql → SQL_CheckCopyFile.sql 순차 호출 SQL_CheckCopyFile.sql 의 반환 결과 기반으로 5분 Delay→ Copy 여부 체크 반복
	D:\Restore\SQL_InsertHeaderInfo.sql	TRN 백업 헤더정보를 읽어 테이블 'msdb.dbo.tblBackupHeader' 인입
	D:\Restore\SQL_CheckCopyFile.sql	쿼리 수행 시점이 00:20/12:20일때, 테이블 'msdb.dbo.tblBackupHeader'에 00:00/12:00 백업본 존재 여부를 반환
	D:\Restore\RestoreTRN.bat	실제 복원을 위한 SQL_RestoreTRN.sql 호출
	D:\Restore\SQL_RestoreTRN.sql	테이블 'msdb.dbo.tblBackupHeader'의 TRN헤더 정보 기반으로 FirstLSN 기준 DB Restore with standby 스크립트 생성
	D:\Restore\RestoreCheck.bat	복원 후 정상 복원 체크를 위한 SQL_RestoreCheck.sql 호출
	D:\Restore\SQL_RestoreCheck.sql	DB배치 '[DBAs] Restore Check' 호출
[DBAs] Restore Check	msdb.dbo.DBAsP_Restore_TRN_Check	3시간 단위 복원 상태 체크 통계 쿼리가 수행될 시간대 (00:00/12:00)의 경우, 테이블 't_RestoreCheck'에 복원 완료시간 및 복원된 최종 백업파일 기록

스크립트

InsertHeaderInfo.bat

@echo off
setlocal
 
:_loop
timeout /t 300

set BackupFilePath=D:\Restore\BackupFile\Log
D:
cd %BackupFilePath%

dir /s/b D:\Restore\BackupFile\Log /on > D:\Restore\BackupFile\BackupFileList.txt
sqlcmd -S TargetServerName  -Q "truncate table msdb.dbo.tblBackupHeader"  
:: Insert Trn Header Info
for %%f in (*.trn) do (
echo %%~dpnxf >> D:\Restore\BackupFile\BackupFileList.txt
sqlcmd -S TargetServerName -v BackupFileName="%%~dpnxf" -i "D:\Restore\SQL_InsertHeaderInfo.sql" -o "D:\Restore\RestoreLog\InsertHeaderInfo\InsertHeaderInfo_%%~nf.txt"
)

cd D:\Restore\BackupFile\Log

for /f %%a in ('sqlcmd -S TargetServerName -i "D:\Restore\SQL_CheckCopyFile.sql"') do set ColumnVar=%%a 
echo %ColumnVar%

::A는 B보다 크거나 같다
if %ColumnVar% GEQ 1 goto _break
goto _loop

:_break
echo Finish

SQL_InsertHeaderInfo.sql

declare @BackupFile varchar(1000)
set @BackupFile = '$(BackupFileName)'
 
declare @sql varchar(8000)
 
 
set @sql = 'restore headeronly from disk = '''+ @BackupFile +''''
insert into msdb.dbo.tblBackupHeader
exec(@sql)

SQL_CheckCopyFile.sql

 SET NOCOUNT ON; 
declare @NowTime datetime
 
set  @NowTime = getdate() 
declare @FirstTime varchar(10)= convert ( nvarchar(10),@NowTime,120)
declare @TargetTime varchar(23) = convert ( nvarchar(13),@NowTime,120)+ ':00:00.000'

--select @FirstTime,@TargetTime,@NowTime
 
if ( @NowTime >cast(@FirstTime+ ' 00:00:00' as datetime) and @NowTime <cast(@FirstTime+ ' 00:00:00' as datetime) )
begin
	SELECT count(*)
	FROM msdb.dbo.tblBackupHeader with (nolock)
	where cast(BackupStartDate as datetime) >= cast(@FirstTime + ' 00:00:00' as datetime) 

end
else if  (@NowTime >cast(@FirstTime+ ' 12:00:00' as datetime) and @NowTime <cast(@FirstTime+ ' 15:00:00' as datetime) )
begin
	SELECT count(*)
	FROM msdb.dbo.tblBackupHeader with (nolock)
	where cast(BackupStartDate as datetime) >= cast(@FirstTime + ' 12:00:00' as datetime) 
end	 
else
begin
	select 1
end

RestoreTRN.bat

@echo off
 
sqlcmd -S TargetServerName -i "D:\Restore\SQL_RestoreCheck.sql" -o "D:\_Restore\RestoreLog\RestoreCheck\RestoreCheck_%%~nf.txt"

SQL_RestoreTRN.sql

declare @targetDB varchar(100) = 'Restore_DB'
declare @trnPath nvarchar(1000) = 'D:\Restore\BackupFile\Log\'
declare @targetLsn varchar(256)
declare @sql nvarchar(max)
 
 
select @targetLsn =max(b.first_lsn) from
msdb..restorehistory a
inner join msdb..backupset B on a.backup_set_id =b.backup_set_id
where a.destination_database_name =@targetDB


DECLARE @EXECSQL varchar(max)
SET @EXECSQL = '' 

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM master.sys.sysprocesses
WHERE dbid = DB_ID(@targetDB)

-- PRINT @EXECSQL
EXEC(@EXECSQL)
 
set @sql = 'alter database ' + @targetDB +' set single_user with rollback immediate;' +char(10)+char(13)
 
select @sql = @sql + 'restore log '+ @targetDB + ' from disk = '+''''
+ @trnPath +''+ backupname +'.trn''' + ' with standby = ''D:\Restore\BackupFile\RollbackUndo\standby.trn'''+char(10)+char(13)
FROM [msdb].[dbo].[tblBackupHeader]
where FirstLSN >@targetLsn
order by firstLSN asc
 
 
select @sql= @sql + 'alter database '+@targetDB+' set multi_user;'
exec (@sql)

RestoreCheck.bat

@echo off
 
sqlcmd -S TargetServerName -i "D:\Restore\SQL_RestoreCheck.sql" -o "D:\_Restore\RestoreLog\RestoreCheck\RestoreCheck_%%~nf.txt"

SQL_RestoreCheck.sql

USE msdb ;  
GO  
EXEC dbo.sp_start_job N'[DBAs] Restore Check' ;  
GO

[DBAsP_Restore_TRN_Check].sql

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[DBAsP_Restore_TRN_Check]    Script Date: 2021-09-01 오전 7:44:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
-- [DBAsP_Restore_TRN_Check] 'Restore_DB', 3
CREATE Procedure [dbo].[DBAsP_Restore_TRN_Check]
@targetDB varchar(100),
@Interval int
as
begin
 
declare @rstTxt nvarchar(max) = ''
,@error_no int =0,@row_count int , @error_message nvarchar(max)

declare @Interval_restore datetime,@time_last_backupfile datetime, @time_restore_backupfile datetime,
@time_restore_finished datetime,@time_execute_insert datetime, @check_table int =0

select top 1 @check_table = 1 from msdb.dbo.tblBackupHeader  
 
if (@check_table !=1)
begin	 
SET @error_message = ' [복원 OS 배치 비정상] ' + char(10)+char(13) 
SET @error_no = 1
GOTO OnError
end


set @Interval_restore = dateadd(hour,@Interval*(-1),getdate())
 
SELECT @time_last_backupfile=   convert(nvarchar(16),max(cast ([BackupFinishDate] as datetime)) ,120)
FROM [msdb].[dbo].[tblBackupHeader]
where databasename = @targetDB

select top 1  @time_restore_finished=restore_date,@time_restore_backupfile= convert(nvarchar(16),backup_finish_date ,120)
from msdb..restorehistory a
inner join msdb..backupset B on a.backup_set_id =b.backup_set_id
where destination_database_name = @targetDB
order by restore_date desc
 
 

set @rstTxt = '| DBName: '+@targetDB+ ' | Last Restore: '
+convert(nvarchar(16),@time_restore_backupfile ,120)+' | Copied Last Backup File Time  : ' + convert(nvarchar(16),@time_last_backupfile ,120)+' |'

set @time_execute_insert= cast(convert(varchar(10), getdate(),120) + ' 00:00' as datetime) 
 
  
if ((@time_last_backupfile < @Interval_restore) or (@time_restore_backupfile != @time_last_backupfile))
begin
	SET @error_message = ' [Restore 이상] ' + char(10)+char(13)+ @rstTxt
	SET @error_no = 1

	GOTO OnError
end
ELSE 
	begin 
		if (
			(@time_execute_insert <=getdate() and getdate() <dateadd(hour,3,@time_execute_insert))
			or 
			(dateadd(hour,12,@time_execute_insert) <=getdate() and getdate() <dateadd(hour,15,@time_execute_insert))
		)
		begin
			insert into Statistics_DB.dbo.t_RestoreCheck([restoreFinishTime],[fileBackupTime],[checkStatus]) values(convert(char(16),getdate() ,120),convert(char(16),@time_last_backupfile ,120),'N');
		end
	end
end
 
 
  
/*******************************************************************************
* WORK COMPLETE
********************************************************************************/
IF @@TRANCOUNT > 0 COMMIT TRAN
RETURN 0
 
/*******************************************************************************
* Error Handling
********************************************************************************/
OnError:
IF @@TRANCOUNT > 0 ROLLBACK TRAN
--PRINT @error_message
RAISERROR(@error_message, 16, 1)
RETURN (@error_no*(-1))
GO

[DBAs] Restore Check

USE [msdb]
GO

/****** Object:  Job [[DBAs] Restore Check]    Script Date: 2021-09-01 오전 7:44:46 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2021-09-01 오전 7:44:46 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs] Restore Check', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N' ', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [3 hour]    Script Date: 2021-09-01 오전 7:44:46 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3 hour', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'[DBAsP_Restore_TRN_Check] ''Restore_DB'', 3', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

tblBackupHeader

USE [master]
GO

/****** Object:  Table [dbo].[tblBackupHeader]    Script Date: 2021-06-30 오전 3:36:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblBackupHeader](
	[BackupName] [varchar](256) NULL,
	[BackupDescription] [varchar](256) NULL,
	[BackupType] [varchar](256) NULL,
	[ExpirationDate] [varchar](256) NULL,
	[Compressed] [varchar](256) NULL,
	[Position] [varchar](256) NULL,
	[DeviceType] [varchar](256) NULL,
	[UserName] [varchar](256) NULL,
	[ServerName] [varchar](256) NULL,
	[DatabaseName] [varchar](256) NULL,
	[DatabaseVersion] [varchar](256) NULL,
	[DatabaseCreationDate] [varchar](256) NULL,
	[BackupSize] [varchar](256) NULL,
	[FirstLSN] [varchar](256) NULL,
	[LastLSN] [varchar](256) NULL,
	[CheckpointLSN] [varchar](256) NULL,
	[DatabaseBackupLSN] [varchar](256) NULL,
	[BackupStartDate] [varchar](256) NULL,
	[BackupFinishDate] [varchar](256) NULL,
	[SortOrder] [varchar](256) NULL,
	[CodePage] [varchar](256) NULL,
	[UnicodeLocaleId] [varchar](256) NULL,
	[UnicodeComparisonStyle] [varchar](256) NULL,
	[CompatibilityLevel] [varchar](256) NULL,
	[SoftwareVendorId] [varchar](256) NULL,
	[SoftwareVersionMajor] [varchar](256) NULL,
	[SoftwareVersionMinor] [varchar](256) NULL,
	[SoftwareVersionBuild] [varchar](256) NULL,
	[MachineName] [varchar](256) NULL,
	[Flags] [varchar](256) NULL,
	[BindingID] [varchar](256) NULL,
	[RecoveryForkID] [varchar](256) NULL,
	[Collation] [varchar](256) NULL,
	[FamilyGUID] [varchar](256) NULL,
	[HasBulkLoggedData] [varchar](256) NULL,
	[IsSnapshot] [varchar](256) NULL,
	[IsReadOnly] [varchar](256) NULL,
	[IsSingleUser] [varchar](256) NULL,
	[HasBackupChecksums] [varchar](256) NULL,
	[IsDamaged] [varchar](256) NULL,
	[BeginsLogChain] [varchar](256) NULL,
	[HasIncompleteMetaData] [varchar](256) NULL,
	[IsForceOffline] [varchar](256) NULL,
	[IsCopyOnly] [varchar](256) NULL,
	[FirstRecoveryForkID] [varchar](256) NULL,
	[ForkPointLSN] [varchar](256) NULL,
	[RecoveryModel] [varchar](256) NULL,
	[DifferentialBaseLSN] [varchar](256) NULL,
	[DifferentialBaseGUID] [varchar](256) NULL,
	[BackupTypeDescription] [varchar](256) NULL,
	[BackupSetGUID] [varchar](256) NULL,
	[CompressedBackupSize] [varchar](256) NULL,
	[containment] [tinyint] NULL,
	[keyAlgorithm] [nvarchar](32) NULL,
	[EncryptorThumbprint] [varbinary](20) NULL,
	[EncryptorType] [nvarchar](32) NULL
) ON [PRIMARY]
GO

저작자표시 비영리 변경금지

'SQL Server > 테스트 (+스크립트)' 카테고리의 다른 글

[테스트] 백업과 LSN 관계 (backupset) (0)	2022.05.03
[테스트] Checkpoint IO제한과 Indirect /direct 비교 (0)	2022.03.15
분리보관용 서버 구성 (파티셔닝 적용 및 관리) (0)	2021.09.14
도메인 독립 AlwaysOn 구성 (AD 미조인) (0)	2021.06.27
AlwaysOn with Basic 도입 방안 (0)	2021.06.26

일상및기술기록

통계용 DB (스크립트 기반 Logshipping) 구성

동작 흐름

스크립트 설명

스크립트

'SQL Server > 테스트 (+스크립트)' 카테고리의 다른 글

+ Recent posts

티스토리툴바