[Section 2] 원본 서버에서 백업 정보를 저장할 테이블 및 프로시저 생성, 공유 폴더 설정
원본 서버에서 로그 백업을 수행할 저장 프로시저 및 로그 백업을 수행한 결과를 저장할 테이블을 생성하며, 로그 파일을 저장할 폴더를 지정하는 단계입니다.
4. [원본 서버] 원본 서버의 TestDB에 대해 로그 백업을 수행하는 저장 프로시저 및 로그 백업 수행 정보를 기록하는 테이블을 msdb에 생성합니다. 별도의 관리 DB가 있는 경우, msdb 대신 해당 관리 DB를 사용하여도 됩니다.
|
USE MSDB GO --로그 백업 수행 정보를 보관하는 테이블 if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TB_LOGBACKUPLIST]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TB_LOGBACKUPLIST] GO CREATE TABLE [dbo].[TB_LOGBACKUPLIST] ( [No] [int] IDENTITY (1, 1) NOT NULL , [List] [varchar] (128) COLLATE Korean_Wansung_CI_AS NOT NULL , [ErrorNum] [int] NOT NULL, [DelYN] [char] (1) NOT NULL DEFAULT('N') ) ON [PRIMARY] GO CREATE CLUSTERED INDEX CIX_TB_LOGBACKUPLIST ON TB_LOGBACKUPLIST(No) GO |
5. [원본 서버] 로그 파일이 저장될 폴더를 생성한 후, 대상 서버에서 해당 서버를 접근할 수 있도록 공유합니다. 이 때, 공유 권한은 가급적이면 읽기 전용으로 설정합니다. 본 예제에서는 D:\LogBackup 이라는 폴더를 만든 후, 이를 공유하도록 설정합니다
6. [원본 서버] 이제 원본 서버에서 로그 백업을 수행하도록 하는 저장 프로시저를 생성합니다. 쿼리 분석기에서 다음 스크립트를 실행시켜 저장 프로시저를 생성합니다.
|
------------------------------------------------------------------------------------ --TestDB 로그백업스크립트 ------------------------------------------------------------------------------------ USE MSDB GO --원본 서버에서 로그 백업을 수행하는 프로시저 CREATE PROCEDURE USP_TestDB_LogBackup AS BEGIN DECLARE @LogBak varchar(256), @path varchar(128), @filename varchar(60), @nowtime datetime, @InsList varchar(1000), @InsList2 varchar(128) SET @nowtime = getdate() --s + ms까지저장 SET @filename = 'TestDB_log_' + CONVERT(varchar(8),@nowtime,112) + CONVERT(varchar(2),@nowtime,108) + substring(CONVERT(varchar(5),@nowtime,108),4,2) + RIGHT('00000' + CONVERT(varchar, DATEPART ( s , getdate() ) ), 2) + RIGHT('00000' + CONVERT(varchar, DATEPART ( ms , getdate() ) ), 3) + '.Trn' SET @path = '''D:\LogBackup\' + @filename + '''' SET @LogBak = 'BACKUP LOG TestDB TO disk =' + @path --msdb.dbo.TB_LOGBACKUPLIST에 Insert SET @InsList = ' if @@error=0 BEGIN INSERT msdb.dbo.TB_LOGBACKUPLIST(List,ErrorNum) values(''' + @filename +''',@@error)' + ' end ' EXEC (@LogBak +@InsList) END GO ------------------------------------------------------------------------------------ --TestDB 로그 백업 파일 삭제 프로시져 (최근 100개의 파일만 보관) ------------------------------------------------------------------------------------ USE MSDB GO --원본 서버에서 로그백업 파일을 최근100개만 보관 및 로그가 저장되는 공간이 3 GB 이하인 경우, 30개만 보관하도록 수행 CREATE PROCEDURE USP_TestDB_LogBackup_Del AS BEGIN --정상적인경우: 디스크 공간이 충분한 상태 시작 DECLARE CUR CURSOR READ_ONLY FOR SELECT NO, LIST FROM TB_LOGBACKUPLIST (NOLOCK) WHERE DELYN = 'N' AND NO <=(SELECT MAX(NO) FROM TB_LOGBACKUPLIST (NOLOCK)) - 100 ORDER BY NO --최근 100개 이전의 리스트 목록 중, 삭제가 되지 않은 대상을 읽어옴 DECLARE @NO INT, @LIST VARCHAR(128), @DelStr VARCHAR(300), @FilePathStr VARCHAR(300) OPEN CUR FETCH NEXT FROM CUR INTO @NO, @LIST WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN DECLARE @Error INT SET @FilePathStr = 'D:\LogBackup\' + @LIST --현재 작업 할 파일이 존재하는지 확인 CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int) INSERT #FileExists EXEC master..xp_fileexist @FilePathStr IF EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1) -- 파일이 존재하는 경우 BEGIN SET @DelStr = ' EXEC @Error = master..xp_cmdshell @DelStr, NO_OUTPUT IF @Error = 0 -- 삭제 작업이 정상적으로 수행된 경우 DELYN = 'Y'로변환 UPDATE TB_LOGBACKUPLIST SET DELYN = 'Y' WHERE NO = @NO END ELSE -- 파일이 존재하지 않는 경우 (대상 서버에서 파일을 삭제해 준 경우) : DELYN = 'Y'로변환 BEGIN UPDATE TB_LOGBACKUPLIST SET DELYN = 'Y' WHERE NO = @NO END DROP TABLE #FileExists END FETCH NEXT FROM CUR INTO @NO, @LIST END CLOSE CUR DEALLOCATE CUR --정상적인 경우: 디스크 공간이 충분한 상태 종료 --디스크의 여유 공간이 3기가 이하인 경우30개 이전의 파일 삭제 작업시작 CREATE TABLE #Drives (Drive char(1), FreeSpace int) INSERT #Drives EXEC master..xp_fixeddrives IF (SELECT FreeSpace FROM #Drives WHERE Drive = 'D')<=3072 -- D 드라이브의 여유 공간이 3GB 이하인 경우 BEGIN DECLARE CUR CURSOR READ_ONLY FOR SELECT NO, LIST FROM TB_LOGBACKUPLIST (NOLOCK) WHERE DELYN = 'N' AND NO <=(SELECT MAX(NO) FROM TB_LOGBACKUPLIST (NOLOCK)) - 30 ORDER BY NO OPEN CUR FETCH NEXT FROM CUR INTO @NO, @LIST WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SET @FilePathStr = 'D:\LogBackup\' + @LIST --현재 작업할 파일이 존재하는지 확인 CREATE TABLE #FileExists1 (FileExists int, FileIsDir int, ParentDirExists int) INSERT #FileExists1 EXEC master..xp_fileexist @FilePathStr IF EXISTS (SELECT * FROM #FileExists1 WHERE FileExists = 1) --파일이 존재 하는경우 BEGIN SET @DelStr = ' EXEC @Error = master..xp_cmdshell @DelStr, NO_OUTPUT IF @Error = 0 --삭제 작업이 정상적으로 수행된 경우 DELYN = 'Y'로변환 UPDATE TB_LOGBACKUPLIST SET DELYN = 'Y' WHERE NO = @NO END ELSE --파일이 존재하지 않는 경우 (대상 서버에서 파일을 삭제해 준 경우) : DELYN = 'Y'로변환 BEGIN UPDATE TB_LOGBACKUPLIST SET DELYN = 'Y' WHERE NO = @NO END DROP TABLE #FileExists1 END FETCH NEXT FROM CUR INTO @NO, @LIST END CLOSE CUR DEALLOCATE CUR END DROP TABLE #Drives --디스크의 여유공간이 3GB 이하인 경우 30개 이전의 파일 삭제 작업종료 END GO |
-------------------------------------------------------------
SQLLeader.com / ADConsulting / 한대성 (olaper@지메일.컴) -------------------------------------------------------------
'연구개발 > SQL2005' 카테고리의 다른 글
| 로그전달 구축강좌 4/6 - 전체 백업 파일을 이용한 복원 수행 (0) | 2009.06.18 |
|---|---|
| 로그전달 구축강좌 3/6 - 로그 백업을 수행할 작업 설정 (0) | 2009.06.18 |
| 로그전달 구축강좌 1/6 - 임시 DB 생성 및 백업 (0) | 2009.06.18 |
| SP_SEARCH - 저장프로시저에서 단어찾기? (0) | 2009.06.17 |
| DML 트리거의 사용 (0) | 2009.06.15 |