One of the most important reasons to prefer Azure SQL Database over SQL Server is Database Backups.
In Azure SQL Database the Backups are automated and the user need not worry about the Database or maintenance.
In this article we would see the Backup and Restore options available in Azure SQL DB as well as Managed Instance since both serves as PAAS.
Automated Backups
- SQL Database automatically creates database backups that are kept between 7 and 35 days(Based on the service Tier used).
- Azure uses read-access geo-redundant storage (RA-GRS) to ensure automated backups preserved even if data center is unavailable.
- Automated backups are created automatically and at no additional cost.
- 200 percent of space(compared to the database size procured) will be allocated for hosting the backups with no additional cost. Anything more than that will be charged.
- Automated Full database backups are created weekly, differential database backups are generally created every 12 hours, and transaction log backups are generally created every 5 – 10 minutes, with the frequency based on the compute size and amount of database activity.
Default automated backups Retention Period:
Backup retention period varies based on the purchase model and the service tier we use.
DTU-based purchasing model
The default retention period for a database created using the DTU-based purchasing model depends on the service tier:
- Basic service tier is 1 week.
- Standard service tier is 5 weeks.
- Premium service tier is 5 weeks.
vCore-based purchasing model
If you’re using the vCore-based purchasing model, the default backup retention period is 7 days (for single, pooled, and Managed Instance databases). For all Azure SQL databases (single, pooled, and Managed Instance databases, you can change backup retention period up to 35 days.(Manged Instance can be purchased only using vCore Model)
Manual Backups
Manual Backups are not possible in Azure Single DB but we would able to take Manual bacpac.
But in case of Managed Instance we could able to take Copy only backups of the database. Since the LSN of the automated backups should not be affected, the backup possibility for managed instance is restricted to Copy_only backups.
Long Term Retention Backups
Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point-time restore (PITR). These backups are copied to different storage blobs if LTR policy is configured. You can configure a LTR policy for each SQL database and specify how frequently you need to copy the backups to the long-term storage blobs. Maximum period a backup can be retained is 10 Years.
Backup Encryption:
If your database is encrypted with TDE, the backups are automatically encrypted at rest, including LTR backups. When TDE is enabled for an Azure SQL database, backups are also encrypted. All new Azure SQL databases are configured with TDE enabled by default whereas the TDE needs to be manually enabled for Azure SQL Managed Instance
Types of Restore Possible:
Managed instance Restores:
- Backups from a Managed Instance can only be restored to another Managed Instance. They cannot be restored to an on-premises SQL Server or to an Azure SQL Database logical server single or pooled database.
- SQL Server Backups can be backed up to blog Storage from there the database can be restored to Managed Instance
- Point in Time restore can be done using the automated backups and LTR backups.
- Backups from a Managed Instance can only be restored to another Managed Instance. They cannot be restored to an on-premises SQL Server or to an Azure SQL Database logical server single or pooled database.
- Normal SQL server On premise\IAAS SQL backup shall be taken to Azure blob storage. Backups stored in Azure storage blob can be directly restored into Managed Instance using the T-SQL RESTORE command.
Azure SQL DB Restores:
- Create a new database on the same logical server recovered to a specified point in time within the retention period.
- Create a database on the same logical server recovered to the deletion time for a deleted database.
- Create a new database on any logical server in the same region recovered to the point of the most recent backups.
- Create a new database on any logical server in any other region recovered to the point of the most recent replicated backups.
- Restoration from LTR
- Restoration from bacpac in Azure blob storage.
- Restoration of a deleted database to the deletion time for a deleted database on the same logical server
CloudToon 