Restoring databases in event server gets deleted

Question

Since backups are tied to Azure SQL Database / Managed Instance servers, what are my options to restore my data if a server gets deleted (deliberate, by accident, etc.)?

Are LTR backups from one server accessible to a new server that I create? If not, then is my only option, besides HADR, to write manual .bacpacs or copy-only .baks to Azure Blob Storage?

Can I restore these backups to on-prem instance (using correct version SQL 2019/2022) or will it complain that it is not compatible?

Answer 1

Hi, @BW7066 Welcome to the Microsoft Q&A forum, Thanks for posting your question.
As I understand the ask here is you want to know the options to restore your data if a server gets deleted correct me if this is not accurate.

LRT backups are preserved even when you delete the databases.

Each backup will be kept in long-term storage according to the policy parameter that is configured when the LTR backup is created. For instance, if you configure the following policy for LTR:

W=12, M=0, Y=0
This means that Each weekly full backup will be kept for 12 weeks.

To List The LTR backups from a deleted database you can use the following Powershell script:

list LTR backups only from live databases (you have option to choose All/Live/Deleted)
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -DatabaseState Deleted

I have restored LTR backups to a new logical server in azure SQL DB when the server was deleted with no issue. It should work the same in MI as well, You can do it from Azure portal/ PowerShell command check here Azure SQL Managed Instance long-term backup retention - Azure SQL Managed Instance | Microsoft Learn

Please let me know for any additional questions.

Regards
Geetha