Database backup and restore strategy is vital in preserving data for disaster recovery, compliance or fixing human error. In a business critical application, data need to be preserved and be readily available to restore/roll back to a state in time required to meet the business SLA.

Data Backup

When an Azure SQL database is created, a database backup service is a automatically created to perform regular database backup. Full database backup is created on a weekly basis followed by differential backup every 12hrs and log backup every 5-10min.

The backup files stored in Azure storage in Read-Access Geo-Redundant Storage (RA-GRS) by default, this can be changed to Zone Redundant Storage (ZRS) or Locally Redundant Storage (LRS).

• LRS: Local copy of the backup file synchronously three times within a single physical location in a primary region. This is not suitable for high availability requirement as it is the least durable option and it only protects data against hardware failure.
• ZRS: Three copies of the backup file are stored across multiple facilities (physical location) within the same geographical region. Data is only replicated synchronously within a primary region
• RA-GRS: Multiple copies of the backup file are stored across different Azure geographical regions. In RA-GRS data is replicated asynchronously to a secondary region, data write in the primary region will not be automatically available in the secondary region

Backup Retension

In Azure database backup is retained for short term as well as long term.

• Short Term Retention: By default database backup files are retained for 7 days, this can be extended to 35 days by changing the retention policy. Short term retention is provided free by Azure as part of the over all database service.

• Long Term Retention: The long term retention feature is available for Azure SQL database is up-to 10 in Azure blob storage, this can be in a weekly, monthly or yearly basis. A copy of the full backup file gets copied to different storage for long term retention at extra cost when the policy is enabled and this feature is not enabled by default.

Data Restore

In Azure database restore from backup file are performed in one of the following options

1. Create new database on the same SQL database server recovered to a specified point in time within the retention period
2. Create a database on the same SQL database server recovered to the deletion time for a deleted database
3. Create new database on any SQL database server in the same region recovered to the point of the most recent backup
4. Create new database on any SQL database server in any other region recovered to the point of the most recent replicated backups