Azure SQL Database Automated Backup Strategy

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of time for disaster recovery.

Manual backup strategy

At the moment, I'm using Export function on Azure Portal to manually export the database into Azure Blob Storage. It involves the following steps:

• Log into Azure Portal.
• Navigate to Azure SQL Database.
• Click Export button in the resource main page:
• Input all the required details in Export database wizard and click OK button to submit the request. 

Once the request is completed successfully, database backup file will be created in Azure Blog Storage container you specified. 

Till now, I've manually backed more than 10 times:

These backup files can be used to restore my database or create new SQL databases on Azure.

Automated point-in-time and long term retention backups

All Azure SQL Database resources support automated long term backups except for Azure SQL database managed instance.

Thus there is no need to backup manually.

To enable this, follow these steps:

• Navigate to SQL server resource page of your Azure SQL Database.
• Click Manage backups link under Settings:
  
• Select the database you'd like to configure backups and then click Configure retention link:
  
  
• Configure backup policies based on your need:
  
  
• Click Apply button to apply the changes. 

Now, I don't need to do manual backups anymore and I can always restore my database through these automated backups. All the available backups can be viewed on the same 'Configure retention' page.

To restore the database, you can navigate to SQL Database resource page and then click Restore link to restore from point int time or LTR backups.

Custom backup strategy

If you prefer to backup Azure SQL Database using your own strategies, you can do this use Azure CLI and Azure Functions to schedule your backup scripts.

There is one good example created by Stefano Demiliani: Creating an Azure SQL Database backup via Powershell.

All the actions I mentioned in the previous sections can also be automated using APIs provided by Azure SDK.

Official references

Refer to these official documentations for more information:

• Manage Azure SQL Database long-term backup retention
• Automated backups