none
Robo Copy command to transfer sql server logshipping files

    Question

  • Hi Experts
     
    I want to Copy log shipping files between primary and secondary servers.
    Can some one help in providing the robocopy command for copying the files on to secondary server.There is no other go except roby copy as i have network related issues.

    As a known fact, the logshipping file names will be dynamic with the datestamp included in the filename.

    It has to pick only the files which are not existing on the secondary server.

    This process to be scheduled in sql server agent.
    Windows server 2003 with sql 2005 64-bit

    Or is there any other way to handle this with xp_cmdshell (with dynamic file names and check if the files exist on secondary)

    Thanks
    Naga

    Tuesday, November 24, 2009 3:45 PM

Answers

  • Robocopy syntax - http://ss64.com/nt/robocopy.html

    Store the syntax / command in a cmd file . Then execute the cmd file using the xp_cmdshell stored proc from the AGENT.
    Thanks, Leks
    • Marked as answer by Naga1982 Thursday, December 03, 2009 6:30 AM
    Wednesday, November 25, 2009 7:08 AM
    Answerer
  • Naga,

    Try the script below.
    Add this script as second step in the source server backup job, so that once backup log completed, it will transfer using robocopy.
    *** Make sure to change dbname, source server path, share path and robocopy path in the script before use.

    set nocount on
    declare @TableVar table (fname VARCHAR(500))
    declare @sql VARCHAR(4000), @sql1 VARCHAR(4000), @spath VARCHAR(2000), @despath VARCHAR(2000)
    SET @spath='SourceServer\LogShipping\'
    SET @despath='\\DestinationServer\LogShipping'
    Insert INTO @TableVar
    select top(1) b.physical_device_name from msdb.dbo.backupset a
    JOIN msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
    where a.type ='L' and a.database_name='DBNAME'
    order by a.backup_finish_date desc
    select @sql='Exec xp_cmdshell ''robocopy ' +@spath +' '+@despath +' ' + REPLACE(fname,@spath,'') +'' from @TableVar
    Print 'Copying Backup : ' + @sql
    EXEC (@sql)

    Vidhya Sagar. Mark as Answer if it helps!
    Monday, November 30, 2009 9:13 AM
    Moderator

All replies

  • This probably isn't the forum for this since you really need help with robocopy....

    You could use /M... This will copy files with the archive bit and then turn off the archive bit so the file won't be copied again.  You just need to be careful that you don't have any additional backup jobs running on these files that will turn off the archive bit (like a tape backup)....
    Tuesday, November 24, 2009 4:52 PM
  • Run ROBOCOPY /? to get the detailed help. If you just want make sure all files in source dir are copied/updated to target, simply run:

    ROBOCOPY <source_unc> <target_unc> -E -XX
    Wednesday, November 25, 2009 3:23 AM
  • Robocopy syntax - http://ss64.com/nt/robocopy.html

    Store the syntax / command in a cmd file . Then execute the cmd file using the xp_cmdshell stored proc from the AGENT.
    Thanks, Leks
    • Marked as answer by Naga1982 Thursday, December 03, 2009 6:30 AM
    Wednesday, November 25, 2009 7:08 AM
    Answerer
  • Hi Leks

    Thanks for your help.
    When i try the xpcmd shell and robocopy, the files are copied in sequence based on filename.
    How to achive to copy the files based on the created/modifieddate.
    how to solve this issue.

    Thanks
    Naga

    Wednesday, November 25, 2009 5:51 PM

  • How to achive to copy the files based on the created/modifieddate.
    how to solve this issue.

    Thanks
    Naga

    Naga,

    An SSIS package would give you that level of control.

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, November 27, 2009 1:09 AM
  • Naga,

    Try the script below.
    Add this script as second step in the source server backup job, so that once backup log completed, it will transfer using robocopy.
    *** Make sure to change dbname, source server path, share path and robocopy path in the script before use.

    set nocount on
    declare @TableVar table (fname VARCHAR(500))
    declare @sql VARCHAR(4000), @sql1 VARCHAR(4000), @spath VARCHAR(2000), @despath VARCHAR(2000)
    SET @spath='SourceServer\LogShipping\'
    SET @despath='\\DestinationServer\LogShipping'
    Insert INTO @TableVar
    select top(1) b.physical_device_name from msdb.dbo.backupset a
    JOIN msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
    where a.type ='L' and a.database_name='DBNAME'
    order by a.backup_finish_date desc
    select @sql='Exec xp_cmdshell ''robocopy ' +@spath +' '+@despath +' ' + REPLACE(fname,@spath,'') +'' from @TableVar
    Print 'Copying Backup : ' + @sql
    EXEC (@sql)

    Vidhya Sagar. Mark as Answer if it helps!
    Monday, November 30, 2009 9:13 AM
    Moderator
  • An additional point here would be that if you have a monitor server, then the file copied information would be incorrect and if you have setup any alerts, they would report incorrect messages.
    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    Monday, November 30, 2009 2:44 PM