Forum Discussion
Azure SQL Managed Instance - DB Backup Retention
Dear All,
We have requirement for backup retention as below:
(1) PITR for 2 months
(2) To be able to restore backup of any day (time doesn't matter) for 1 year
Can you please advise how to accoplish above?
Thank you.
Hi,
Towards (1):
Azure SQL Managed Instance supports Point-in-Time Restore (PITR) for a maximum of 35 days. To enable this, configure the short-term retention policy to 35 days using the Azure portal, PowerShell, or CLI.
To extend Point-in-Time Restore (PITR) beyond the 35-day platform limit in Azure SQL Managed Instance, you could manually export transaction logs continuously to external storage. Once exported, you can use the Log Replay Service (LRS) or a custom log replay workflow to apply these logs and reconstruct the database state at any specific point in time, beyond the native PITR window.
Please note: This approach is relatively complex, not natively supported end-to-end, and requires custom automation and scripting for exporting, storing, and replaying logs.
Towards (2):
Long-Term Retention (LTR) for Azure SQL Managed Instance (SQL MI) only supports weekly backups directly (not daily), unlike Azure SQL Database. However, you can achieve daily backups with 1-year retention using the following:
Use automated SQL Agent Jobs or Azure Automation to:
- Run a daily full export
- Save each backup to Azure Blob Storage.
- Apply an Azure Storage lifecycle management policy to retain each backup for 1 year (365 days).
- Use these backups for manual restore in case of data loss, corruption, or auditing requirements.
Best regards!
3 Replies
In Azure SQL Managed Instance, PITR (point-in-time restore) is only supported for up to 35 days. You cannot extend it to 2 months. To meet longer retention needs, you should configure long-term retention (LTR). LTR lets you keep full backups for up to 10 years and you can set it to store daily, weekly, monthly, or yearly full backups. This way you can restore backups from any day within a year, but it will be from the full backup, not PITR granularity.
Hi,
Thank you very much for your recommendations.
Regards,
Hi,
Towards (1):
Azure SQL Managed Instance supports Point-in-Time Restore (PITR) for a maximum of 35 days. To enable this, configure the short-term retention policy to 35 days using the Azure portal, PowerShell, or CLI.
To extend Point-in-Time Restore (PITR) beyond the 35-day platform limit in Azure SQL Managed Instance, you could manually export transaction logs continuously to external storage. Once exported, you can use the Log Replay Service (LRS) or a custom log replay workflow to apply these logs and reconstruct the database state at any specific point in time, beyond the native PITR window.
Please note: This approach is relatively complex, not natively supported end-to-end, and requires custom automation and scripting for exporting, storing, and replaying logs.
Towards (2):
Long-Term Retention (LTR) for Azure SQL Managed Instance (SQL MI) only supports weekly backups directly (not daily), unlike Azure SQL Database. However, you can achieve daily backups with 1-year retention using the following:
Use automated SQL Agent Jobs or Azure Automation to:
- Run a daily full export
- Save each backup to Azure Blob Storage.
- Apply an Azure Storage lifecycle management policy to retain each backup for 1 year (365 days).
- Use these backups for manual restore in case of data loss, corruption, or auditing requirements.
Best regards!
