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

COPYTO, ERASEFILES and FILEOPTIONS

knockoutknockout Posts: 8
edited August 23, 2010 11:12PM in SQL Backup Previous Versions
Hi,

I backup to a drive on the primary server and use COPYTO to copy the backup to a remote network share. I also use ERASEFILES and FILEOPTIONS = 1. I have been noticing that old backup files get deleted on the remote network share even if the the COPYTO is unsuccessful (in other words, deletion precedes the COPYTO). Is there any way to prevent this? I'm using SQL Backup Pro 5.4

Comments

  • Options
    Thanks for your post.

    This shouldn't be happening unless you're using ERASEFILES_ATSTART.

    I haven't got a SQL Backup 5 environment to test just now, but I will set one up and see if I can recreate your issue.
    Chris
  • Options
    I've enclosed part of the log file generated for a sample backup if it helps:
      9/22/2009 6:00:00 PM: BACKUP DATABASE [dbname] TO DISK = '<AUTO>' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', DIFFERENTIAL, INIT, ERASEFILES = 12h, COPYTO = '\\remote\E$\Backup\server\dbname', FILEOPTIONS = 1, COMPRESSION = 2 9/22/2009 6:03:11 PM: Backup data size : 3.755 GB 9/22/2009 6:03:11 PM: Compressed data size: 938.851 MB 9/22/2009 6:03:11 PM: Compression rate : 75.58% Processed 147232 pages for database 'dbname', file 'filename' on file 1. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 491971 pages in 188.717 seconds (21.355 MB/sec). 9/22/2009 6:03:11 PM: Deleting old backup file: D:\Backup\server\dbname\dbname_DIFF_20090921_180000.sqb 9/22/2009 6:03:11 PM:
    Deleting old backup file: \\remote\E$\Backup\server\dbname\dbname_DIFF_20090921_180000.sqb
    9/22/2009 6:03:39 PM: Copied D:\Backup\server\dbname\dbname_DIFF_20090922_180000.sqb to \\remote\E$\Backup\server\dbname\dbname_DIFF_20090922_180000.sqb.
    9/22/2009 6:03:39 PM: SQL Backup process ended.

    9/22/2009 6:03:39 PM: Deleted msdb entries older than 8/23/2009 6:03:39 PM
    9/22/2009 6:03:39 PM: Deleted local history entries older than 8/23/2009 6:03:39 PM
  • Options
    Thanks for your reply.

    It seems that this always happens, as the erase happens after the backup completes, but before the file is copied to the network share.

    I have logged this as a bug with SQL Backup (SB-4420)

    However, if you upgrade to SQL Backup 6, the network resilience features will make sure that the backup file is still copied to the network share if you encounter any network glitches or short outages.
    Chris
  • Options
    Any progress made on this bug?
  • Options
    peteypetey Posts: 2,358 New member
    This bug report (SB-4420) is still open. So far, we know of only one request to perform the deletion after the file has been successfully copied. Changing the default behavior now would affect all other users, who may be perfectly happy with the deletion happening before the copy. Option would then be to introduce a new keyword e.g. ERASEFILES_AFTERCOPY, but if it's only useful for a handful of users, it wouldn't be feasible for us to add that keyword.

    If you can tell us why your files may not always be copied successfully, perhaps we can offer some workarounds for now.

    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.