Point-in-time restore of Azure SQL database

July 30th 2021 Azure Microsoft SQL Server

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:

Restore an Azure SQL database in Azure Portal

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:

Configuring point-in-time-restore in Azure Portal

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:

Delete an Azure SQL database in Azure Portal

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.

Get notified when a new blog post is published (usually every Friday):

If you're looking for online one-on-one mentorship on a related topic, you can find me on Codementor.
If you need a team of experienced software engineers to help you with a project, contact us at Razum.
Copyright
Creative Commons License