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

Another FILEOPTIONS question

Randy DoubRandy Doub Posts: 26
edited July 22, 2009 9:36AM in SQL Backup Previous Versions
I think I know the answer to this but thought I'd ask anyway... just in case...
I backup to local disk and copy to a network share using COPY_TO, ERASEFILES=23h, ERASEFILES_REMOTE=30 and FILEOPTIONS=1. Works great. I have one server that doesn't have enough disk available to hold the one day's worth of backup files. So I backup to another server and then copy to the network share using the same command options. So technically, both locations are remote, but I thought ERASEFILES=23h would delete the files from the backup location. What happens is both locations keep files for 30days, the ERASEFILES_REMOTE option. Is there anyway I can handle this within SQL Backup? Here's my command...

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [sde, ReportServer] TO DISK = ''\\sqlalpha01\e$\sql_backup_helpdesk\<AUTO>.sqb'' WITH COPYTO = ''\\filcnc1s08pp001\sqlback01\HelpDesk\'', COMPRESSION = 1, ERASEFILES = 23h, ERASEFILES_REMOTE = 30, FILEOPTIONS = 1, MAILTO_ONERROR = ''me@myplace.org''"', @exitcode OUT, @sqlerrorcode OUT


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Randy,

    Thanks for bringing this up, as it can be confusing. The way I understand it, ERASEFILES_REMOTE is applied to any "secondary" backup location, for instance, a network share. ERASEFILES concerns itself with local resources such as fixed disks. We are currently re-thinking the way that copy retention is going to be done in the future, for instance, to allow multiple ERASEFILES_REMOTE parameters for multiple copyto operations (bug reference SB-3801)

    So presumably, ERASEFILES_REMOTE overrides ERASEFILES when the primary backup location is a share. I'll try to get some for-sure confirmation about this.
  • Options
    Hi Brian,
    Thanks for the response. I figured as much since that's the way its working (talk about stating the obvious!). I though I'd ask in case there was some command option I was overlooking.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Randy,

    I can confirm that now. ERASEFILES_REMOTE settings are applied to any backup to a network share and override any ERASEFILES settings, unless the backup is to a fixed disk, which also includes SANs or any other type of appliance that exposes a drive letter.
Sign In or Register to comment.