Design for Azure SQL backup and recovery

Completed

It's essential that you can recover your SQL database data. You should consider automated backups of your Azure SQL Database and Azure SQL Managed Instances. Database backups enable database restoration to a specified point in time and within a configured retention period.

Describe automated backups

Both SQL Database and SQL Managed Instance use SQL Server technology to create 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. When you restore a database, the service determines which full, differential, and transaction log backups need to be restored.

  • Full backups: In a full backup, everything in the database and the transaction logs is backed up. SQL Database makes a full backup once a week.

  • Differential backups: In a differential backup, everything that changed since the last full backup is backed up. SQL Database makes a differential backup every 12 - 24 hours.

  • Transactional backups: In a transactional backup, the contents of the transaction logs are backed up. If the latest transaction log fails or is corrupted, the option is to fall back to the previous transaction log backup. Transactional backups enable administrators to restore up to a specific time, which includes the moment before data was mistakenly deleted. Transaction log backups every five to 10 minutes.

Describe backup usage cases

You can use the automated backups in several ways.

Long-term backup retention policies

Azure SQL Database automatic backups remain available to restore for up to 35 days. This period is enough for the purposes of day-to-day administration. But sometimes you might need to retain data for longer periods. For example, data protection regulations in your local jurisdiction might require you to keep backups for several years.

For these requirements, use the long-term retention (LTR) feature. This way, you can store Azure SQL Database backups in read-access geo-redundant storage (RA-GRS) blobs for up to 10 years. If you need access to any backup in LTR, you can restore it as a new database by using either the Azure portal or PowerShell.