Erase files and always on

BokaBoka Posts: 33 Silver 3
in SQL Backup
Hi,

We are running SQL 2016 with AG on 2 hosts. Secondary replica is read-only so we are doing backup on passive node. Backup files are directed to network share. After failover and failback (usually during system upgrade) it looks like SQL Backup 10 is loosing information about old backup files so those files remain on share and we have to manually delete them. Is there something wrong in our approach? Or this could be bug? Thanks anyway for help.
Tagged:
· Share on Twitter

Best Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    Hi @Boka (and everyone else),

    This has now been released in the latest version of SQL Backup 10.0.11.

    You will need to use the <AVAILABILITYGROUP> file location tag as a prefix for the names of the backups themselves and then use the ERASEFILES_PREFIX option to indicate which files ERASEFILES should remove.  

    For example, if <AVAILABILITYGROUP> resolves to MY_AG_NAME, the command would look like this:

    BACKUP DATABASE ... TO DISK = 'C:\Backups\<AVAILABILITYGROUP>_<DATABASE>_<TYPE>_<DATETIME yyyymmdd>.sqb' WITH ERASEFILES = 14, ERASEFILES_PREFIX = [MY_AG_NAME] ...


    This way each node of the availability group has the same prefix (in this case MY_AG_NAME) and the ERASEFILES job is looking for that prefix when run on any node of the AG to determine which files to delete rather than looking at the server name which is different between each node.


    Kind regards,

    Alex

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    Hi @Boka ,
    1. Yes that is correct, that way each file is prefixed with the same string that you specify for the ERASEFILES_PREFIX.
    2. Yes, currently there is only support for the keyword and it has not been added to the UI for adding in that manner.
    3. You can use either (or any of the ERASEFILES*​ methods, they should all look at the prefix to see if they need to use that instead of doing it by server as it normally would.
    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @Boka,

    I believe I'm already working with you in a ticket, but just to update here for anyone else that may come across it.

    This is something the team are working on currently under internal reference SB-5738.  The issue is that the ERASEFILES functionality currently only looks at the server name - so when the system fails over and the backups are taken from a different server, and then it fails back - those backups from the different server are not identified as backups that are OK to delete as they are from a different machine.

    I will update here when there is further information from the team on this, but in the meantime you will unfortunately need to manually delete them or let them be removed by the ERASEFILES the next time the replicas failover.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @Boka,

    The team have implemented this functionality and it will be available in the next release (which should be 10.0.11).

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • BokaBoka Posts: 33 Silver 3
    Nice to hear. Thanks
    Regards,
    Nebojsa

    · Share on Twitter
  • BokaBoka Posts: 33 Silver 3

    Hi Alex,

     Just to clarify:

    1. <AVAILABILITYGROUP> tag should be at the beginning of the file name
    2. ERASEFILES_PREFIX should be added to backup command manually
    3. ERASEFILES should be used instead of ERASEFILES_PRIMARY (which is created by GUI; or it doesn’t matter)

    · Share on Twitter
Sign In or Register to comment.