/*
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
'Brain Trainning > DataBase' 카테고리의 다른 글
[MSSQL] Replication Row 에러 대처법 (0) | 2011.08.05 |
---|---|
[MSSQL] SSIS Community Tasks and Components (0) | 2011.08.05 |
[MSSQL] Log Shpping Error Message ... LS Alert (0) | 2011.08.03 |
[MSSQL] 2008 설치 중 무한 리붓하라는 말이 나오면... (0) | 2011.08.02 |
[SSIS] Template 용 DB Script (0) | 2011.07.24 |