Log Backups with CopyTO

qsac226qsac226 Posts: 22
edited October 27, 2016 11:43PM in SQL Backup

I have an instance of SQL 2014 that I am running log backups every 15 minutes and using the copyto command to copy to a second server for DR.

The issue is that if I use the queued copy, I find it falls behind and is slow to copy. If i use simplecopy, I end up missing a log file or two, if there are connectivity issues with my DR.

I do have about 200 databases. Ideally I would like to use the queued copy mechanism, so if I have network issues, it will retry. Does anyone know a way to tweak this copy mechanism?

NOTE: i am running latest build of SQL Backup 7. Does SQL Backup 8 offer any improvements on the queued copy?

Thanks in advance


  • peteypetey Posts: 2,358 New member
    Using the queued backups, do the missing files eventually get copied to the destination folder?

    How the queued copy works is that the oldest files are selected for processing. The file is assigned to one of five threads (user-definable) to be copied. On failure, the file would be copied again later in intervals of 2, 4, 6, 8 and 10 minutes, for a period of 24 hours.

    Thus, a larger older trx log backup file might take longer than a smaller newer file to be copied to the destination folder. The result is that the newer file appears on the destination folder first. This would trigger a warning that the log cannot be restored because the larger older file needs to be restored first. Eventually, the older file will be copied over, and the restore can proceed. The same situation applies when a network issue interrupts the copying process - newer files might get copied over before the older files.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.