This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
Once you have deployed your databases and have been able to access and create logins/users for your applications or for anyone using it, the next thing to know is who is responsible for taking backups.
Azure SQL Database has automated backup strategy of full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes. The frequency of transaction log backups is based on the compute size and the amount of database activity.
The important thing is, by default, Point in time Recovery of 7 days is maintained by Azure, i.e. Full, Differential and Transactional Log backups are stored so that if you need to do point in time restore for anytime in the previous 7 days then that will be possible from the backups stored by Azure.
The default time is 7 days, but can be changed to up-to 35 days, for doing this go to your SQL Server resource, and go to Manage Backups.
PITR is 7 days by default, which can be changed to maximum 35 days. To change it select “Configure Retention” and change it to the one from the available options which are 1, 7, 14, 21, 28, 35 days.
Above are the normal backups for doing Point-in-time restore but what if you need to have the Long term backups, similar to what we generally maintain in our environment. Like for one of my project we maintain Weekly, Monthly and Yearly backups. For doing the same for your Azure SQL Database you can use the “Configure Retention”.
As you can see that we can choose the Weekly, Monthly and Yearly LTR backups (maximum is 10 years)
For checking the pricing related to the backups and databases I would suggest to check the pricing calculator.
Azure SQL Database works on automated backups, you cannot change the backup strategy and it will always be Weekly Full backup, every 12 hours differential backup and Transaction Log backup every 5-10 minutes.