Forum Discussion

AlwaysTheLearner's avatar
AlwaysTheLearner
Copper Contributor
Jul 31, 2025
Solved

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.

Managed Instance
  • MoritzG's avatar
    MoritzG
    Aug 06, 2025

    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:

    1. Run a daily full export
    2. Save each backup to Azure Blob Storage.
    3. Apply an Azure Storage lifecycle management policy to retain each backup for 1 year (365 days).
    4. Use these backups for manual restore in case of data loss, corruption, or auditing requirements.

     

    Best regards!

2 Replies

  • MoritzG's avatar
    MoritzG
    Iron Contributor
    Aug 06, 2025

    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:

    1. Run a daily full export
    2. Save each backup to Azure Blob Storage.
    3. Apply an Azure Storage lifecycle management policy to retain each backup for 1 year (365 days).
    4. Use these backups for manual restore in case of data loss, corruption, or auditing requirements.

     

    Best regards!

Resources