My problem is that although full and transaction log backups are running beautifully using backup devices, I cannot work out how to copy the log files to another server from a scheduled sql agent job. I want to use the source file and destination as specified in the backup devices (sysdevices table).
I found xp_cmdshell and knocked up the following stored procedure
< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
CREATE PROCEDURE up_CopyBackup
Copies file specified by backup device source to file specified by backup device dest
DECLARE @SourceDev varchar(400)
Declare @DestDev varchar(400)
Declare @SQLString varchar(4000)
Declare @rc int
set @rc = 0
SET NOCOUNT ON
Get source and dest backup device details
select @sourcedev = phyname from dbo.sysdevices where name = @source
select @destdev = phyname from dbo.sysdevices where name = @dest
set @SQLstring = 'copy ' + @sourcedev + ' ' + @destdev
exec @rc = xp_cmdshell @SQLString
I thought this would do it and set it up as a T_SQL jobstep, following the backup to device “Source” as
exec up_CopyBackup ‘Source’, ‘Dest’
but the copy failed to work, with the error “The system cannot find the file specified.”.
But it has just successfully backed up to it!!
I can also do a successful backup to the dest device from the job – I am temporarily doing full backups rather than tran logs to achieve the duplication.
I am executing this from the agent account (a windows account) with sql system administrator rights on both servers.
The source device is e:\dir and the dest is \\servername\e$\dir
My sql version is (from print @@version)
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I then found an activeX script to copy files. This worked beautifully when I hard coded the file names but I really want to use the lookup in sysdevices as the backup device can then only be altered in one place not lots.
1. Why didn’t the xp_cmdshell work when the backup and active X work with the same files?
2. How can I do the lookup in activeX script?
3. Can I write a parameterised stored procedure to do the job in ActiveX script and call it from T_SQL?
I don’t want replication or log shipping, just backups available on the second server.
Sorry this is so long but I wanted to explain fully.