In one of my previous posts I had talked about creating or rather restoring a deleted database backup. There is another option in preview called the automated export which allows you to create an automated export copy of your Azure SQL Database. The Azure SQL Database Import/Export Service is a REST-based web service that runs in every Microsoft Azure data center. The service provides a free request queuing service and a free Compute service to perform imports and exports from a Microsoft Azure SQL database to Microsoft Azure binary large object (BLOB) storage. The import and export operations are not a traditional physical database backup but a logical backup of the database that uses a special BACPAC format. This logical BACPAC format lets you avoid having to use a physical format that might vary between versions of SQL Server and SQL Database. Therefore, you can use it to safely restore the database to an SQL database and also to a SQL Server database.

As you can see from the screenshot, the Configure tab in the Azure Management portal for my Azure SQL Database, megatron, has an Export Status set to None.

 

As usual, this is a PREVIEW feature and the above is true as of today when I am writing this post. This functionality could change in the future.

Read on to find out what happens when I change the setting to Automatic… Is is as easy as 1-2-3?

If you select Automatic, this database will be exported based on the start date and the frequency that you specify. If you specify None, no automatic exports will occur. If you see Screenshot 2, you will find that you need to provide inputs for the following:

• Storage Account – This is the Azure Storage account where you export will reside
• Frequency – The frequency with which the backup will occur. This is currently limited to days
• Start date and time – The frequency of your database exports. Database export operations create a temporary database copy, and use network and storage resources, so they may incur significant cost. This is an important point to note because depending on the tier/edition of your Azure SQL Database, this would attract additional cost especially if you are testing this against the Premium tiers. In the UI, you cannot select anything that between the 30th and the 60th minute. Eg. You cannot schedule this at 12:45am. It either has to be 12:30AM or 1AM.
• Retention – After the specified number of days, export files will be deleted from your storage account. A longer retention time will result in more costs for your storage account. The retention period should be longer than the frequency of exports, or there may be periods of time when you do not have any export files.
• Always keep at least one export file – If this option is checked, then at least one export indefinitely regardless of the retention period.
• Server Login Name – This is the account that will be used to perform the export. Note that the server account must be a server-level principal login – created by the provisioning process – or a member of the dbmanager database role.
• Password – Shhh.. That’s a secret!

Although the export will begin at the specified time, it can take a while to complete the operation.

The Azure SQL Database Import/Export Service provides a limited number of Compute virtual machines (VMs) per region to process the import and export operations. The Compute VM is hosted per region to make sure that the import or export avoids cross-region bandwidth delays and charges. If too many requests are made at the same time in the same region, significant delays occur in processing the operations. The time that is required to complete requests can vary from a few seconds to many hours. If a request is not processed within 12 hours, the service automatically cancels the request.

See the KB Article mentioned below for more details.

Once you click on Save and the operation completes successfully, you will be greeted by the messages shown below in Screenshot 3

The Automated Export feature first makes a database copy of your original database to make sure that the export is transactionally consistent. Then, the Automated Export service queues an export of the database copy by using the Microsoft Azure SQL Database Import/Export REST service API. After the export is complete, the database copy is deleted. Your Azure account will attract additional database charges for the database copy.

Once your schedule is hit, the Azure Export feature will create a .bacpac file in your storage account. Once you have the bacpac files, you will have the option of creating a database from that .bacpac. So this provides you the option of restoring from .bacpac file (see Screenshot 4). It could a little unintuitive as the New Database text show in the screenshot is actually a button!

The next question is… Do you have to go to the storage container to check if the .bacpac files have been created! No. In the Management Portal, click on the database and the dashboard will show you the recent export status. In Screenshot 5, you will see that the AUTOMATED EXPORT status for my database shows as NEVER EXPORTED. This will change once a successful export is done.

After the export completes which was ultimately scheduled at 1AM, completed after 5 minutes and the automated export status in my dashboard shows that the same. (See screenshot 6).

What is the container name?

The Azure storage container name is “automated-sql-export“! This is created automatically as you only specify the storage account in the Wizard during the configuration.

What is the .bacpac file name?

The .bacpac file name will have the following naming convention: <servername>-<database name>-yyyymmddThhmmssZ.bacpac. The time is a GMT time, so if you do the necessary offset calculation, you will get the local time when the export was done.

Does the storage account and the Azure SQL Database Server have to be in the same location?

No. But it is advisable to create the storage account in the location where you want to perform the restore. For the above example, my database server was in South Central US but the export was done in a storage account in SoutEast Asia. Data sent between regions will be counted as billable bandwidth usage. So the location of the Storage Account should be done carefully as there is a cost factor associated with it.

References:

Azure SQL Database Import/Export Service takes a long time to import or export a database
http://support.microsoft.com/kb/2965554 

How to: Import and Export a Database (Azure SQL Database)
http://msdn.microsoft.com/en-us/library/hh335292.aspx