admin.002.ap_TranLogBackup

매일 매일 차주언 과장님의 피와 땀이 되는 스크립트 공유해 드립니다^^

부족한 부분 말씀해 주시와요^^

 

목적 : 트랜잭션 백업

내용 : 시간단위 트랜잭션 백업시 이용한다.

Script

	USE master GO IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_TranLogBackup')   BEGIN   DROP  PROCEDURE ap_TranLogBackup  END GO CREATE PROCEDURE dbo.ap_TranLogBackup  @DBNAME varchar(20),  @physical_path varchar(100) = 'd:\MSSQL\Backup\FBackup\',  @FullBackupTm TINYINT = 6 -- full backup hour AS  SET NOCOUNT ON  DECLARE @query varchar(1000),    @cur_hour tinyint,    @del_hour tinyint,    @cur_device varchar(50),    @del_device varchar(50),    @description varchar(48),    @physical_name  varchar(64)      SET @cur_hour = datepart(hour,getdate())  SET @del_hour = datepart(hour,getdate())  --Full Backup 시간은 제외함.  IF @cur_hour = @FullBackupTm   Return  SET @cur_device = @DBNAME + '_tlog_' + CONVERT(char(8),getdate(),112) + '_'    --48 시간 이전의 device를 삭제한다.  SET @del_device = @DBNAME + '_tlog_' + CONVERT(char(8),dateadd(hour,-48,getdate()),112) + '_'  SET @description = 'Log Hourly Backup , ' + @DBNAME  + '  ' + CONVERT(CHAR(16),GETDATE(),20)     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 @del_hour < 10   SET @del_device = @del_device + '0' + convert(char(1),@del_hour)  ELSE   SET @del_device = @del_device + convert(char(2),@del_hour)  SET @physical_name = @physical_path + @cur_device + '.tbak'  PRINT 'cur_device : ' + @cur_device  PRINT 'del_device : ' + @del_device  PRINT 'physical_name : ' + @physical_name    IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@del_device) ) = 1   EXEC sp_dropdevice @del_device,'delfile'     IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@cur_device) ) = 0     EXEC sp_addumpdevice 'disk' , @cur_device , @physical_name   SET @query = 'BACKUP LOG ' + @DBNAME + ' TO ' + @cur_device  PRINT @query  EXECUTE(@query) GO

 

 

예제

	EXEC DBO.ap_TranLogBackup 'AdventureWorks', 'E:\DB_TEST\Backup\FBackup\'

결과

AdventureWorks_tlog_20081007_22.tbak <-- 요렇게 파일이 생겼군요^^

 

 

현재 백업된 정보 보기

select * from master.dbo.sysdevices