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

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

Comments

  • Options
    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]
  • Options
    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.
  • Options
    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
  • Options
    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)?
  • Options
    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
  • Options
    petey wrote:
    Yes.

    Perfect. This morning's backup went flawless and all the older data has been erased.
  • Options
    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?
  • Options
    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
  • Options
    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?
Sign In or Register to comment.