SQL Database PITR not working

Question

I have setup the default PITR backup which has default value for 7 days. But when i am trying to restore the PITR it is not recovering the database.

Answer 1

Hello Rajnish Sharma,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Most of all, Azure SQL Database supports automated backups that enable Point-in-Time Restore (PITR) for up to 7 to 35 days, depending on configuration. PITR - https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore?view=azuresqluses:

• Full backups (weekly)
• Differential backups (daily)
• Transaction log backups (every 5–10 minutes)

By practice, you can use the step below to affirm what go wrong and resolve the issue:

1. Use PowerShell to check actual restore points:

      Get-AzSqlDatabaseRestorePoint -ResourceGroupName "<RG>" -ServerName "<Server>" -DatabaseName "<DB>"
   

   https://learn.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaserestorepoint?view=azps-14.4.0
2. To restore to a specific point do:

      Restore-AzSqlDatabase `
        -FromPointInTime "<UTC-Timestamp>" `
        -ResourceGroupName "<RG>" `
        -ServerName "<Server>" `
        -TargetDatabaseName "<NewDB>" `
        -SourceDatabaseName "<OriginalDB>"
   

   https://learn.microsoft.com/en-us/powershell/module/az.sql/restore-azsqldatabase?view=azps-14.4.0
3. Ensure your restore time is correctly converted to Azure SQL UTC timestamps. https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql
4. You can use Azure Monitor to check backup success/failure and gaps by Go to: Azure Portal > SQL Database > Monitoring > Alerts and use Query Performance Insight and Database Watcher - https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-sql-database-azure-monitor?view=azuresql and https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/backup-activity-monitor?view=azuresql provide more guides.
5. Lower tiers like Basic may have limited PITR capabilities. Upgrade to Standard or Premium for better backup frequency. - https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-sql-database-vcore?view=azuresql
6. For production environments, configure LTR to retain backups for up to 10 years using bash command: az sql db ltr-policy set --resource-group <RG> --server <Server> --database <DB> --weekly-retention P1Y- https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview?view=azuresql

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

Hi @rajnish sharma

We sincerely apologize for the delay in responding and truly appreciate your patience and input on this matter.

Here are the responses and confirmations to the additional questions raised. We kindly request you to review the details provided below step by step. -

Q - Can you please confirm if the configuration in the attached image is correct?
A- This configuration is valid and should allow you to restore your database to any point in time within the last 7 days, down to a 5-minute granularity—assuming no other constraints are in play

Q - I was not able to restore the database PITR for more than 15 minutes.
A - When you restore a database using PITR in Azure SQL Database, the system creates a new database instance at the selected restore point. This new instance does not inherit the full PITR history of the original database—it starts a new backup chain from the moment of restore.

As a result:

• The restored database on September 9th at 12 PM UTC only has backups starting from that point forward.
• Attempting to restore that new database to September 8th is not possible because the original backup chain is not linked to the new instance.

This behavior is confirmed in Microsoft documentation:

“You can use point-in-time restore to create a database that's a copy of a database at a specific, earlier point in time. However, the new database starts a new backup chain and cannot be restored to a point before its creation.” — Restore a database from a backup - Azure SQL Database

Q - How can different PITR backups e.g. 4th day, 5th day and 6th day backup?
A - Azure SQL Database automatically performs the following types of backups:

• Full backups: Taken weekly.
• Differential backups: Taken every 12 hours.
• Transaction log backups: Taken every 5 minutes.

These backups are stored in a way that allows you to restore your database to any specific point in time within the configured retention period, which can range from 1 to 35 days depending on your settings. [Point-in-T...Explained], [Basics of...L Database]

So yes, you can restore to the 4th, 5th, or 6th day—or even to a specific minute within those days—as long as:

• The restore point is within the configured retention window.
• The database has not been deleted beyond the retention period.
• You are not trying to overwrite an existing database (PITR always creates a new database).

Thanks,
Vrishabh