March 18, 2005 at 2:56 am
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
CREATE PROCEDURE up_CopyBackup
@Source varchar(100),
@Dest varchar(100)
AS
/*
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
print @sqlstring
exec @rc = xp_cmdshell @SQLString
RETURN @rc
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.
Many thanks.
March 18, 2005 at 5:45 am
Hi,
What is the account with which SQL server(MSSQLSERVER ) is running. MSSQLSERVER must be running with a windows account which has got sufficient rights on the destination server.
Use
exec master.dbo.xp_cmdshell N'xcopy e:\dir\*.dat \\servername\e$\dir\'
command do not mention the file name.
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply