Restoring An Azure SQL Database

Not too long ago, I had a chance to restore a database in Azure SQL Database, as someone had accidentally run an UPDATE statement without a WHERE clause.  That happens to the best of us, and it’s one of many reasons why we keep database backups around.  To correct the mistake, I had to restore a backup and run an update statement to set some values back to their prior values.  Overall, the process was pretty easy, but I figured it was worth a blog post.

First, log in to the Azure portal.  Once you’re in there, go to the SQL Databases option and select the database you want to restore.  Once you’ve chosen your database, hit the Restore button in the top bar.

Clicking that button brings up a new blade which allows you to choose a new name for your restored database.  Note that you cannot (at least as of the writing of this blog post) restore over an existing database; you can only restore to a new database.

You will need to select your restore point as well.  In this case, I decided to restore back to midnight UTC on a particular date.  Note that the dates are UTC rather than your local timezone!

After selecting your restore point, you pick the target server and can decide a couple of things.  First, you can put this database into an elastic database pool, which makes cross-database connections a lot easier.  Second, you can choose a different pricing tier.  Because I only needed this database for a few minutes, keeping it at P2 Premium was fine; the total restore time meant that we spent less than a dollar bringing the data back to its pristine condition.

Once you’ve selected the name, restore point, and database size, hit the OK button and prepare to wait.  And wait.  And wait.  Azure database restorations can be slow.  And the latest version (v12) had some issues with restoration performance, although in the comments, the Azure SQL Database Product Manager notes that they’ve improved performance times since that post.

Anyhow, it took somewhere between 5 and 10 minutes for my 40 MB (yeah just 40 MB) database to restore.  We weren’t in crisis mode or anything—we’re using Azure Redis cache, so customers were hitting the cached values during this window—but it’s an important part of the restoration process to set expectations so people have a good idea of when that server will be available (or fixed) again.

Advertisement