ERASEFILES does not delete COPYTO location?

MindfluxMindflux Posts: 47
edited October 23, 2008 11:31AM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • Jason CookJason Cook Posts: 185
    edited October 9, 2007 5:36AM
    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]
    · Share on Twitter
  • MindfluxMindflux Posts: 47
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MindfluxMindflux Posts: 47
    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)?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Yes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MindfluxMindflux Posts: 47
    petey wrote:
    Yes.

    Perfect. This morning's backup went flawless and all the older data has been erased.
    · Share on Twitter
  • cgriffincgriffin Posts: 9
    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?
    · Share on Twitter
  • Jason CookJason Cook Posts: 185
    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
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    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?
    · Share on Twitter
Sign In or Register to comment.