Point-in-time restore of Azure SQL database
Automatic updates are configured by default for each Azure SQL database, allowing you to perform a point-in-time restore of the database to any point within the configured retention period. Although the restore process is documented, I still had some doubts when replacing the existing database with the one from the backup, so I am documenting the steps I took for future reference.
I started by restoring a backup from a specific time to a new database. In the Azure Portal, the restore functionality can be triggered from the button in the database page toolbar:
On the next page, I selected the desired restore point and kept the suggested new name for the restored database. I made sure that the selected tier for the restored database matched the one used by the existing database, since the new database was to replace the existing one:
As expected, the restore process took a while. Once it was complete, I used Azure Data Studio to connect using the same credentials as for the existing database. After making sure I had selected the correct restore point and that the data in the database met my expectations, it was time to replace the existing database with this one.
First, I deleted the existing database. In the Azure Portal, there is a button for this in the toolbar of the database page. To prevent accidental deletion, a second confirmation is required after clicking:
As a very last step, I had to rename the restored database to match the name of the database I just deleted. To do this, I connected to the master database of the SQL Server hosting my databases in Azure Data Studio using the server admin credentials. This allowed me to execute the following T-SQL query:
ALTER DATABASE [RestoredDbName] MODIFY NAME = [OriginalDbName]
As a final check, I connected to the renamed database using Azure Data Studio.
The process of restoring an Azure SQL database to a specific point in time and replacing the existing database with it is fully supported. Unfortunately, there is more documentation for Azure SQL Managed Instance than for Azure SQL databases. However, the same steps can be used for both.
