In the article Learning from Expertise #2: Who Dropped my Azure SQL DB? - Microsoft Tech Community we have explored various solutions to know, secure, protect, recover, audit and monitor Azure SQL DB against unintended deletion.
Today, we will highlight on another unappealing situation when customer 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.
You might need as well to submit the answers of these inquiries to Microsoft support to expedite the recovery process (if it is possible):
• Region (e.g. West Europe).
• Server Name
• Is this a Production server? Yes /No.
• Was the server dropped by itself? Or did you attempt to drop the resource group which resulted in force drop of the server?
• Approximate date and time that the server was dropped?
• Have you tried to re-create the server with the same name after the original server was dropped? (Yes /No) If yes, for future occurrences, you should avoid re-creating server with same name.
• Provide the business justification for the requested restore.
• Request Approval from the Subscription Owner (if you are not the owner) for any restore operation of dropped server or database.
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