Erase files and always on
Boka
Posts: 32 Silver 3
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.
Best Answers
-
Options
Alex B
Posts: 1,132 Diamond 4
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 theERASEFILES_PREFIXoption to indicate which filesERASEFILESshould 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
ERASEFILESjob 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
-
OptionsAlex B
Posts: 1,132 Diamond 4
Hi @Boka ,
- Yes that is correct, that way each file is prefixed with the same string that you specify for the ERASEFILES_PREFIX.
- Yes, currently there is only support for the keyword and it has not been added to the UI for adding in that manner.
- 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.
Alex
Answers
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
Have you visited our Help Center?
The team have implemented this functionality and it will be available in the next release (which should be 10.0.11).
Kind regards,
Alex
Have you visited our Help Center?
Regards,
Nebojsa
Hi Alex,
Just to clarify: