로그전달 구축강좌 5/6 - 복원 수행 프로시저, 테이블 생성
[Section 5] 대상 서버에서 로그 복원을 수행할 테이블 및 프로시저 생성, 공유 폴더 설정
원본 서버에서 발생되는 트랜잭션 로그 파일을 저장할 공유 폴더를 설정하고, 로그 복원을 수행할 저장 프로시저를 생성합니다.
10. [대상 서버] 로그 백업 파일을 가져오기 위한 작업과 관련된 테이블과 저장 프로시저를 생성합니다. 쿼리 분석기에서 대상서버로 연결한 후, 다음 스크립트를 수행하여 테이블과 저장 프로시저를 생성합니다.
|
USE MSDB GO --로그 백업 수행 정보를 보관하는 테이블 if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TB_LOGRESTORELIST]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TB_LOGRESTORELIST] GO CREATE TABLE [dbo].[TB_LOGRESTORELIST] ( [No] [int] NOT NULL , [List] [varchar] (128) COLLATE Korean_Wansung_CI_AS NOT NULL , [RestoreYN] [char] (1) NOT NULL Default('N'), [CopyYN] [char] (1) NOT NULL Default('N'), [FileSize] [bigint] NOT NULL Default(0), [Start_Time] [datetime] NULL, [End_Time] [datetime] NULL, [Duration] AS ISNULL(DATEDIFF(SS, Start_Time, End_Time), 0) ) ON [PRIMARY] GO CREATE CLUSTERED INDEX CIX_TB_LOGRESTORELIST ON TB_LOGRESTORELIST(No) GO |
11. [대상 서버] 원본 서버의 트랜잭션 로그 파일을 가져와서 저장할 폴더를 생성하고, 이 폴더를 공유합니다. 공유 권한은 읽기 및 쓰기로 지정 되어야 합니다. 본 예제에서는 D:\LogBackup_Target 이라는 폴더를 이용하겠습니다.
12. [대상 서버] 원본 서버에 있는 로그 리스토어 정보 테이블을 읽어오기 위해 대상 서버와 원본 서버 간의 연결을 설정해 줘야 합니다. 다음과 같은 방식으로 대상 서버에서 연결을 설정합니다.
|
--대상 서버(ADC2)에서 원본 서버(ADC1)으로 Linked Server를 설정합니다 sp_addlinkedserver 'ADC1' GO sp_addlinkedsrvlogin 'ADC1', 'false', NULL, '<로그인 아이디>', '<로그인 패스워드>' GO |
13. [대상 서버] 이제 로그 백업 파일을 복사해 오는 작업을 수행할 저장 프로시저와, 로그 백업을 수행하기 전에 해당 DB의 사용자를 모두 Kill 시키는 프로시저, 로그 백업 정보를 이용하여 복원을 수행하는 프로시저를 생성하겠습니다. 쿼리 분석기를 이용하여 다음 스크립트를 실행하여 프로시저 들을 생성합니다.
|
USE MSDB GO --대상 서버(ADC2)에서 로그 리스토어를 수행하기 전에 해당DB(TestDB)를 사용하는 프로세스를 KILL하는 프로시저 if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_KillSPIDUsing_TestDB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[USP_KillSPIDUsing_TestDB] GO CREATE PROC USP_KillSPIDUsing_TestDB AS BEGIN SET NOCOUNT ON DECLARE @spid smallint, @str varchar(50) SELECT spid INTO #SPID_USING_USERDB FROM master..sysprocesses WHERE dbid=db_id('TestDB') AND cmd NOT LIKE 'RESTORE LOG%' -- Log Restore Process는제외 AND program_name NOT LIKE 'SQLAgent%' AND program_name NOT LIKE 'DTS%' -- SQLAgent 관련Process는제외 SELECT spid FROM master..sysprocesses a WHERE EXISTS (SELECT 1 FROM master..syslocks WHERE spid = a.spid AND dbid = db_id('TestDB')) AND cmd NOT LIKE 'RESTORE LOG%' AND -- Log Restore Process는제외 program_name NOT LIKE 'SQLAgent%' AND program_name NOT LIKE 'DTS%' DECLARE spid_cursor CURSOR FOR SELECT spid FROM #SPID_USING_USERDB FOR READ ONLY OPEN spid_cursor FETCH NEXT FROM spid_cursor INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN SET @str = 'kill ' + CONVERT (char(5), @spid) EXECUTE (@str) FETCH NEXT FROM spid_cursor INTO @spid END CLOSE spid_cursor DEALLOCATE spid_cursor END GO |
|
USE MSDB GO --원본서버(ADC1)에서 대상서버(ADC2)로 로그 파일을 복사하는 스크립트 if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_LOGCOPY]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[USP_LOGCOPY] GO CREATE PROCEDURE USP_LOGCOPY AS SET NOCOUNT ON DECLARE @list varchar(128), @copystr varchar(256), @no int, @maxno int --TB_LOGRESTORELIST 에서의최종No를구한다. SELECT @maxno = isnull(MAX(No), 0) FROM TB_LOGRESTORELIST WITH (NOLOCK) --ADC1 서버로부터 새로 추가된 리스트 정보를 입력 받아서 TB_LOGRESTORELIST에 저장 INSERT msdb.dbo.TB_LOGRESTORELIST(No, List) SELECT No, List FROM ADC1.msdb.dbo.TB_LOGBACKUPLIST WHERE No>@maxno DECLARE cur cursor for SELECT NO, LIST FROM TB_LOGRESTORELIST WITH (NOLOCK) WHERE CopyYN = 'N' ORDER BY NO -- logbackuplist에서 CopyYN = 'N' 인것을가져온다. OPEN cur FETCH NEXT FROM cur INTO @no, @list WHILE @@fetch_status = 0 BEGIN -- 파일을 복사하기 전 CopyYN = 'C'로 설정 UPDATE TB_LOGRESTORELIST SET CopyYN = 'C' WHERE NO = @no SET @copystr = 'COPY \\ADC1\LogBackup\' + @list +' \\ADC2\LogBackup_Target\' EXEC master..xp_cmdshell @copystr, NO_OUTPUT IF @@ERROR = 0 BEGIN UPDATE TB_LOGRESTORELIST SET CopyYN = 'Y' WHERE NO = @no --Copy 해온파일의Size를계산시작 DECLARE @tmpStr varchar(500), @tmpResultStr varchar(500) CREATE TABLE #TEMPSIZE(RESULTSTR VARCHAR(1000)) SET @tmpStr = 'dir \\ADC2\LogBackup_Target\' + @List + ' |find "' + @List + '"' INSERT #TEMPSIZE EXEC master..xp_cmdshell @tmpStr DELETE FROM #TEMPSIZE WHERE RESULTSTR IS NULL IF EXISTS (SELECT 1 FROM #TEMPSIZE) BEGIN SET @tmpResultStr = (SELECT TOP 1 RESULTSTR FROM #TEMPSIZE) SET @tmpResultStr = REPLACE(REPLACE(REPLACE(SUBSTRING(@tmpResultStr, 19, LEN(@tmpResultStr)), @List, ''), ' ',''),',','') IF ISNUMERIC(@tmpResultStr) = 1 BEGIN UPDATE TB_LOGRESTORELIST SET FileSize = CAST(@tmpResultStr AS INT) WHERE NO = @no END END DROP TABLE #TEMPSIZE --Copy 해온 파일의 Size 계산 종료 END FETCH NEXT FROM cur INTO @no, @list END CLOSE cur DEALLOCATE cur GO |
|
USE MSDB GO --대상 서버(ADC2)에서 로그 리스토어를 수행하는 프로시저 if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_LOGRESTORE]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[USP_LOGRESTORE] GO CREATE PROCEDURE USP_LOGRESTORE AS DECLARE @list varchar(128), @restorestr varchar(256), @no int, @err int , @delstr varchar(256) , @error int EXEC DBO.USP_KILLSPIDUSING_TestDB DECLARE cur cursor for SELECT NO, LIST FROM TB_LOGRESTORELIST WITH (NOLOCK) WHERE RestoreYN <>'Y' AND CopyYN = 'Y' ORDER BY NO -- logbackuplist에서 RestoreYN이 'Y'가 아니며, CopyYN = 'Y' 인 것을 가져온다. OPEN cur FETCH NEXT FROM cur INTO @no, @list WHILE @@fetch_status = 0 BEGIN UPDATE TB_LOGRESTORELIST SET restoreYN = 'C', start_time=getdate() WHERE no=@no -- 시작 시간을 업데이트 SET @restorestr = 'restore log TestDB FROM disk=''\\ADC2\LogBackup_Target\' + @list +'''' + ' with standby=''D:\DBData\TestDB_Standby.tdf''' -- Standby옵션으로 TestDB서버에있는 로그를 restore한다. EXEC (@restorestr) SET @error = @@error if @error = 0 BEGIN UPDATE TB_LOGRESTORELIST SET restoreYN = 'Y', end_time = getdate() -- 에러가 없으면 restoreY 필드를 'Y'로 채운다. WHERE no = @no --리스토어가 정상적으로 수행 되었으면 해당파일을 삭제. SET @delstr ='master..xp_cmdshell '' EXEC (@delstr) END FETCH NEXT FROM cur INTO @no, @list END CLOSE cur DEALLOCATE cur GO |
-------------------------------------------------------------
SQLLeader.com / ADConsulting / 한대성 (olaper@지메일.컴) -------------------------------------------------------------