(─━┘_└━─)/ 머리만1톤(Head1Ton)

USE MSDB

GO

--로그 백업 수행 정보를 보관하는 테이블

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TB_LOGRESTORELIST]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TB_LOGRESTORELIST]

GO

CREATE TABLE [dbo].[TB_LOGRESTORELIST] (

             [No] [int] NOT NULL ,

             [List] [varchar] (128) COLLATE Korean_Wansung_CI_AS NOT NULL ,

             [RestoreYN] [char] (1) NOT NULL Default('N'),

             [CopyYN] [char] (1) NOT NULL Default('N'),

             [FileSize] [bigint] NOT NULL Default(0),

             [Start_Time] [datetime] NULL,

             [End_Time] [datetime] NULL,

             [Duration] AS ISNULL(DATEDIFF(SS, Start_Time, End_Time), 0)

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX CIX_TB_LOGRESTORELIST ON TB_LOGRESTORELIST(No)

GO

--대상 서버(ADC2)에서 원본 서버(ADC1)으로 Linked Server를 설정합니다

sp_addlinkedserver 'ADC1'

GO

sp_addlinkedsrvlogin 'ADC1', 'false', NULL, '<로그인 아이디>', '<로그인 패스워드>'

GO

USE MSDB

GO

--대상 서버(ADC2)에서 로그 리스토어를 수행하기 전에 해당DB(TestDB)를 사용하는 프로세스를 KILL하는 프로시저

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_KillSPIDUsing_TestDB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[USP_KillSPIDUsing_TestDB]

GO

CREATE PROC USP_KillSPIDUsing_TestDB

AS     

BEGIN      

             SET NOCOUNT ON      

             DECLARE  @spid  smallint, @str  varchar(50)     

             SELECT spid INTO  #SPID_USING_USERDB      

             FROM master..sysprocesses      

             WHERE dbid=db_id('TestDB') AND cmd NOT LIKE 'RESTORE LOG%' -- Log Restore Process는제외    

             AND program_name NOT LIKE 'SQLAgent%' AND program_name NOT LIKE 'DTS%'   

               -- SQLAgent 관련Process는제외    

             UNION

             SELECT spid FROM master..sysprocesses a  

             WHERE EXISTS (SELECT 1 FROM master..syslocks

                          WHERE spid = a.spid AND dbid = db_id('TestDB')) AND

                          cmd NOT LIKE 'RESTORE LOG%' AND -- Log Restore Process는제외    

                          program_name NOT LIKE 'SQLAgent%' AND program_name NOT LIKE 'DTS%' 

             DECLARE spid_cursor CURSOR      

             FOR SELECT spid FROM #SPID_USING_USERDB FOR READ ONLY     

             OPEN spid_cursor     

             FETCH NEXT FROM spid_cursor INTO @spid     

                           WHILE @@FETCH_STATUS = 0     

                                        BEGIN     

                                                     SET @str = 'kill ' + CONVERT (char(5), @spid)      

                                                     EXECUTE (@str)     

                                                     FETCH NEXT FROM spid_cursor INTO @spid     

                                        END     

             CLOSE spid_cursor     

             DEALLOCATE spid_cursor     

             END   

GO

USE MSDB

GO

--원본서버(ADC1)에서 대상서버(ADC2)로 로그 파일을 복사하는 스크립트

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_LOGCOPY]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[USP_LOGCOPY]

GO

CREATE PROCEDURE USP_LOGCOPY AS

SET NOCOUNT ON                   

 DECLARE @list varchar(128), @copystr varchar(256), @no int, @maxno int     

 --TB_LOGRESTORELIST 에서의최종No를구한다.      

 SELECT @maxno = isnull(MAX(No), 0) FROM TB_LOGRESTORELIST WITH (NOLOCK)     

 --ADC1 서버로부터 새로 추가된 리스트 정보를 입력 받아서 TB_LOGRESTORELIST에 저장    

 INSERT msdb.dbo.TB_LOGRESTORELIST(No, List)     

 SELECT No, List FROM ADC1.msdb.dbo.TB_LOGBACKUPLIST     

 WHERE No>@maxno     

 DECLARE  cur cursor  for                 

 SELECT NO, LIST FROM TB_LOGRESTORELIST WITH (NOLOCK) WHERE CopyYN = 'N'     

 ORDER BY  NO     

 -- logbackuplist에서 CopyYN = 'N' 인것을가져온다.     

 OPEN cur                 

 FETCH  NEXT FROM cur  INTO  @no, @list          

 WHILE  @@fetch_status =  0      

 BEGIN 

 -- 파일을 복사하기 전 CopyYN = 'C'로 설정  

     UPDATE TB_LOGRESTORELIST SET CopyYN = 'C' WHERE NO = @no     

  SET @copystr = 'COPY \\ADC1\LogBackup\' + @list +' \\ADC2\LogBackup_Target\'           

     EXEC master..xp_cmdshell @copystr, NO_OUTPUT      

   IF @@ERROR = 0

     BEGIN

          UPDATE TB_LOGRESTORELIST SET CopyYN = 'Y' WHERE NO = @no     

          --Copy 해온파일의Size를계산시작

          DECLARE @tmpStr varchar(500), @tmpResultStr varchar(500)

          CREATE TABLE #TEMPSIZE(RESULTSTR VARCHAR(1000))

          SET @tmpStr = 'dir \\ADC2\LogBackup_Target\' + @List + ' |find "' + @List + '"'

          INSERT #TEMPSIZE

          EXEC master..xp_cmdshell @tmpStr

          DELETE FROM #TEMPSIZE WHERE RESULTSTR IS NULL

          IF EXISTS (SELECT 1 FROM #TEMPSIZE)

            BEGIN

              SET @tmpResultStr = (SELECT TOP 1 RESULTSTR FROM #TEMPSIZE)

               SET @tmpResultStr = REPLACE(REPLACE(REPLACE(SUBSTRING(@tmpResultStr, 19, LEN(@tmpResultStr)), @List, ''), ' ',''),',','')

              IF ISNUMERIC(@tmpResultStr) = 1

                 BEGIN

                           UPDATE TB_LOGRESTORELIST SET FileSize = CAST(@tmpResultStr AS INT) WHERE NO = @no

                 END

            END

          DROP TABLE #TEMPSIZE

          --Copy 해온 파일의 Size 계산 종료

        END

  FETCH  NEXT FROM cur  INTO  @no, @list          

 END     

 CLOSE  cur                 

 DEALLOCATE  cur       

GO

USE MSDB

GO

--대상 서버(ADC2)에서 로그 리스토어를 수행하는 프로시저

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_LOGRESTORE]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[USP_LOGRESTORE]

GO

CREATE PROCEDURE USP_LOGRESTORE AS           

DECLARE @list varchar(128), @restorestr varchar(256), @no int, @err int ,

               @delstr varchar(256)  , @error int     

 EXEC DBO.USP_KILLSPIDUSING_TestDB 

 DECLARE  cur cursor  for               

 SELECT NO, LIST FROM TB_LOGRESTORELIST WITH (NOLOCK) WHERE RestoreYN <>'Y' AND CopyYN = 'Y'    

 ORDER BY  NO   

 -- logbackuplist에서 RestoreYN이 'Y'가 아니며, CopyYN = 'Y' 인 것을 가져온다.   

 OPEN cur               

 FETCH  NEXT FROM cur  INTO  @no, @list        

 WHILE  @@fetch_status =  0    

 BEGIN                  

  UPDATE TB_LOGRESTORELIST SET restoreYN = 'C', start_time=getdate() WHERE no=@no -- 시작 시간을 업데이트  

  SET @restorestr = 'restore log TestDB FROM disk=''\\ADC2\LogBackup_Target\' + @list +''''               

    + ' with standby=''D:\DBData\TestDB_Standby.tdf''' -- Standby옵션으로 TestDB서버에있는 로그를 restore한다.               

  EXEC (@restorestr)   

  SET @error  = @@error     

  if @error = 0               

   BEGIN         

    UPDATE TB_LOGRESTORELIST   

    SET restoreYN = 'Y', end_time = getdate()  -- 에러가 없으면 restoreY 필드를 'Y'로 채운다.               

    WHERE  no = @no      

    --리스토어가 정상적으로 수행 되었으면 해당파일을 삭제.     

    SET @delstr ='master..xp_cmdshell ''del \\ADC2\LogBackup_Target\' + @list + ''''       

    EXEC (@delstr)        

   END   

  FETCH  NEXT FROM cur  INTO  @no, @list        

 END   

 CLOSE  cur               

 DEALLOCATE  cur     

GO

--복원할 로그 파일의 구성 상태를 확인합니다.

RESTORE FILELISTONLY FROM DISK='D:\Backup\TestDB_backup1.bak'

/*

LogicalName        PhysicalName      Type      FileGroupName     Size       MaxSize

--------------------------------------------------------------------------

TestDB_Data         D:\Data\TestDB_Data.mdf D           PRIMARY             31457280 35184372080640

TestDB_Log          D:\Data\TestDB_Log.ldf    L            NULL      10485760 35184372080640

*/

--TestDB_Data를 D:\DBData\TestDB_Data.mdf,

--TestDB_Log를 D:\DBData\TestDB_Log.ldf 로 위치를 이동하여 복원을 수행하며,

--대기 상태로 설정하기 위해 Standby 옵션을 사용합니다.

RESTORE DATABASE TestDB

FROM DISK='D:\Backup\TestDB_backup1.bak',

DISK='D:\Backup\TestDB_backup2.bak',

DISK='D:\Backup\TestDB_backup3.bak'

WITH

             MOVE 'TestDB_Data' TO 'D:\DBData\TestDB_Data.mdf',

             MOVE 'TestDB_Log' TO 'D:\DBData\TestDB_Log.ldf',

STANDBY='D:\DBData\TestDB_Standby.tdf'

GO

/*

1 파일에서'TestDB' 데이터베이스, 'TestDB_Data' 파일에 대해 112 페이지를 처리했습니다.

1 파일에서'TestDB' 데이터베이스, 'TestDB_Log' 파일에 대해 1페이지를 처리했습니다.

RESTORE DATABASE이(가) 113 페이지를 0.299초(3.075MB/초)만에 처리했습니다.

*/

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

------------------------------------------------------------------------------------

--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()