You can always use the xp_cmdshell extended procedure to copy the TLog Files from Server A to server B. Here is a crude Stored Procedure that I use to do a smiliar job.USE MasterGOIF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_CopyTranLogFile') DROP PROCEDURE dbo.sp_CopyTranLogFilegoCREATE PROCEDURE dbo.sp_CopyTranLogFile ( @Time DateTime , @BackupDirectory Varchar(255) , @BackupFileName Varchar(255) , @DestinationDir Varchar(255) , @debug = 0 ) ASBEGIN /**************************************************************************** Procedure Name : sp_CopyTranLogFile Procedure Desc : : Parameters : : Returns : 0 If Execution Completed Without Errors : 1 If Execution Failed Due To Errors Date Created : DD-MMM-2005 Author : <Your Name Here> - BBC Last Amended : DD-MMM-CCYY : : ****************************************************************************/ DECLARE @ProcName varchar(32) DECLARE @UserId varchar(32) Declare @BackupFile Varchar(255) , @Cmd Varchar(1024) , @Result Integer SET NOCOUNT ON SELECT @ProcName = 'sp_CopyTranLogFile' IF @UserId = Null SELECT @UserId = SUSER_SNAME ( SUSER_SID ( ) ) IF @debug = 1 Print @ProcName + ' is Executing as User : ' + SUSER_SNAME() + ' On ' + Convert(Varchar(11), @Time , 106) + ' At ' + Convert(Varchar(13), @Time , 114) IF @debug = 1 Print '' -- The Backup File Has the format Of FileRoot + Year + Month + Day + Hour + Min Of Backup SELECT @BackupFile = @BackupFileName + Convert ( Varchar(4) , year ( @Time ) ) + RIGHT ( '00' + Convert ( Varchar(2) , Month ( @Time ) ) , 2 ) + RIGHT ( '00' + Convert ( Varchar(2) , Day ( @Time ) ) , 2 ) + RIGHT ( '00' + Convert ( Varchar(2) , DatePart (hh , @Time ) ) , 2 ) + LEFT ( RIGHT ( '00' + Convert ( Varchar(2) , DatePart (mi , @Time ) ) , 2 ) , 1 ) + '*.TRN' IF @debug = 1 Print 'Backup File Name : ' + @BackupFile SELECT @Cmd = 'XCOPY ' + @BackupDirectory + @BackupFile + ' ' + @DestinationDir + ' /Y' IF @debug = 1 Print 'Command To Execute : ' + @Cmd IF @debug = 1 Print '' EXECUTE @Result = master.dbo.xp_cmdshell @Cmd , no_output If @Result != 0 RETURN @Result -- Well Thats Copied The file Now delete the file from The Backup -- Directory From Yesterday at the same time SELECT @Time = DateAdd ( dd , -1 , @Time ) SELECT @BackupFile = @BackupFileName + Convert ( Varchar(4) , year ( @Time ) ) + RIGHT ( '00' + Convert ( Varchar(2) , Month ( @Time ) ) , 2 ) + RIGHT ( '00' + Convert ( Varchar(2) , Day ( @Time ) ) , 2 ) + RIGHT ( '00' + Convert ( Varchar(2) , DatePart (hh , @Time ) ) , 2 ) + LEFT ( RIGHT ( '00' + Convert ( Varchar(2) , DatePart (mi , @Time ) ) , 2 ) , 1 ) + '*.TRN' SELECT @Cmd = 'DEL ' + @DestinationDir + @BackupFile IF @debug = 1 Print 'Command To Execute : ' + @Cmd IF @debug = 1 Print '' EXECUTE @Result = master.dbo.xp_cmdshell @Cmd , no_output IF @Result = 0 RETURN 0 ELSE RETURN 1END /******* END OF PROCEDURE sp_CopyTranLogFile ***************/GOIF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'sp_CopyTranLogFile') GRANT EXECUTE ON dbo.sp_CopyTranLogFile TO Publicgo
Needs more error handling (Checking for file existance etc), and you might want to create it in another database other than master (And use a prefix other than sp_ ). Just add another step to you TLog backup job which calls the SP, although I do it all in the same step sort of like this DECLARE @time DateTimeSELECT @Time = GetDate()-- Code for Transaction Log Filename here Need Date and Time for filenameBACKUP LOG Pubs TO DISK=@filename WITH INIT-- Copy the log fileexec sysadmindbo.dbo.sys_CopyTranLogFile @time, 'Yada' , 'Yada' , 'Yada'
-- RegardsTony The DBA