SQL Backup CopyTo Failure, file exists. Log Copy Queue, Status keeps changing, what do I do?
mjninc
Posts: 16 Bronze 1
in SQL Backup
I keep getting messages saying the log 'CopyTo' failed. I have confirmed the file is in the destination location. To help others, I will list what I've found out so far, use the following at your own risk, and confirm yourself if this is RedGate approved as methods of repair.
First, read and understand how the Log Copy Queue works.
Files are placed in a backupfiles_copylist with a certain status.
Based on another forum answer, this is what the status column means.
To see all non-successful file copies, you could run this:
EXEC master..sqbdata 'select * from backupfiles_copylist WHERE status <> ''S'''
To update a status, you could run something like this:
EXEC master..sqbdata 'UPDATE backupfiles_copylist SET status = ''S'' WHERE id in (123,456)'
If after updating the status, a few minutes later the status gets set back to active, you can try a few things, restarting the SQL Backup service, or, copying/removing the files from the destination and letting Sql Backup copy them up.
First, read and understand how the Log Copy Queue works.
Files are placed in a backupfiles_copylist with a certain status.
Based on another forum answer, this is what the status column means.
A = active. SQL Backup is currently attempting to copy the file.
S = successful. SQL Backup has copied the file successfully.
P = pending. The file is waiting to be copied.
C = cancelled. The file will not be copied. This happens when the local source file no longer exists.
E = expired. SQL Backup has hit the limit for the maximum number of tries to copy the file and will no longer
To see all non-successful file copies, you could run this:
EXEC master..sqbdata 'select * from backupfiles_copylist WHERE status <> ''S'''
To update a status, you could run something like this:
EXEC master..sqbdata 'UPDATE backupfiles_copylist SET status = ''S'' WHERE id in (123,456)'
If after updating the status, a few minutes later the status gets set back to active, you can try a few things, restarting the SQL Backup service, or, copying/removing the files from the destination and letting Sql Backup copy them up.
Tagged:
Answers
To resolve this, use the option to overwrite existing files in the COPYTO location in the backup command via the FILEOPTIONS parameter. If you are not currently using the FILEOPTIONS parameter, then add FILEOPTIONS = 4 to the backup command. If you are already using FILEOPTIONS, add 4 to the current value. For more details, please see the FILEOPTIONS section in the help file (https://documentation.red-gate.com/sbu/scripting-sql-backup-pro/the-backup-command)