admin.003.ap_TranMiLogBackup 분단위 트랜잭션 백업

---------------------------------------------------------------------------------
-- 분단위  트랜잭션 백업 로그
---------------------------------------------------------------------------------
-- 작성자 : 차주언
-- 수정자 : 추숙
-- 마지막 수정 내용
--  1. 트랜잭션 파일 시간을 조정 가능하도록 변경
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_TranMiLogBackup') 
 BEGIN
  DROP  PROCEDURE ap_TranMiLogBackup
 END
GO
CREATE PROC [dbo].[ap_TranMiLogBackup]
 @DBNAME varchar(20),
 @physical_path varchar(100) = 'c:\Backup\TBackup\',
 @FullBackupHour TINYINT = 6 , -- full backup 시간 
 @DelLogHour INT = 48   -- (시간) 삭제할 이전의 DEVICE 생성일 비교
AS
 SET NOCOUNT ON
 
 -- 변수 선언
 DECLARE
  @query varchar(1000),
  @cur_hour tinyint,
  @cur_min tinyint,
  @del_hour tinyint,
  @del_min tinyint,
  @cur_device varchar(50),
  @del_device varchar(50),
  @description varchar(48),
  @physical_name  varchar(64) 

 DECLARE @aCnt INT,
   @aID INT,
   @aDeviceName VARCHAR(1000)

 DECLARE @aTemp TABLE (
  mID  INT   IDENTITY(1,1),
  mDate DATETIME,
  mDeviceName VARCHAR(1000)
 )
 
 SELECT @aCnt = 0, @aID = 0, @aDeviceName = ''  

 SET @cur_hour = datepart(hour,getdate())
 SET @cur_min = datepart(mi,getdate())
 
 -- Full Backup 시간은 제외함.
 IF ( @cur_hour = @FullBackupHour )
  Return
 
 -- 삭제할 로그 정보 
 INSERT INTO @aTemp( mDate, mDeviceName )
 SELECT
  mDate,
  name
 FROM (
  SELECT
   convert( datetime, mYY + '-' + mMM + '-'+ mDD + ' ' + mHH + ':' + mMT + ':00' ) mDate,
   name  
  FROM (
   select
    SUBSTRING(RIGHT(name, 13), 1, 4) mYY, -- 년
    SUBSTRING(RIGHT(name, 13), 5, 2) mMM, -- 월
    SUBSTRING(RIGHT(name, 13), 7, 2) mDD, -- 월

    SUBSTRING(RIGHT(name, 4),1,2) mHH, -- 시
    SUBSTRING(RIGHT(name, 4),3,2) mMT, -- 분
    name
   from master.dbo.sysdevices
   where name like '%tlog%'
    and ISNUMERIC(right(name, 4)) = 1 ) T1 ) T3
 WHERE mDate <= DATEADD(HH, -@DelLogHour, GETDATE()) 

 SELECT @aCnt = @@ROWCOUNT, @aID = 1 
  
 
 --현재 Device Setup
 SET @description = 'Log Minute Backup , ' + @DBNAME  + '  ' + CONVERT(CHAR(16),GETDATE(),20)   
 SET @cur_device = @DBNAME + '_tlog_' + CONVERT(char(8),getdate(),112) + '_'   

 
 IF @cur_hour < 10
  SET @cur_device = @cur_device + '0' + convert(char(1),@cur_hour)
 ELSE
  SET @cur_device = @cur_device + convert(char(2),@cur_hour)

 IF @cur_min < 10
  SET @cur_device = @cur_device + '0' + convert(char(1),@cur_min)  
 ELSE
  SET @cur_device = @cur_device + convert(char(2),@cur_min)

 SET @physical_name = @physical_path + @cur_device + '.tbak'

 --이전의 device를 삭제한다. 
 IF ( @aCnt >= 1 )
 BEGIN  
  WHILE ( @aCnt >= @aID )
  BEGIN
   
   SELECT
    @aDeviceName = mDeviceName 
   FROM @aTemp
   WHERE  mID = @aID

   EXEC sp_dropdevice @aDeviceName,'delfile'   

   SET @aID = @aID + 1
  END      
 END

 --dump device를 설정한다.
  IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@cur_device) ) = 0  
   EXEC sp_addumpdevice 'disk' , @cur_device , @physical_name 

 --transaction log 를 백업받는다.
  SET @query = 'BACKUP LOG ' + @DBNAME + ' TO ' + @cur_device
  EXECUTE(@query)
GO

-- TEST QUERY
EXEC [ap_TranMiLogBackup] 'AdventureWorks', 'E:\DB_TEST\Backup\', DEFAULT, DEFAULT -- 48시간 이전
GO

EXEC [ap_TranMiLogBackup] 'AdventureWorks', 'E:\DB_TEST\Backup\', DEFAULT, 6 -- 6시간 이전 트랜잭션 파일 삭제
GO