In this article, we will review default backup settings, long-term retention (LTR) backups in Azure SQL database and how-to setup long-term retention (LTR) backup policy in Azure SQL database using Azure portal.
The database backups are automatically managed in Azure. Azure takes a full backup of your database every week, differential backups every 12 hours and transaction log backup every five-ten minutes. These backups are replicated to the paired data center to protect backups in case of an outage in the primary data center. The retention period of these backups files is based on the service level you choose while creating the database in Azure. If you choose basic tier the backups up to 7 days old are stored. If you choose standard or premium tier the backups up to 35 days old are stored.
With Azure SQL database backups, you can restore the database to a point in time with a different database name on the same Logical SQL Server based on the retention period.
The point in time restore in Azure SQL database
We can restore the Azure SQL database to point in time within the retention period. For example, the database with a backup retention period of 7 days can be restored to any point within 7 days. The database with a backup retention period of 35 days can be restored to any point within 35 days.
To restore the database using the Azure portal, log in to the Azure portal and navigate to ‘All resources’. Click on the Azure SQL database that you are going to restore point in time. Please refer to the below image.
In the database details page, Click on Restore as shown in the below image.
Select the source as the point in time. we cannot restore the database backup to the existing database in Azure SQL Server. The database can be restored only as a new database. Enter the new database name. The oldest restore point is shown just below the database name. We cannot restore the database to a state that is before the oldest restore point. Enter the restore point, the time is in UTC time zone.
The target server is locked out as we can restore the database backup which creates a new database on the same logical SQL Server. Click on OK to restore the database with the specified database name on the same logical SQL Server.
Long-term retention (LTR) backups in Azure SQL database
In some cases, we may need a backup of the database that is longer than the default retention period. This is due to the regulatory, compliance of the applications. Long-term retention (LTR) backup is a feature in Azure to store the full backups up to 10 years. Long-term retention (LTR) backups can be configured on a single Azure SQL database or elastic pool.
Log in to the Azure portal and click on the logical SQL Server that has the database on which you are going to configure long-term retention (LTR) backup policy.
In the server details page, click on Manage backups. Select the database and click on Configure retention.
In the configure policies pane, select weekly LTR backups if you want to retain weekly full backups and specify the retention period of the weekly backups. i.e. one backup from each week is copied to different long-term blob storage.
Select Monthly LTR backups to copy one backup from the first week of every month to long-term storage.
Select Yearly LTR backup and specify the week number to copy one full backup of Azure SQL database in the specified week of the year to the long-term storage.
These backups are stored in the long-term storage based on the retention period you specified while creating the retention policy. In case if the database is geo-replicated or part of auto-failover groups, configure long-term retention backups with the same settings on the secondary database.
Restore long-term retention (LTR) backup
Restoring the long-term retention (LTR) backup is like restoring the Azure SQL database to point time using Azure automatic backups.
Log in to the Azure portal and click on the database you want to restore. In the database details page, click on Restore. Select the source as long-term backup retention as shown in the below image, enter the name of the new database and select the backup from the list of the available backups. Click on OK to restore. Once you click on OK, a new database with the specified name is created on the same logical SQL Server.
Conclusion
In this article, we explored the default backup retention period in the Azure SQL database, point in time restore of Azure SQL databases using automated backups, and long-term retention (LTR) backups on the database. In case you have any questions, please feel free to ask in the comment section below.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019