Transferring backups

  • P Jones


    Points: 12323

    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)



    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



    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



    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.

  • Helen-84411

    Hall of Fame

    Points: 3320


    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. 


    exec master.dbo.xp_cmdshell N'xcopy e:\dir\*.dat  \\servername\e$\dir\'

    command do not mention the file name.


    Are you a born again. He is Jehova Jirah unto me

Viewing 2 posts - 1 through 2 (of 2 total)
