Smart automation of reliable differential backups of always on availability group databases to optimize the restore time
One example method includes performing data protection operations including backup operations. Backups of an availability group include a full backup, transaction log backups, and differential backups. Each differential backup includes transactions since the full backup. The differential backups are performed based on a trigger and, during a restore operation, the differential backups reduce the number of restores that need to be performed and can be restored instead of restoring the corresponding transactional log backups.
1. A method, comprising:
generating, by a processor, transaction log backups of a database operating in an availability group, wherein each transaction log backup includes transactions in the database and wherein the availability group includes a primary node and secondary nodes;
detecting, by the processor, a trigger to perform a differential backup;
generating the differential backup by the processor, wherein the differential backup includes all transactions in the database since a full backup of the database; and
resuming the generation of the transaction log backups of the database.
2. The method of claim 1 , further comprising generating the transaction log backups from a primary node generating the differential backup from the primary node.
3. The method of claim 1 , further comprising generating the transaction log backups from a secondary node and generating the differential backup from a primary node.
4. The method of claim 1 , wherein the trigger comprises performing a predetermined number of the transaction log backups.
5. The method of claim 1 , wherein the trigger comprises a size of the transactional log backups.
6. The method of claim 1 , further comprising generating a second differential backup based on a second trigger, wherein the second differential backup includes all transactions in the database since the full backup of the database.
7. The method of claim 1 , further comprising generating a media log that includes for each backup, a backup level and a mode, wherein the mode is one of full, user scheduled, system promoted on primary node, or system promoted.
8. The method of claim 1 , further comprising performing a restore operation based on a specific transactional log backup by restoring the full backup, the most recent differential backup and all transaction log backups from the most recent differential backup to the specific transactional log backup.
9. The method of claim 8 , further comprising indexing all backups, selecting a node in the availability group, and performing the restore operation on the selected node.
10. The method of claim 1 , further comprising generating the differential backup without deleting any of the transaction log backups.
11. A non-transitory storage medium having stored therein instructions that are executable by one or more hardware processors to perform operations comprising:
generating transaction log backups of a database operating in an availability group, wherein each transaction log backup includes transactions in the database and wherein the availability group includes a primary node and secondary nodes;
detecting a trigger to perform a differential backup;
generating the differential backup, wherein the differential backup includes all transactions in the database since a full backup of the database; and
resuming the generation of the transaction log backups of the database.
12. The non-transitory storage medium of claim 11 , further comprising generating the transaction log backups from a primary node generating the differential backup from the primary node.
13. The non-transitory storage medium of claim 11 , further comprising generating the transaction log backups from a secondary node and generating the differential backup from a primary node.
14. The non-transitory storage medium of claim 11 , wherein the trigger comprises performing a predetermined number of the transaction log backups.
15. The non-transitory storage medium of claim 11 , wherein the trigger comprises a size of the transactional log backups.
16. The non-transitory storage medium of claim 11 , further comprising generating a second differential backup based on a second trigger, wherein the second differential backup includes all transactions in the database since the full backup of the database.
17. The non-transitory storage medium of claim 11 , further comprising generating a media log that includes for each backup, a backup level and a mode, wherein the mode is one of full, user scheduled, system promoted on primary node, or system promoted.
18. The non-transitory storage medium of claim 11 , further comprising performing a restore operation based on a specific transactional log backup by restoring the full backup, the most recent differential backup and all transaction log backups from the most recent differential backup to the specific transactional log backup.
19. The non-transitory storage medium of claim 11 , further comprising generating the differential backup without deleting any of the transaction log backups.
20. The non-transitory storage medium of claim 11 , further comprising indexing all backups, selecting a node in the availability group, and performing the restore operation on the selected node.