I have decided to do a summary blog post on backup and recovery options for Azure SQL Database. If you have a DBA background, you will know the importance of securing backups for your SQL Server databases. We have many options to do such a thing; being full, differential, log and filegroup backups. The common question that is asked is how do you backup (and restore) Azure SQL Databases? Or, what are the options available?

Azure Built-In Backups

Overview

When it comes to backups the answer is simple, you don’t! One of the key benefits of Azure SQL Database is the fact that it has built-in backups. Microsoft’s techniques are very thorough, they use full database backups weekly, differential database backups hourly, and transaction log backups every five – ten minutes. For added availability the full and differential backups are also replicated to a paired data center just in case there is an outage at primary data center.

If you are confused as to a paired data center means, then some call it a paired region. For the UK the regions are UK West and UK South.

Your backup retention rates and point in time recovery ability depends on your database service level. Retention is for 35 days for databases in the Standard and Premium service tiers and 7 days for databases in the Basic service tier. Azure takes care of your backups, so the key point to reinforce here, you cannot (or need not) issue classic commands such as: BACKUP DATABASE [TestDB] TO DISK = ‘\\Your Location\Name.bak’ WITH CHECKSUM, STATS = 10

Once again freeing you as the DBA to carry out other activities.

Long-term retention

If you have a business requirement which has a need to retain database backups for longer than 35 days, then you have an option to use long-term backup retention. This feature utilises the Azure Recovery Services Vault where you can store up to 10 years’ worth of backups for up to 1000 databases per vault and 25 vaults per subscription.

There are some guidelines that you need to follow to successful set this up:

• Your vault MUST be in the same region, subscription and resource group as your logical SQL Server, if not then you will not be able to set this up.
• Register the vault to the server.
• Create a protection policy.
• Apply the above policy to the databases that require long-term backup retention.

BACPACs

I have mentioned before that it really isn’t possible to create traditional backups for Azure SQL Database, the closet thing we have is an export of the database into a BACPAC file. This sort of file contains the metadata and data for the database. Now, this is definitely not a replacement for the built-in backup system more so a supplement. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database. Quite simply best practice should be followed which is summarised by the screen shot below.

Let’s go through this together.

Step 1: Create the database copy vis T-SQL.

-- Execute on the master database
CREATE DATABASE [AVTEST02] AS COPY OF [AVTEST01];

Step 2: Export Copy command example syntax. (Note the Administrator Login and password are the credentials for the SQL Server). This is best done via PowerShell.

New-AzureRmSqlDatabaseExport -ResourceGroupName "RG01" -ServerName "Server01" -DatabaseName "Database01" -StorageKeyType "StorageAccessKey" -StorageKey "StorageKey01" -StorageUri "http://youraccount/bacpacs/database01.bacpac" -AdministratorLogin "User" -AdministratorLoginPassword "secure password"

While step 2 is running you will be given an operation status link which you can pass into the following cmdlet to allow you to understand the status of the request. For example:

Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink https://management.contoso.com/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/resource01/providers/Microsoft.Sql/servers/server01/databases/database01/importExportOperationResults/00000000-000-0000-0000-000000000000?api-version=2014-04-01

Step 3: Simply remove the copy once the bacpac has been produced.

-- Execute on the master database.
DROP DATABASE [AVTEST02]

Restoring

The following table gives a great overview of the 3 main capabilities of Azure SQL Database recovery in terms of estimated time recovery (ERT) and recovery point objective (RPO).

Lets concentrate on the 2 more common types of recovery, point in time recovery and geo-restore.

Point in Time

Point in time restoring is easy to do and very much possible via the Azure portal. Looking at the above table it is easy to see that for a basic database you can restore anytime back to 7 days whereas the other versions you have the ability to go back to 35 days. All you need to do is select a restore point (UTC). No code needs to be written.

Geo-Restore

If in the rare situation your database becomes unavailable due to a regional issue you have the ability to use a geo-replicated copy of a backup. The only issue with this strategy is that you would have to allow for up to 1-hour worth of data loss.

With any form of recovery, especially geo-restore you have to make sure that you have the correct connection strings in place and the correct server level / database level firewalls else your application will not be able to connect to the newly restored database.

Hopefully things are slightly clearer now.

References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups