USE master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_load_shipped_logs'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_load_shipped_logs
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_load_shipped_logs'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_load_shipped_logs >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_load_shipped_logs >>>'
END
go
CREATE PROCEDURE sp_load_shipped_logs ( 
        @PlanName varchar( 255 ) , 
        @RestoreMode varchar( 10 ) = 'NORECOVERY' , 
        @Debug bit = 0 , 
        @JustCheck bit = 0 
) 
--WITH ENCRYPTION 
AS 
/*********************************************************************************/ 
/* Created By  : Umachandar Jayachandran (UC)                                    */ 
/* Created On  : 06 September 2000                                               */ 
/* Description : This stored procedure can be used to load shipped logs from a   */ 
/*               source. This SP uses the log shipping tables for processing.    */ 
/*               This is mainly in place of the load job of log shipping & to    */ 
/*               restore logs in NORECOVERY mode. And this SP demonstrates some  */
/*               powerful programming techniques.                                */
/*********************************************************************************/ 
/* Resources   : https://umachandar.com/resources.htm                         */ 
/*********************************************************************************/ 
CREATE TABLE #TRN_Files( 
[FileName] varchar( 255 ) NULL , 
FileTimeStamp AS ( CAST( STUFF( STUFF( SUBSTRING( [FileName] , 
                        PATINDEX('%_' + REPLICATE( '[0-9]' , 12 ) + '%' , [FileName] ) + 1 , 
                                PATINDEX( '%.TRN' , [FileName] ) - 
                                        PATINDEX('%_' + REPLICATE( '[0-9]' + '%' , 12 ), 
                                [FileName] ) - 1 ) , 
                                9 , 0 , SPACE( 1 ) ) ,
                                12 , 0 , ':' ) AS datetime ) ) 
); 

DECLARE @PlanID uniqueidentifier , @DestDir nvarchar( 512 ) , 
        @SourceDB nvarchar( 128 ) , @DestDb nvarchar( 128 ) , @RetentionPeriod int , 
        @LastFileLoaded varchar( 255 ) , @CmdStr varchar( 8000 ) , @Deleted bit , 
        @FileName varchar( 255 ) , @LoadFile varchar( 255 ) , @StandbyFile varchar( 255 ); 

IF COALESCE( UPPER( @RestoreMode ) , 'NORECOVERY' ) NOT IN ( 'NORECOVERY' , 'STANDBY' ) 
BEGIN 
        RAISERROR( 'Invalid restore option was specified!', -1 , 12 ); 
        RETURN( -1 ); 
END; 

SELECT @PlanID = plan_id , @DestDir = destination_dir 
FROM msdb..backup_movement_plans 
WHERE plan_name = @PlanName; 

IF @Debug = 1 
BEGIN 
        PRINT 'PlanID :'; 
        PRINT @PlanID; 
        PRINT REPLICATE( '-' , 80 ); 
END; 

SELECT @SourceDB = source_database , @DestDB = destination_database , 
       @RetentionPeriod = retention_period , 
       @LastFileLoaded = REVERSE( LEFT( REVERSE( last_file_loaded ) , 
       CHARINDEX( '\' , REVERSE( last_file_loaded ) ) - 1 ) ) 
FROM msdb..backup_movement_plan_databases 
WHERE plan_id = @PlanID; 

IF @Debug = 1 PRINT 'Destination Database : ' + @DestDB + REPLICATE( CHAR( 13 ) + CHAR( 10 ) , 2 ); 
IF @Debug = 1 PRINT 'Last Loaded File : ' + @LastFileLoaded + REPLICATE( CHAR( 13 ) + CHAR( 10 ) , 2 ); 

SET @CmdStr = 'DIR/B ' + @DestDir + '\' + @SourceDB + '*.TRN'; 

IF @Debug = 1 PRINT 'Directory Command String : ' + @CmdStr + REPLICATE( CHAR( 13 ) + CHAR( 10 ) , 2 ); 

INSERT INTO #TRN_Files ( [FileName] ) EXEC master..xp_cmdshell @CmdStr; 

IF @Debug = 1 
PRINT 'Log Backups to be deleted :' + CHAR( 13 ) + CHAR( 10 ) + REPLICATE( '-' , 25 ); 

-- Files to be deleted: 
DECLARE delfiles CURSOR FAST_FORWARD FOR 
SELECT [FileName] FROM #TRN_Files 
WHERE DATEDIFF( hh , FileTimeStamp , CURRENT_TIMESTAMP ) > @RetentionPeriod 
ORDER BY [FileName]; 
OPEN delfiles; 

WHILE( 'FETCH IS OK' = 'FETCH IS OK' ) 
BEGIN 
        FETCH delfiles INTO @FileName; 
        IF @@FETCH_STATUS < 0 BREAK; 
        
        SELECT @CmdStr = 'DEL ' + @DestDir + '\' + @FileName , @Deleted = 1; 
        IF @Debug = 1 PRINT @CmdStr; 
        IF @JustCheck = 0 EXEC master..xp_cmdshell @CmdStr , no_output; 
END; 
DEALLOCATE delfiles; 

IF @Debug = 1 
BEGIN 
        IF COALESCE( @Deleted , 0 ) = 0 PRINT '(No files)' 
        PRINT REPLICATE( '-' , 80 ) + REPLICATE( CHAR( 13 ) + CHAR( 10 ) , 2 ) + 
        'Log Backups to be loaded :' + CHAR( 13 ) + CHAR( 10 ) + REPLICATE( '-' , 24 ); 
END; 

-- Files to be loaded: 
DECLARE loadfiles CURSOR FAST_FORWARD FOR 
SELECT [FileName] FROM #TRN_Files 
WHERE [FileName] > @LastFileLoaded 
ORDER BY [FileName]; 
OPEN loadfiles; 

WHILE( 'FETCH IS OK' = 'FETCH IS OK' ) 
BEGIN 
        FETCH loadfiles INTO @FileName; 
        IF @@FETCH_STATUS < 0 BREAK; 
        
        SET @LoadFile = @DestDir + '\' + @FileName ; 
        SELECT @StandbyFile = @DestDir + '\' + @DestDB + '.REDO' 
        WHERE UPPER( @RestoreMode ) = 'STANDBY'; 
        
        PRINT @LoadFile + COALESCE( ' | ' + @StandbyFile , '' ); 

        IF UPPER( @RestoreMode ) = 'NORECOVERY' And @JustCheck = 0 
                RESTORE LOG @DestDB 
                FROM DISK = @LoadFile 
                WITH NORECOVERY; 
        ELSE IF UPPER( @RestoreMode ) = 'STANDBY' And @JustCheck = 0 
                RESTORE LOG @DestDB 
                FROM DISK = @LoadFile 
                WITH STANDBY = @StandbyFile; 
        
        IF @@ERROR = 0 And @JustCheck = 0 
                UPDATE msdb..backup_movement_plan_databases 
                SET last_file_loaded = @LoadFile , 
                    date_last_loaded = CURRENT_TIMESTAMP 
                WHERE plan_id = @PlanID; 
END; 
DEALLOCATE loadfiles; 
IF @Debug = 1 PRINT REPLICATE( '-' , 80 ); 
RETURN( 0 ); 
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_load_shipped_logs'), 'IsProcedure') = 1
BEGIN
     GRANT EXECUTE ON dbo.sp_load_shipped_logs To Public
     PRINT '<<< CREATED PROCEDURE dbo.sp_load_shipped_logs >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_load_shipped_logs >>>'
go

This page was last updated on May 01, 2006 04:28 PM.