[SSIS] DTS Log Shipping Script

SSIS를 이용한 로그쉬핑 Script 입니다.

/*

        LogShipping Trans Status Table

               - 20110804 by Koon

*/

create table LS_TRANS

(

        Log_idx int not null identity(1,1),

        LS_idx int not null,

        DBName varchar(50) not null,

        BackupFileName varchar(100) not null,

        FTPTransYN char(1) not null default ('N'),

        RestoreYN char(1) not null default ('N'),

        UpdDT datetime not null default (getdate()),

        RegDT datetime not null default (getdate())

)

 

alter table LS_TRANS add constraint PK_LS_TRANS primary key (Log_idx)

create index IX_LS_TRANS on LS_TRANS (LS_idx)

create index IX_LS_TRANS_DBName on LS_TRANS (DBName)

 

/*

        LogShipping Master Config Table

               - 20110804 by Koon

*/

create table LS_Config

(

        LS_idx int not null identity(1,1),

        DBName varchar(100) not null,

        SRC_ADDR varchar(100) not null,

        TAR_ADDR varchar(100) not null,

        SRC_Folder varchar(300) not null,

        TAR_Folder varchar(300) not null,

        LS_SRVYN char(1) not null default ('Y'),

        LS_Note varchar(1000)

)

 

alter table LS_Config add constraint PK_LS_Config primary key (LS_idx)

create index IX_LS_Config on LS_Config (DBName)

 

 

 

/*

        로그쉬핑- 백업

               20110804 by Koon

*/

create proc dbo.LS_Sync_BackupLog

        @ls_idx int

as

 

set nocount on

 

declare @logFileName varchar(100);

declare @TransFileName varchar(100);

declare @DBName varchar(100)

declare @SRC_Folder varchar(100)

 

select @DBName = DBName, @SRC_Folder = SRC_Folder from FTP_TEST.dbo.LS_Config where ls_idx = @ls_idx

 

set @logFileName = @SRC_Folder + '\' + @DBName + '_' + CONVERT(varchar(8), getdate(), 112) + replace(CONVERT(varchar(12), getdate(), 114), ':', '') + '.trn';

set @TransFileName = @DBName + '_' + CONVERT(varchar(8), getdate(), 112) + replace(CONVERT(varchar(12), getdate(), 114), ':', '') + '.trn';

 

Backup Log @DBName to disk = @logFileName with compression;

 

if @@ERROR = 0

        insert into FTP_TEST.dbo.LS_TRANS (LS_idx, DBName, BackupFileName) values (@ls_idx, @DBName, @TransFileName)

 

/*

        FTP전송을해야하는파일리스트조회

               - 20110804 by Koon

*/

create proc dbo.LS_Sync_GetTransFileList

        @ls_idx int

as

 

set nocount on

 

select a.log_idx, (replace(b.SRC_Folder, '\', '\\') + '\\' + a.BackupFileName) as  BackupFileName

        from FTP_TEST.dbo.LS_TRANS a with (nolock)

               inner join FTP_TEST.dbo.LS_Config b with (nolock) on a.ls_idx = b.ls_idx

where b.ls_idx = @ls_idx and FTPTransYN = 'N'

order by a.Log_idx

 

/*

        Restore를해야하는파일리스트조회

               - 20110804 by Koon

*/

create proc dbo.LS_Sync_GetRestoreFileList

        @ls_idx int

as

 

set nocount on

 

select a.log_idx, a.DBName, (b.TAR_Folder + '\' + a.BackupFileName) as  BackupFileName, (b.TAR_Folder + '\') as RestoreFolder

        from FTP_TEST.dbo.LS_TRANS a with (nolock)

               inner join FTP_TEST.dbo.LS_Config b with (nolock) on a.ls_idx = b.ls_idx

where b.ls_idx = @ls_idx and FTPTransYN = 'Y' and RestoreYN = 'N'

order by a.log_idx