6

According to this article, an Azure SQL backup can be used to "Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world."

My question is this. In the event the server my database is sitting on fails how would I be able to access the most recent backup to restore it to another server? For example, if I wanted to restore a copy of my database now, I would select my database and then the Restore option from the menu at the top of the page. However, if I've lost my server (and therefore my database), how do I get access to the backup(s) of the database(s) that sat on the server that was lost?

Improve this question

3 Answers 3

Reset to default
4

I think the key to understanding that sentence is what I have marked in bold:

Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world.

See the documentation on Geo restore:

Geo-restore is the default recovery option when your database is unavailable because of an incident in the region where the database is hosted. If a large-scale incident in a region results in unavailability of your database application, you can restore a database from the geo-replicated backups to a server in any other region. There is a delay between when a differential backup is taken and when it is geo-replicated to an Azure blob in a different region. This delay can be up to an hour, so, if a disaster occurs, there can be up to one hour data loss.

The difference between this and a regular restore is that there is a bigger latency and possibility of data loss. You also can't do a point in time restore.

So when you restore the backup from an existing database through the azure portal you have access to the backups on that same location.

Microsoft is replicating those backups to azure blobs in different regions in case some serious incident happens in a region.

In order to restore you would add a new database in your portal and select "backup" as a source:

To geo-restore a database during its retention period using the Azure portal, open the SQL Databases page and then click Add. In the Select source text box, select Backup. Specify the backup from which to perform the recovery in the region and on the server of your choice.

So in case your entire database and data center is gone, you have the option to create a new database in another data center, and you can use the latest geographically replicated backup (not the latest backup) as a source.

Improve this answer
2

In the event the server my database is sitting on fails how would I be able to access the most recent backup to restore it to another server?

Your option is following unless you had Geo-Replicaiton configured.

A new database on any logical server in any region recovered to the point of the most recent daily backups in geo-replicated blob storage (RA-GRS).

I have 3 logical server. 2 in the same location and one in a different location.

enter image description here

I have one database named original in server swwazuretest. enter image description here

Now I am simulating that server swwazuretest is destroyed by deleting it.

enter image description here

I can restore in either one of my 2 other remaining logical server. One in the same data center (location) and other one in another location. After you click New database, from the drop down of select source chose backup. You will see the list of available backup on the right side.

enter image description here

Once you restore in other location you might lose data. SLA is described in this document.

Overview of business continuity with Azure SQL Database

Improve this answer
4
  • You don't mention Geo-restore. Isn't this an option since Azure automatically creates backups that are geo-replicated?
    – Randy Minder
    Oct 16, 2017 at 16:33
  • I mentioned Your option is following unless you had Geo-Replicaiton configured.
    – SqlWorldWide
    Oct 16, 2017 at 16:39
  • Geo-Replication of databases is much different than Geo-Replication of database backup files, which Azure does automatically, without configuration.
    – Randy Minder
    Oct 16, 2017 at 16:41
  • Agree and that is why I mentioned you can restore in both of the available server. One in the same location and other one in South Central US. Because your backup is automatically copied to other locations for redundancy.
    – SqlWorldWide
    Oct 16, 2017 at 16:43
1

The backups that form that restore option are not something you have direct access to. The data for your logical SQL server's active databases are locally replicated to if a node dies you get switched onto another with little or no service or data loss. The backups are further duplicated so if restoring your database directly on another node is not possible they can restore from those unless a large chunk of the DC is taken out at the same time.

We recently experienced something like this: no data was lost but the performance of our applications dropped markedly while everyone was transferred off the affected nodes and there was a short period of complete downtime at the end.

If you need better protection than that then you either need to pay for wider replication or (cheaper but less ideal) take your own backups by regularly exporting the database to an Azure storage account (in another location) or storage solution away from Azure.

Improve this answer
3
  • If I understand your answer correctly, it seems to contradict what Microsoft has stated in their documentation, that backups can be used to restore to another region in the event I cannot access my server due to a geographic disaster. If I cannot access my server, how am I able to access my backups for the databases that were on that server in order to restore to another region?
    – Randy Minder
    Oct 16, 2017 at 13:43
  • @RandyMinder - A GeoRestore will allow you to restore the database in another location if enough infrastructure is hit at the original location that the logical server is down but the backups are still available, but my understanding is that the backups are not geo-replicated by default (they might be replicated between DCs in their current region) so if enough of the source region is down/uncontactable you are stuck. Happy to admit that I could be wrong though, I'll have to scan the relevant documentation when I have time to be sure either way. Pointers appreciated if you have them!
    – David Spillett
    Oct 16, 2017 at 16:11
  • It appears, based on this article (learn.microsoft.com/en-us/azure/sql-database/…), very first sentence, that database backups are geo-replicated. So I now have an idea of how I would perform a backup in this sort of scenario.
    – Randy Minder
    Oct 16, 2017 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.