Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 BU same log to 2 locations

Author  Topic 

verinow
Starting Member

5 Posts

Posted - 2006-12-15 : 11:13:04
I do full backups nightly and log backups every 15 minutes. The files are stored on a seperated computer as opposed to the SQL-Server storage device. I want to BU to a second computer as well (soon to be off-site). As I understand it, I can have two jobs for the full BU. One job backups up to computer A and the other job backups the identical file to computer B. I don't think I can do the same with the transaction logs since once I back them up, the transactions are flagged or whatever as being backed up so the backup file for the second job would get an empty file.
How can I copy the same log backup to two locations?
Any ideas would be appreciated.
TIA,
Mark

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 12:05:32
Keep your backups locally, backing up across the network isn't a great idea. Create a second job to copy/ftp/etc... the backupfiles to the other locations.
Go to Top of Page

verinow
Starting Member

5 Posts

Posted - 2006-12-15 : 15:58:12
Forget about off-site. I just want want to backup to two different computers.
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-18 : 08:34:58
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 Master
GO

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_CopyTranLogFile')
DROP PROCEDURE dbo.sp_CopyTranLogFile
go

CREATE PROCEDURE dbo.sp_CopyTranLogFile ( @Time DateTime
, @BackupDirectory Varchar(255)
, @BackupFileName Varchar(255)
, @DestinationDir Varchar(255)
, @debug = 0 ) AS
BEGIN

/****************************************************************************
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 1

END /******* END OF PROCEDURE sp_CopyTranLogFile ***************/

GO

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'sp_CopyTranLogFile')
GRANT EXECUTE ON dbo.sp_CopyTranLogFile TO Public
go
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 DateTime

SELECT @Time = GetDate()

-- Code for Transaction Log Filename here Need Date and Time for filename

BACKUP LOG Pubs TO DISK=@filename WITH INIT

-- Copy the log file

exec sysadmindbo.dbo.sys_CopyTranLogFile @time, 'Yada' , 'Yada' , 'Yada'


--
Regards
Tony The DBA
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-18 : 13:22:38
this is possible in SQL 2005 using the 'MIRROR TO' option of the standard BACKUP command. Mirroring the backups like this is done in parallel.

in SQL 2000, you would have to back the file up to a single location and then programatically move it to your other locations. These operations would be done in serial, one after another.



-ec
Go to Top of Page
   

- Advertisement -