[스크립트] TRN 복원 쿼리 생성 스크립트

아래 케이스에서 사용하기 위해 작성하였습니다
 - 이관등 짧은 다운타임으로 최대한 빠르게 복원이 필요
 - 다수의 TRN을 수동으로 진행하면서 발생할 오류를 최소화

 

설명

• Full 백업본에 대한 with Norecovery 상태의 DB에 아래 스크립트 생성 결과로 TRN 복원 진행

• Full 백업본은 아래 URL의 스크립트에서 recovery --> norecovery로 수정하여 활용 가능

  • https://sqlseverjoa.tistory.com/13

쿼리문

• 입력 파라미터 (주석 처리된 부분 )

  • #DBList / @TrnPath_Ori / @TrnPath_New/ @restoredatabasename

--by jyoh 
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

DECLARE @restoreDatabaseName  varchar(1000)

if OBJECT_ID('tempdb..#TRRestore') is not null
	drop table #TRRestore
CREATE TABLE #TRRestore(
database_name nvarchar(100),
backup_set_id int,
TRQuery nvarchar(500)
)
if OBJECT_ID('tempdb..#DBList') is not null
	drop table #DBList
CREATE TABLE #DBList(
num int identity(1,1),
name nvarchar(100)
)

--TRN 복원 스크립트 생성 대상 DB명
INSERT INTO #DBList VALUES('DB_1'); 
INSERT INTO #DBList VALUES('DB_2');
INSERT INTO #DBList VALUES('DB_3');
INSERT INTO #DBList VALUES('DB_4');
INSERT INTO #DBList VALUES('DB_5');
INSERT INTO #DBList VALUES('DB_6'); 
 

--기존 Path와 신규 Path 지정
DECLARE @TrnPath_Ori VARCHAR(1000) = 'E:\ASIS\Log\';
DECLARE @TrnPath_New VARCHAR(1000) = 'D:\TOBE\TRN\';
 

DECLARE @INDEX INT,@databaseName VARCHAR(100);
SET @INDEX = 0;  

DECLARE CUR CURSOR FOR    
SELECT NAME FROM #DBList
OPEN CUR      
FETCH NEXT FROM CUR INTO @databaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @INDEX = @INDEX + 1;  
----------------------------------------------------------------- 


--복원 DB명 ex : DB_1_test --> @databaseName +'_test'
SET @restoreDatabaseName = @databaseName ;
 
 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999


insert into #TRRestore (database_name,backup_set_id,TRQuery)
select  *
from
	(
	SELECT @databaseName as name,backup_set_id, 'RESTORE LOG ' + @restoreDatabaseName + ' FROM DISK = ''' 
			+ 
			case 
			when CHARINDEX(@TrnPath_Ori, mf.physical_device_name) <> 0   then
			STUFF (mf.physical_device_name, 1,LEN(@TrnPath_Ori),@TrnPath_New)
			else  mf.physical_device_name +'[ERROR_PATH]' end
			 + ''' WITH NORECOVERY' as TRQuery
	FROM    msdb.dbo.backupset b,
			   msdb.dbo.backupmediafamily mf
	WHERE    b.media_set_id = mf.media_set_id
			   AND b.database_name = @databaseName
			  AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
			  AND b.type = 'L'
	UNION
	SELECT @databaseName as name,999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY' as TRQuery
	) as T


 
---------------------------------------------------------------------
FETCH NEXT FROM CUR INTO @databaseName 
END
 
CLOSE CUR
DEALLOCATE CUR

select 'NEED TO CHECK',* from #TRRestore
where TRQuery like '%[ERROR] WITH NORECOVERY' or TRQuery like '%ERROR_PATH%'
ORDER BY database_name, backup_set_id

select * from #TRRestore
ORDER BY database_name, backup_set_id