Exporting an Azure SQL Databases Using .NET

It’s possible to export an Azure SQL database to a BACPAC file using the Azure portal and PowerShell among other methods but the documentation makes no mention of doing this directly in .NET, though happily this is also possible.

The process detailed below triggers a database export to a specified storage account using the Azure Management API and then downloads the resulting BACPAC file to a local folder. An example project implementing my complete solution can be found here.

This is a .NET Core 2.0 console application which uses logging and appsettings.json as previously detailed here and here.

Authorization

In order to be able to trigger the export of your Azure SQL database you first need to create an Azure Active Directory service principle and grant it the necessary rights to the Azure SQL Database and the storage account. I followed this guide to create the service principle and then added it as a contributor using the “Access control (IAM)” blade to the SQL server containing the database to be exported and the storage account where it will be exported to. When creating the service principle be sure to note down the subscription-id, application-id, authentication-key and tenant-id as these will be used to create an authorization file as detailed here.

This credentials file should then be added to your project with the “Copy to Output Directory” property set to “Copy if newer”, this can then be loaded and used to authorize Azure Management requests using the Microsoft.Azure.Management.ResourceManager.Fluent package.

AzureCredentials credentials = SdkContext.AzureCredentialsFactory.FromFile(Directory.GetCurrentDirectory() + "\\Azure_Credentials.txt");

IAzure azure = Azure
	.Configure()
	.WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
	.Authenticate(credentials)
	.WithDefaultSubscription();

Database Export

Using the Azure management object created above the target database can then be exported to your storage account.

_logger.LogInformation("Get SQL database reference");
ISqlServer sqlServer = await azure.SqlServers.GetByResourceGroupAsync(backup.Source.SqlServerResourceGroup, backup.Source.SqlServerName);

ISqlDatabase sqlDatabase = await sqlServer.Databases.GetAsync(backup.Source.SqlDatabaseName);

_logger.LogInformation("Get storage account reference");
IStorageAccount storageAccount = azure.StorageAccounts.GetByResourceGroup(backup.Destination.StorageAccountResourceGroup, backup.Destination.StorageAccountName);

_logger.LogInformation("Export database to storage account");
string blobPath = backup.Source.SqlDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd") + ".bacpac";

ISqlDatabaseImportExportResponse exportedSqlDatabase = sqlDatabase.ExportTo(storageAccount, backup.Destination.StorageContainerName, blobPath)
	.WithSqlAdministratorLoginAndPassword(backup.Source.SqlAdminUsername, backup.Source.SqlAdminPassword)
	.Execute();

Database File Download

Once the database has finished exporting to the specified storage account it can be downloaded using the Microsoft.WindowsAzure.Storage.Blob package.

_logger.LogInformation("Get reference to storage account");
CloudBlobContainer container = new CloudBlobContainer(new Uri(backup.Destination.StorageContainerConnectionString));

_logger.LogInformation("Get reference to blob");
CloudBlockBlob blockBlob = container.GetBlockBlobReference(blobPath);

_logger.LogInformation("Download blob");
if (await blockBlob.ExistsAsync())
{
	string filePath = backup.Destination.LocalDirectory + blobPath;
	await blockBlob.DownloadToFileAsync(filePath, FileMode.Create);
}
else
{
	throw new FileNotFoundException("Target blob not found in storage account");
}

if (backup.Destination.DeleteFromStorageAfterDownload)
{
	_logger.LogInformation("Removing file from storage");
	await blockBlob.DeleteAsync();
}