Another FILEOPTIONS question
Randy Doub
Posts: 26
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
Thanks.
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
Thanks.
Comments
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.
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.
Thanks,
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.