Restoring backups from a Standard tier DTU based Azure SQL Database

Question

I am testing an environment. I have SQL server (serverless) and database using standard DTU model in UK south region, as it's a very small database. I set the backup storage to globally redundant.

It seems that when I try to create a database in UK West using the backups, I cannot see any listed, but I do see them via the command in SSMS : SELECT * FROM sys.dm_database_backups ORDER BY backup_finish_date DESC;

I also see them under the SQL server in Azure, however there I can only restore the backups to the same server, which doesn't solve my issue, as I am looking at recovery options here (and I would prefer not to pay for an active replica).

Why might I not see the backups?

Answer 1

Hi @Tim Herman,

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

As I understand it, you're facing an issue with the visibility of your globally redundant backups when trying to restore them to a different region. Here are a few troubleshooting steps:

• Ensure that your backup storage redundancy is correctly configured to be geo-redundant. This setting ensures that your backups are replicated to a paired region, allowing for cross-region restores.
• Even with geo-redundant storage, there might be a delay in the replication process. This could cause backups to not immediately appear in the target region. Check if the backups appear after some time.
• The Azure portal might show different options compared to SSMS. While SSMS can list all backups, the Azure portal might restrict restore options to the same server for simplicity. Using PowerShell or Azure CLI might provide more flexibility for cross-region restores.
• Ensure that you have the necessary permissions to access and restore backups in the target region. Sometimes, access issues can prevent visibility of backups.
• Verify that your backup retention policies are correctly set to retain backups for the desired period. If backups are not retained long enough, they might not be available for restore.

For more information, please refer the documents: https://techcommunity.microsoft.com/blog/azuresqlblog/configuring-backup-storage-redundancy-in-azure-sql/1554322

https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.