Azure SQL Restore from Long Term Retention

Craig vN 0

I am trying to restore a database from long term retentions. I can't see the DB listed in my restore values, what are the options?

Vijayalaxmi Kattimani 1,485 Reputation points Microsoft External Staff

2025-02-28T01:48:59.4+00:00
Hi Craig vN,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are unable to see your Azure SQL Database listed for Restore from long-term retention (LTR) backups.

Please follow the below mentioned troubleshooting steps to resolve the issue:

If your database is not listed for restoration, ensure that long-term retention (LTR) is properly configured for that database. You can check the available LTR backups under the "Manage" section in the Azure portal, and verify that the retention policy is set correctly for the desired backup period. Go to Azure Portal → Your SQL Server → Backups. Under Manage Backups, check if Long-term Retention is enabled for the database.

You can restore a database from LTR using the Azure portal or PowerShell. When a restore request is initiated in the final 7 days of the LTR retention period, Azure will automatically extend the expiration date of all backups by 7 days to prevent an LTR backup from expiring during the restore.

If the database is not listed under "Deleted databases," you can still recover it if it's within the PITR retention period. In the Azure Portal, go to the SQL Server that hosted the database, click on "Backups" in the SQL Server menu, select "Point-in-time restore" for the deleted database, choose the time to restore the database to, and specify a name for the new database.

If you are trying to perform a restore using the REST API, make sure to use the 'Create or Update' endpoint and check the createmode option for restoring from LTR.

If the issue still persists, please let us know here. We will respond with more details and try to help you.

Please refer to the below mentioned link for more information.

https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview?view=azuresql

I hope this information helps. Please do let us know if you have any further queries.
Craig vN 0 Reputation points

2025-02-28T03:19:55.57+00:00

Can you look at these retention settings I have. I am basically trying to restore a database from two weeks ago.
Craig vN 0 Reputation points

2025-02-28T03:36:14.6766667+00:00

Hi

This is my current retention policy configuration. I am trying to get a backup from two weeks ago. Can you tell me how to do that?
TP 107K Reputation points

2025-02-28T04:08:25.2566667+00:00
@Craig vN Please follow instructions below (except from Manage Azure SQL Database long-term retention article):

In the Azure portal, navigate to your server and then select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane appears with a list of the available LTR backups for the selected database.

In the Available LTR backups pane that appears, review the available backups. Select a backup to restore from.

To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.

Choose a name for your new database, then select Review + Create to review the details of your Restore. Select Create to restore your database from the chosen backup.
Craig vN 0 Reputation points

2025-02-28T06:53:06.9+00:00

The only ones visible are here. It is not showing any previous weekly ones.
TP 107K Reputation points

2025-02-28T10:48:47.6533333+00:00

The only ones visible are here. It is not showing any previous weekly ones

Looking at your retention policy, you set it to only keep weekly, monthly, and yearly LTR backups for one week. What this means is, your LTR backups will only remain for 1 week and then they are automatically deleted.For example, from your latest screenshot, there was a weekly LTR backup on 2025-02-22 (last Saturday), but it will be automatically deleted on 2025-03-01 when your next weekly LTR backup runs. This explains why you are only seeing one LTR backup (besides an old backup that had different retention policy).

Unfortunately I don't see how you will be able to restore a backup from 2 weeks ago, unless you happened to export to bacpac file 2 weeks ago.

For the future I recommend you change your policy so that yours backups will be retained for a longer time period.

1 answer

PratikLad 85 Reputation points Microsoft External Staff

2025-03-03T10:13:39.4733333+00:00

In addition to TP response, you can also consider...

As per your current retention policy configuration. I can see the at you have configured the retention for weekly, monthly, and yearly LTR backups for one week. It means your backup will be available in azure only for 1 week after that it will be deleted automatically. These are the reasons you are seeing only one LTR backup for the week as the retention period for old backup is expired the backups are also got deleted.

To avoid these scenarios in Future You can reconfigure the backup policies and increase the retention periods. here in below image for example I have kept it for 4 weeks

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure SQL Restore from Long Term Retention

1 answer

Your answer