ERASEFILES does not delete COPYTO location?

Mindflux

Is there any way to get SQLBackup to delete the copyto location when running the code below? My storage device was nearly full as I had not checked it since last Thursday trusting that SQLBackup was erasing the copied files as well as the original storage file?

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [master, model, msdb, DHL, DHL_Missing, DHL_Orig, pubs] TO DISK = ''C:\SQLBACKUP\Daily\DB\<database>\<AUTO>.sqb'' WITH COMPRESSION = 1, COPYTO = ''E:\SQLBACKUP\Daily\DB\<Database>\'', ERASEFILES = 4, MAILTO = ''me@domain.com'', THREADCOUNT = 3, VERIFY"', @exitcode OUT, @sqlerrorcode OUT

Jason Cook

The keyword you are looking for is "FILEOPTIONS", which is a value between 1 to 7 which defines what happens with the files in the various folders:

Adding 1 will delete old backup files (subject to the ERASEFILES or ERASEFILES_ATSTART definition) in the secondary folders (e.g. COPYTO directories)

Adding 2 will do the same in the primary folder

Adding 4 will overwrite existing files in the COPYTO folder.

So if you want to delete outdated files in both the primary and secondary folders, you would use "FILEOPTIONS = 3" (adding 1 and 2 together)

Hope that helps,
Jason

[edit]Sorry, Peter is right, the "primary folder" one only applies to the DISK keyword, when the ARCHIVE flag is not set... therefore 1 should be sufficient.[/edit]

Mindflux

Hello. Thank you for the answer, however that does not seem to have worked?

SQL Backup log file 5.2.0.2824
10/8/2007 3:00:00 PM: Backing up DHL (transaction log) to: 
10/8/2007 3:00:00 PM:   C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb

10/8/2007 3:00:00 PM: BACKUP LOG [DHL]  TO DISK = 'C:\SQLBACKUP\Daily\TRN\<database>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', VERIFY, ERASEFILES = 4, COPYTO = 'E:\SQLBACKUP\Daily\TRN\<Database>\', FILEOPTIONS = 3, COMPRESSION = 1, THREADCOUNT = 3

10/8/2007 3:00:04 PM: Backup data size    : 39.000 MB
10/8/2007 3:00:04 PM: Compressed data size: 6.294 MB
10/8/2007 3:00:04 PM: Compression rate    : 83.86%

Processed 3869 pages for database 'DHL', file 'DHL_Log' on file 1.
BACKUP LOG successfully processed 3869 pages in 1.306 seconds (24.263 MB/sec).
10/8/2007 3:00:04 PM: 
10/8/2007 3:00:04 PM: Validating files:
10/8/2007 3:00:04 PM:   C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb
10/8/2007 3:00:04 PM: 
10/8/2007 3:00:04 PM: RESTORE VERIFYONLY FROM DISK = 'C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb' 

The backup set is valid.
10/8/2007 3:00:07 PM: Copied C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb to E:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb.
10/8/2007 3:00:07 PM: SQL Backup process ended.

10/8/2007 3:00:07 PM: Deleted msdb entries older than 8/9/2007 3:00:07 PM
10/8/2007 3:00:07 PM: Deleted local history entries older than 8/9/2007 3:00:07 PM

I have backups from 10/3/07 still in my copyto folder, which is older than 4 days old (which is what my ERASEFILES) is set to.

petey

Sorry, you should use the value 1 i.e. FILEOPTIONS = 1.

The value 2 instructs SQL Backup to only delete files if the file does not have the archive attribute. In cases where you back up the files to tape, this option prevents you from deleting files that have not yet been backed up to tape.

Mindflux

petey wrote:

Sorry, you should use the value 1 i.e. FILEOPTIONS = 1.

The value 2 instructs SQL Backup to only delete files if the file does not have the archive attribute. In cases where you back up the files to tape, this option prevents you from deleting files that have not yet been backed up to tape.

So this will delete both the Primary and COPYTO locations after "X" days (ERASEFILES setting)?

petey

Yes.

Mindflux

petey wrote:

Yes.

Perfect. This morning's backup went flawless and all the older data has been erased.

cgriffin

Mindflux wrote:

petey wrote:

Yes.

Perfect. This morning's backup went flawless and all the older data has been erased.

Have you attempted to edit that backup job with the SQLBackup GUI after you added those options?

Jason Cook

Hi,
The SQL Backup GUI will only allow you to edit jobs that can be correctly displayed in the GUI without losing settings. This means the use of the FILEOPTIONS keyword will prevent the GUI from displaying the job.

If you want to update a job, you can use the "Backup Wizard" and at the end the script will be displayed. The SQL Backup command (inside the double quotes) can be copied across into the job and then the job can be saved accordingly.

We are considering adding the FILEOPTIONS keyword into a future version of the GUI of SQL Backup (which would eliminate this particular scenario), but unfortunately I do not have any timeframe as to what release that will occur in.

Thanks,
Jason

swirl80

Hi,

i'm having a similar issue. All of a sudden one of my databases is not clearing any old log files (backup logs every 30mins). The script is exactly the same as for the other DB's but this just does not seem to be clearing the files anymore. I've tried adding the FILEOPTIONS = 1, 2, and 3 and none of them work!

Any ideas as to why this may be happening:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-sql "BACKUP LOG [<Database>]
TO DISK = ''F:\Backups\Shipping\LOG__<Database>_<DATETIME yyyymmdd_hhnnss>.sqb'' 
WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', ERASEFILES = 4, FILEOPTIONS = 1,
COPYTO = ''\\BackupServer\LogBackups'',  COMPRESSION = 1, THREADS = 1"', 
@exitcode OUTPUT, @sqlerrorcode OUTPUT

Can anyone help?