Reverting Azure Sql Databases

Once in a while, you may find that you need to roll your Azure Sql Database back to a previous state. Unfortunately, even though Azure takes care of backups for you, there isn't a lot of information on how to restore a database to an earlier state.

For instance, you're not able to do the following through the Azure Portal:

  • Restore a point in time backup over the original
  • Rename a database

Instead you need to use a mixture of the Azure portal and Sql Server Management studio. If you follow the steps below, you'll be back up and running in no time:

Step 1:

Restore the database in question to the same server using the point in time restore functionality in the new portal.

Step 2:

Rename the original database to something else using the following script on the server's master database:

ALTER DATABASE [my_database] MODIFY NAME = [my_database_old];  

Step 3:

Rename the newly restored database to the original name using the same script as above (also on the server's master database):

ALTER DATABASE [my_database_restore] MODIFY NAME = [my_database]  

A special mention should go to the newly released SQL Server 2016 management studio which sometimes has trouble connecting to servers and / or running commands. If you find yourself in this situation, a good old computer restart should get you back on your feet.

Author image
About Jon Leigh
London Website

Hi, I'm Jon. I'm part of the Engineering team at Moneybox, a London based Fintech startup helping people to save and invest.

In my spare time, I've created CompareVino, a UK supermarket wine comparison website. If you like wine and buy it from a supermarket, you should definitely check it out.

If you want to get in touch, send me a message via Twitter