Automatic Backups in Azure SQL Database

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.

Backup settings

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.

PITR

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.

Summary

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.

One thought on “Automatic Backups in Azure SQL Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s