What are the challenges you face when working across database platforms? Take the survey
Options

COPYTO failed after Mirror failure

davidfreyedavidfreye Posts: 4
edited August 30, 2010 2:46AM in SQL Backup Previous Versions
Well the Mirror failure could be a coincdence. Heres the situation..
I had a Mirror failure which required me to ship the logs, which I used Red Gates log shiping, to my secondary server to restore before I could re-initialize the server to be the mirror. After which all my log backups fail to COPYTO the network location for my snap to pick it up. What do I need to do to get this working again.

When I do Prints of my variables, everything is correct with path and filenames, they just dont make it there.

Here is my backup script with Backup 6:

SET @sql_string = '-SQL "BACKUP LOG ' + @dbname + ' TO DISK = ''' +
@file_name + ''' WITH COMPRESSION = 2, ERASEFILES = 1b, ' +
' COPYTO = ' + '''' + @directory + '''' +
' ,MAXDATABLOCK = 524288, MAXTRANSFERSIZE = 524288, ERASEFILES_REMOTE = 1, ' +
' FILEOPTIONS = 3, DISKRETRYINTERVAL = 90, DISKRETRYCOUNT = 2"'

Comments

  • Options
    Log backup for a database failing to get copied to the log shipping share
    Category: Troubleshooting & error messages
    Date: 18 Feb 2010
    Product: SQL Backup
    During the process of a log shipping job, SQL Backup may log something similar the following error:
    "COPYTO error: Unable to copy F:\backup\SVR_LOG_DB_xxxxx.sqb to \\SERVER\Transactionlogs\SVR\DB\SVR_LOG_DB_xxxxx..sqb (Source file does not exist: F:\backup\SVR_LOG_DB_xxxxx..sqb)."

    In version 6 of SQL Backup, the behaviour of the COPYTO keyword has changed for transaction log backups.

    When you create a transaction log backup file using SQL Backup, and request that the file is copied to another location (either using the COPYTO keyword, or with the "Copy to location" setting in the SQL Backup wizards), the backup files are placed in the log copy queue before they are copied. SQL Backup checks the log copy queue regularly for transaction log files that are waiting to be copied. Files are then copied in the correct order; log files for lower LSNs are copied first.

    If there is a problem during the copying process (caused by an extended network outage, for example) and the copy fails, the files remain on the log copy queue, ready for another copy attempt.

    By default, SQL Backup attempts to copy each transaction log backup file as soon as it is added to the log copy queue. If the first copy attempt fails, subsequent copy attempts are made according to the following schedule:

    Copy attempt - Copy attempt is made...

    1 - immediately

    2 - 2 minutes after the 1st copy attempt failed

    3 - 4 minutes after the 2nd copy attempt failed

    4 - 6 minutes after the 3rd copy attempt failed

    5 - 8 minutes after the 4th copy attempt failed

    6 - 10 minutes after the 5th copy attempt failed

    All subsequent copies - 10 minutes after the preceding copy attempt failed
    SQL Backup will continue trying to copy the oldest transaction log backup file every 10 minutes, for up to 24 hours.

    If you wish to revert to the previous copy behaviour for transaction log backups, edit your log backup job on the source server and add the replace the USESIMPLECOPY keyword.
  • Options
    peteypetey Posts: 2,358 New member
    When you run the following from Management Studio:

    EXEC master..sqbdata 'SELECT * FROM backupfiles_copylist_log'

    what are the errors logged for the files that were not copied?

    Thanks.
    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.