Different retention times for local and remote backup files

shawnnwfshawnnwf Posts: 16
edited February 9, 2009 9:00AM in SQL Backup Previous Versions
Hello,

I am trying to get a log backup job to have different file retention times for the local copy and the remote copy. I want the local files to stay for only 24 hours and the remote SAN copy (sent via COPYTO) for 3 days.

Here is an example of the backup syntax that I am using.

EXECUTE master..sqlbackup -SQL "BACKUP LOGS
    TO DISK = ''f:\sqblbackups\<AUTO>.sqb'' WITH COMPRESSION = 3, COPYTO = ''\\MySanStorage\ServerName'', ERASEFILES_ATSTART = 24h, ERASEFILES_REMOTE = 3, FILEOPTIONS = 3, MAILTO =
''someone@something.com''"

The files go to the correct places and works fairly quickly. The problem is the remote coppies are not being deleted after 3 days. The local copy folder is keeping up with the one day limit, so it is just the remote folder.

I don't want to play around with the fileoptions, in case it deletes the SAN files early.

Am I doing something wrong with the syntax?
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Using a FILEOPTIONS value of 3 causes:

    - SQL Backup to delete old files in the COPYTO folder(s)
    - old files will only be deleted if they do not have the archive attribute set. This means that if the file has the archive attribute, it will not be deleted. This option is usually used when you have back up files to tape. When the tape backup application has copied the file to tape, it will remove the archive attribute, and only then will SQL Backup delete the file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • shawnnwfshawnnwf Posts: 16
    That is exactly what I want...but it isn't working. I want the files in the COPYTO folder to get deleted automatically, but they aren't.

    As stated in my first post, the files are backing up to a SAN backended drive, not to tape.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please check if the files copied to the COPYTO folder have the 'Archive' attribute active?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • shawnnwfshawnnwf Posts: 16
    Peter,

    Yes they do. Will adding the 4 mask to the fileoptions total remove these?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Let's back up a little here.

    When a file is copied, it usually has the archive attribute set. Using a FILEOPTIONS value of 1, this file will be deleted if its age meets the ERASEFILES/ERASEFILES_ATSTART/ERASEFILES_REMOTE value. If you use the value 3, which is the sum of 1 and 2, the file will only be deleted if its archive attribute is NOT set.

    Why would anyone want to do this? Because if you back up the files to tape, the tape archive application uses the 'archive' attribute to determine which files need to be copied to tape. Once the file has been copied, the tape archive application will remove the archive attribute, so that it does not copy the file to tape again.

    So in this example, if you use 3 for FILEOPTIONS, SQL Backup will only delete old files if they have already been backed up to tape.

    A value of 4 only tells SQL Backup to overwrite existing files in the COPYTO folders.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • shawnnwfshawnnwf Posts: 16
    Peter,

    I just changed the FILEOPTIONS to 1 and it worked perfectly. I have 24 hours on local disk and 3 days on SAN storage.

    Thank you for your help!
    · Share on Twitter
  • LehighDBALehighDBA Posts: 24
    Can the backup wizard be updated to allow the ERASEFILES_REMOTE value to be entered into it instead of having to modify the SQL Job directly? What about the FileOptions?
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi LehighDBA,

    Thank you for your post into the forum.

    Unfortunately the ERASEFILES_REMOTE and FILEOPTIONS are only configurable outside of the GUI at this time.

    The syntax and available options are detailed in the help file under The SQL Backup Toolkit>Toolkit Syntax>The BACKUP Command.

    Thanks
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • Giggles220Giggles220 Posts: 154
    You say at this time? Are there plans to add this to the GUI? Clearly this is something that many of us want, myself included.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi Giggles220,

    The demand for these features to be configurable in the GUI has been recognised and will be considered for a future release.

    Thanks
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • LehighDBALehighDBA Posts: 24
    Still eager to have this available in the GUI...
    · Share on Twitter
  • priyasinhapriyasinha Posts: 548 Silver 1
    Hi,

    ERASEFILES_REMOTE option will be available on the UI in the next release.

    Thanks,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
    · Share on Twitter
Sign In or Register to comment.