ERASEFILES does not delete COPYTO location?

• 
  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]

  Jason Cook October 08, 2007 12:28

  - 0 +
• 
  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.

  Mindflux October 08, 2007 15:57

  - 0 +
• 
  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.

  petey October 08, 2007 23:56

  - 0 +
• 

  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)?

  Mindflux October 09, 2007 10:52

  - 0 +
• 
  Yes.

  petey October 09, 2007 21:08

  - 0 +
• 

  petey wrote:

  Yes.

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

  Mindflux October 10, 2007 10:20

  - 0 +
• 

  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?

  cgriffin October 15, 2007 14:17

  - 0 +
• 
  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

  Jason Cook October 16, 2007 06:39

  - 0 +
• 
  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?

  swirl80 October 23, 2008 11:31

  - 0 +

Add comment

Please sign in to leave a comment.