COPYTO, ERASEFILES and FILEOPTIONS

knockout

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

Chris Auckland

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.

knockout

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: \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 \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

Chris Auckland

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.

knockout

Any progress made on this bug?

petey

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.