Understanding SQL DB Backup Retention

Question

Hello,

Retention setting for my Azure DB set to :

• differential : 12 hours
• weekly : 4
• monthly : 6
• yearly : 3 on week 1

My question is for weekly and monthly when the backup will be created since there no option to select date or day.

Answer 1

@Handian Sudianto Greetings!

For Azure SQL Database, the weekly and monthly backups are created based on the schedule defined by Azure, not by a specific date or day you select1.

Weekly backups are taken every 7 days.

Monthly backups are taken on the first day of the month.

This means you don't need to worry about setting specific dates; Azure handles the scheduling for you.

Reference link: https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure?view=azuresql&tabs=portal

Additional details: About the backup retention settings you've specified will follow a default schedule for creating backups:

1. Differential Backups:
   • Taken automatically every 12 hours. These backups capture only the data changes since the last full backup.
2. Weekly Backups:
   • Azure automatically creates a weekly backup, typically on Sundays. The retention period you specified (4 weeks) means that the last 4 weekly backups will be kept.
3. Monthly Backups:
   • A monthly backup is taken on the first day of each month. With a retention period of 6 months, the last 6 monthly backups will be retained.
4. Yearly Backups:
   • A yearly backup is created at the end of the first week of each year, and you have set a retention period of 3 years for these backups.

Hope this clarifies. Please write back to us if you have any questions!

---

If the response helped, do "Accept Answer" and up-vote it