In the article Learning from Expertise #2: Who Dropped my Azure SQL DB? - Microsoft Tech Community we have explored various solution to know, secure, protect, recover, audit and monitor Azure SQL DB against unintended deletion.
Today, we will highlight on another unappealing situation when we see customer who accidentally deleted the SQL DB Server which ultimately deleting the underlying databases as well. This is a scenario commonly hit because of automation tools such as Terraform.
It's very important to note that: - Restore of a dropped server is not an officially supported scenario, and any attempt to recover will be laid under a best effort trial to recover the server and databases.
First Recommendation
*Do not* recreate the server again with the same name in case you want to restore the dropped the server and try to contact Microsoft support the soonest the possible.
Additional precautionary measures:
The following recommendations can help you to recover from these unintentional scenarios by either preventing it or restoring the important data whenever needed:
1. Implement resource lock to avoid accidental changes in Azure resources. you can lock at different levels like subscription, resource group, or resource to prevent other users in your organization from accidentally deleting or modifying critical resources. You can find more information, see: Lock resources to prevent changes - Azure Resource Manager | Microsoft Docs
2. Enable Long Term Backup Retention (LTR)
This feature allows users to configure a single or a pooled database with a long-term backup retention policy (LTR) to automatically retain the database backups in separate Azure Blob storage containers for up to 10 years and recover database using these backups via Azure portal or PowerShell. LTR backups are completely independent and cannot be impacted by server drop. For more information, see:
Long-term backup retention - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs
Azure SQL Database: Manage long-term backup retention - Azure SQL Database | Microsoft Docs
3. You can export the latest copy of the database into a storage account before deleting the database. Export the database to BACPAC File can be done through various tools like Azure Portal, SQLPackage, SSMS and powershell. More information can be found in:
Also, you can leverage Azure automation to automate the database export, you can find the detailed steps handy at my colleague @Mohamed_Baioumy_MSFT's blog: How to automate Export Azure SQL DB to blob storage use Automation account - Microsoft Tech Community
I hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.
Ahmed S. Mazrouh
Posted at https://sl.advdat.com/3y5mupa