Point-in-Time Restore (PITR) is designed for operational recovery and short-term resilience, and Long-Term Retention (LTR) is for storing or retaining backups for long term (up to 10 years)
The same differences also apply to Azure SQL Database as well as Azure SQL MI. We know that both resources are PaaS services and the backups are managed by Azure automatically.
Table of Contents
- 1. Short-Term Retention (Point-in-Time Restore / PITR)
- 2. Long-Term Retention (LTR)
- Table differences between PITR vs LTR in SQL MI
1. Short-Term Retention (Point-in-Time Restore / PITR)
Point-in-Time Restore (PITR) serves as a critical safety net for data protection and operational recovery. It allows you to roll back a database to an exact millisecond, effectively neutralizing the following scenarios:
- Accidental Data Modification or Deletion (User Errors)
- Database and Application Corruption
- Malicious Attacks and Ransomware
PITR manages the continuous full, differential, and transaction log backups that allow you to restore your database to any exact millisecond.
The minimum retention period is 1 day (the maximum is 35 days). By default, Azure configures this to 7 days for new instances.
I checked out the Microsoft documentation to get a better grasp of the PITR method. Please go through the article Point-in-Time Restore – Azure SQL Managed Instance | Microsoft Learn
Point-in-Time Restore (PITR) Recovery Scenarios:
- Restore an existing database to a specific point in time: Revert a live database to any exact timestamp within your configured retention window.
- Restore a deleted database while the instance is active: Recover a deleted database, provided its parent SQL Managed Instance is still running.
- Restore a database from one instance to another within the same subscription: Copy and deploy a database onto a completely different SQL Managed Instance under the same Azure subscription.
- Restore a database from one subscription to another subscription: Transfer data to a different subscription, as long as both subscriptions belong to the same tenant and share the same Azure region.
The implications of changing the PITR retention period are significant. Reducing the retention period results in the immediate deletion of older backups, a non-reversible action that limits recovery options.
Limitations of PITR
There are certain limitations that you should be aware of when doing Point in time restore in Azure SQL MI:
- Instance-level recovery is unsupported: PITR can only restore individual databases as copies, not the entire SQL Managed Instance deployment.
- Subscription-dependent restrictions: The specific limitations you encounter during a restore vary depending on whether the target instance is in the same or a different subscription.
- Subnet policy blocking: Enabling service endpoint policies on a subnet blocks PITR restores coming from instances located in different subnets.
- Risk of instance storage exhaustion: You must verify available disk space beforehand, as restoring a database can fail if it exceeds the remaining storage capacity of the target instance.
2. Long-Term Retention (LTR)
If you require compliance storage beyond the standard 35-day window, you can configure an LTR policy using full backups for up to 10 years.
Here you need to select the Weekly frequency, Monthly frequency and Yearly frequency of retaining the backups.
While PITR handles the immediate 35-day window, many organizations face regulatory mandates such as those from the SEC, FINRA, or GDPR that require data preservation for years. Long-Term Retention (LTR) fulfills this requirement by allowing specific weekly full backups to be retained for up to 10 years.
Check out the official documentation for long term retention Long-Term Retention Backups – Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn
So, the only option to retain your backups for more than 35 days in SQL MI is using long term retention. Note that we can still do a copy only database backup to blob storage and store it for more than 10 years. But this is a manual approach and controlled by us.
Read Also:
Different database restore methods in Azure SQL MI
Table differences between PITR vs LTR in SQL MI
| Category | Point-in-Time Restore (PITR) | Long-Term Retention (LTR) |
| Primary Purpose | Operational recovery (e.g., human error, database corruption) | Regulatory compliance and multi-year archival |
| Retention Period | Configurable from 7 to 35 days | Minimum 1 week till up to 10 years |
| Backup Types | Full (weekly), Differential (12-24 hrs), and Log (5 to10 mins) | Full backups only (copied from the PITR stream) |
| Recovery Point Objective (RPO) | Approximately 10 minutes | Policy-dependent (Weekly, Monthly, or Yearly snapshots). So, the minimum RPO is one week or 7 days |
| Recovery Time Objective (RTO) | Size-of-data operation; typically, < 12 hours depending on size/activity and number of restore requests to the region. | Size-of-data operation; relies on restoration of a full backup. Maximum 12 hrs time for restoration. Generally, the database restore is completed fairly quickly, depending on the size of the database. |
| Cost Structure | Free allowance up to 100% of provisioned data storage of SQL MI; Additional backup storage is billed accordingly. | No free allowance; all storage is billed depending on backup frequencies set for LTR. |
| Persistence after Deletion | Backups are deleted if the instance is deleted. Backups are available even if active database is deleted | Backups persist for the full retention period even if the instance is deleted |
| Control on Backup location | Controlled by Azure | Controlled by Azure |
| Restore Mechanics | Uses the full “log chain” to restore to a specific second | Restores a specific, discrete full backup snapshot as a new database |
| Restored to | Same region only | Any region in Azure |
In both methods, backups are done in the primary region when you set up two regions, with one as Primary and the other as DR. The secondary region only takes backups during a forced or planned failover.
If you have any questions about the differences between PITR and LTR, feel free to reach out to me on LinkedIn or leave a comment below, and I’ll respond as quickly as I can. For more technical blog posts, check out cloudnerchuko.in.