Automated Export Workaround

It may happen that the Automated Export feature is improperly working or not working at all.
The procedure described in this article can be used as a temporary workaround to schedule and run export of a specific database.

DISCLAIMER

This is a sample workaround and not the solution for the issue, since it just simulates what the automated export does.
It is important for you to know that Microsoft does not provide any warranty on this procedure.
You should review this procedure, customize it if required and test it before applying it on your environment.
Use the procedure at your own risk.

How it works

A batch file will execute the following operations:

1. Create a copy of the source database by using the TSQL command
   “CREATE DATABASE AS COPY OF”. Note: This step uses PowerShell.
2. Export the copied database using Import/Export REST Endpoints.
   Note: This step will use a .Net command line application based on the sample application shown in the following article:
   Windows Azure SQL Database Import/Export Service Community Sample Application
3. Drop the copy after the Export completed (still uses PowerShell). 

KNOWN ISSUES

• There is no retry-logic implementation in this first version. 
• If a .bacpac with the same name already exists in the destination storage account, step 2 will fail.
• There is no check-logic between each steps.
  I.e.: if the step 1 fails, the step 2 will be executed anyway.

Where is it?

The procedure is packed into a zip file available from this blog article. See below.

PACKAGE CONTENTS:

• Copy_db.ps1:   PowerShell script for creating the transitionally consistent copy (step 1)
• WASDImportExport.exe and related dlls: the export application which uses public API (step 2)
• Drop_db.ps1: the PowerShell script for dropping the copied db (step 3)
• Run.bat: the batch file that will run the 3 steps. 
• The batch file is fully configurable with all the requested parameters:
  •  ServerName
  •  UserName
  •  Password
  •  StorageName
  •  StorageContainer
  •  StorageKey
  •  EndopintURI
• There is a 5-minute time delay between step 1 and 2 and a 30-second time delay between step 2 and 3, in order to allow all asynchronous actions to be completed.
• WASD ImportExport Service Community Sample Application Edited.zip: this zip contains the Visual Studio Solution with the source code of the export app used in the batch  

WHAT TO DO?

1. You can replicate the batch file for each database you would need to export. 
2. Configure each batch file providing the source database name and the copied database name + credentials for Azure SQL Db + storage account details. 
3. You can schedule all the batches from any client machine which has Internet access  + SQL Native Client. You could use the Windows Task Scheduler (taskschd.msc). Alternatively, you could create a temporary Azure Virtual Machine with Basic size to schedule the above task. 

References

The above procedure is based on information provided in this article:

• How to: Use the Import and Export Service in Azure SQL Database, https://msdn.microsoft.com/en-us/library/azure/hh335292.aspx
• Windows Azure SQL Database Import Export Code Examples, https://sqldacexamples.codeplex.com/
• Automate tasks with Task Scheduler, https://windows.microsoft.com/en-us/windows-vista/Automate-tasks-with-Task-Scheduler-from-Windows-Vista-Inside-Out

 

Have a good time Exporting!

Rossano