Log Backups with CopyTO

qsac226 · October 27, 2016 9:05AM

Hello

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
Steve

petey · October 27, 2016 11:43PM

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.

Log Backups with CopyTO

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk