Azure SQL Database Restore from PITR but full backup expired

Hi there,

I have noticed that PITR backups in Azure SQL and Azure Managed Instance do not maintain the full backup necessary to restore the database. As a result, even though the retention period is set to 7 days, there are days when the full backup is not available, making it impossible to restore the database to certain points in time. For example, I have an Azure SQL Database where the full backup is taken every Friday. If I want to restore the database to a point in time from the previous Tuesday, I can't because only the log backups are available and not the full backup.

Do you have any plans to fix this issue in Azure?

ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee

2023-04-03T09:25:47.54+00:00
Hi Nuria Arévalo Martínez-Raposo •,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand, you are having trouble in getting Azure SQL DB restored from PITR bcakup.

Several factors affect the recovery time to restore a database through automated database backups:

The size of the database

The compute size of the database

The number of transaction logs involved

The amount of activity that needs to be replayed to recover to the restore point

The network bandwidth if the restore is to a different region

The number of concurrent restore requests that are processed in the target region. For a large or very active database, the restore might take several hours. A prolonged outage in a region might cause a high number of geo-restore requests for disaster recovery. When there are many requests, the recovery time for individual databases can increase. Most database restores finish in less than 12 hours.

Note: Long-term retention is not applicable to Hyperscale databases.

Could you please let us know the configuration details of the Azure SQL Database which is in use?

1 answer

Rahul Randive 9,176 Reputation points Microsoft Employee

2023-04-03T11:48:38.0166667+00:00

Thanks for your question.

Could you please provide a screenshot or error details to help us understand where your see "full backup has expired"

All Basic, Standard, and Premium databases are protected by automatic backups. Full backups are taken every week, differential backups every day, and log backups every 5 minutes.

As per blog https://azure.microsoft.com/en-us/blog/azure-sql-database-point-in-time-restore/

For basic tier you can perform restore 'any restore point within 7 days' and for Standard & Premium, 'any restore point within 35 days'

If you are seeing no restore point available, kindly refer below blog for more information.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/learning-from-expertise-10-why-no-restore-point-available/ba-p/3482699

Thank you!
Please sign in to rate this answer.
Nuria Arévalo Martínez-Raposo 0 Reputation points

2023-04-04T05:59:16.2266667+00:00

Hi!,

Thank you for your responses!

I am familiar with how Azure SQL Database PITR works, but what I was mentioning is that I have encountered errors while attempting to restore databases to a specific point in time.

Upon investigating the issue, I have noticed in the sys.dm_database_backups view that a recent full backup has been taken, and the previous full backup is shown as out of retention. Through testing, I have found that I can only restore the database to a point in time after the last backup taken yesterday, despite the retention period being set for 7 days. If I attempt to restore to a point before yesterday's full backup, the implementation runs for a long time and ultimately fails with an error:

"statusMessage": "{"status":"Failed","error":{"code":"ResourceOperationFailure","message":"The resource operation completed with terminal provisioning state 'Failed'.","details":[{"code":"InternalServerError","message":"An unexpected error occured while processing the request. Tracking ID: '31915f09-77d9-42b9-8705-6d62103cd23b'"}]}}",

I am attaching a screenshot of the sys.dm_database_backups view with the full backup shown as out of retention, which means that the subsequent log backups are useless until the full backup taken yesterday.

ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee

2023-04-04T08:39:30.6266667+00:00

Hi Nuria Arévalo Martínez-Raposo •, This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Sign in to comment

Share via

Azure SQL Database Restore from PITR but full backup expired

1 answer