Restoring a Database in Azure

One of the many small things you don’t have to worry about any more when working with Azure SQL Database are those pesky backups… Ha-ha-ha! Yeah, I can’t keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you’re working in the Platform as a Service offering of Azure SQL Database. If that doesn’t scare you, I’m not sure what will. However, there’s good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft’s backups actually work. Testing it out is easy. Let’s walk through it once.

I’m going to assume you have an Azure account on which you already have Azure SQL Databases created. If you navigate to one of your databases, the screen, in the new portal, looks something like this:

If you look at the tool bar at the top, you can see the command for “Restore.” Clicking on that opens another window:

The first, and most important thing to notice here is that it’s supplying me with a new name. I can change that to anything I want as long as it’s not the name of a database already in existence on my Azure SQL Database Server. You read that correctly, you can restore a database to Azure SQL Database, but there is no WITH REPLACE option. The restore creates a new database. This is important. In a recovery scenario, you need to be sure that you’re prepared to deal with this fact. How could you replace the existing database? Immediately run a pair of ALTER DATABASE commands to change the name of the existing database to something else and then change the name of your newly created database to the old name. That’s your choice.

The other options and information are pretty clear. You can decide on the server you’d like to restore to, allowing you to move or copy a database to another server. You get the current date and time. More importantly, you get the oldest restore point. This is determined by the service tier that this database is currently defined on. Different service tiers have different SLAs from Microsoft on how much backups are kept around. At the bottom you can pick the date, within the range of backups you have available, as well as the hour and minute. At the bottom of the screen, not shown, you can then click Create. That’s right, Create, not Restore. Why? Because, as I already said, the restore operation is creating a new database.

I’ve tested this a number of times with databases from various tiers. It works and works well. I will caution you on another point. Let’s say you want to restore just, say, a couple of rows of data or a table, not the entire database. You’re going to have to get imaginative with how you deal with that because, let’s say I run this query:

SELECT  *
FROM    [CruiserDirectory_2016-01-07T09-04Z].dbo.table3;

The results I get are an error:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in ‘CruiserDirectory_2016-01-07T09-04Z.dbo.table3’ is not supported in this version of SQL Server.

Because, you can’t do cross-database queries in Azure SQL Database.

My best suggestion here, take a look at a third party solution that allows you to compare the data between two databases, even in Azure, and then provides you with the ability to move those rows or tables that you need. Hint, hint, nudge, nudge, wink, wink, SAY NO MORE! You can then capture the data from one database and move it into the other pretty easily.

That’s it. Azure SQL Database does provide a very simple mechanism for getting at your backups that are automatically created for you. That’s pretty cool.