Programmatically export Azure SQL as DACPAC to Blob Storage

Question

I would like to perform a scheduled task of exporting an Azure SQL database as DACPAC to the Blob Storage. I would like to know can I do this. Web Job? Powershell script?

Answer 1

We also can do this with WebJob. I create a demo with Microsoft.Azure.Management.Sql -Pre .Net SDK,and it works successfully for me. More information about how to deploy webjob and create scheduled job please refer to the following documents.

creating-and-deploying-microsoft-azure-webjobs

create-a-scheduled-webjob-using-a-cron-expression

The following is my detail steps and sample code:

Prerequisites:

Registry an App in Azure AD and create service principle for it. More detail steps about how to registry app and get access token please refer to document.

Steps:

1.Create a C# console Application

2.Get accessToken by using registry App in Azure AD

public static string GetAccessToken(string tenantId, string clientId, string secretKey)
 {

    var clientCredential = new ClientCredential(clientId, secretKey);
    var context = new AuthenticationContext("https://login.windows.net/" + tenantId);
    var accessToken = context.AcquireTokenAsync("https://management.azure.com/", clientCredential).Result;
    return accessToken.AccessToken;
  }

3.Create Azure sqlManagementClient object

  SqlManagementClient sqlManagementClient = new SqlManagementClient(new TokenCloudCredentials(subscriptionId, GetAccessToken(tenantId,clientId, secretKey)));

4.Use sqlManagementClient.ImportExport.Export to export .dacpac file to azure storage

var export = sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,   
                    exportRequestParameters)

5. Go the the Bin/Debug path of the Application and Add all the contents in a .zip file.

6. Add the webjob from the Azure portal

7. Check the webjob log from the kudu tool

8. Check the backup file from the azure storage.

SDK info please refer to the Package.config file.

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Hyak.Common" version="1.0.2" targetFramework="net452" />
  <package id="Microsoft.Azure.Common" version="2.1.0" targetFramework="net452" />
  <package id="Microsoft.Azure.Common.Dependencies" version="1.0.0" targetFramework="net452" />
  <package id="Microsoft.Azure.Management.Sql" version="0.51.0-prerelease" targetFramework="net452" />
  <package id="Microsoft.Bcl" version="1.1.9" targetFramework="net452" />
  <package id="Microsoft.Bcl.Async" version="1.0.168" targetFramework="net452" />
  <package id="Microsoft.Bcl.Build" version="1.0.14" targetFramework="net452" />
  <package id="Microsoft.IdentityModel.Clients.ActiveDirectory" version="2.28.3" targetFramework="net452" />
  <package id="Microsoft.Net.Http" version="2.2.22" targetFramework="net452" />
  <package id="Microsoft.Web.WebJobs.Publish" version="1.0.12" targetFramework="net452" />
  <package id="Newtonsoft.Json" version="6.0.4" targetFramework="net452" />
</packages>

Demo code:

 static void Main(string[] args)
        {
            var subscriptionId = "Your Subscription Id";
            var clientId = "Your Application Id";
            var tenantId = "tenant Id";
            var secretKey = "secretkey";
            var azureSqlDatabase = "Azure SQL Database Name";
            var resourceGroup = "Resource Group of Azure Sql ";
            var azureSqlServer = "Azure Sql Server";
            var adminLogin = "Azure SQL admin login";
            var adminPassword = "Azure SQL admin password";
            var storageKey = "Azure storage Account Key";
            var baseStorageUri = "Azure storage URi";//with container name endwith "/"
            var backName = azureSqlDatabase + "-" + $"{DateTime.UtcNow:yyyyMMddHHmm}" + ".bacpac";  //back up sql file name
            var backupUrl = baseStorageUri + backName;
            ImportExportOperationStatusResponse exportStatus = new ImportExportOperationStatusResponse();
            try
            {
                ExportRequestParameters exportRequestParameters = new ExportRequestParameters
                {
                    AdministratorLogin = adminLogin,
                    AdministratorLoginPassword = adminPassword,
                    StorageKey = storageKey,
                    StorageKeyType = "StorageAccessKey",
                    StorageUri = new Uri(backupUrl)
                };

                SqlManagementClient sqlManagementClient = new SqlManagementClient(new TokenCloudCredentials(subscriptionId, GetAccessToken(tenantId,clientId, secretKey)));
                var export = sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,   
                    exportRequestParameters); //do export operation

                while (exportStatus.Status != OperationStatus.Succeeded) // until operation successed
                {
                    Thread.Sleep(1000 * 60);
                    exportStatus = sqlManagementClient.ImportExport.GetImportExportOperationStatus(export.OperationStatusLink);  
                }

               Console.WriteLine($"Export DataBase {azureSqlDatabase} to Storage wxtom2 Succesfully");
            }
            catch (Exception)
            {

             //todo

            }
        }

Answer 2

Hi have you had a look at the following documentation which includes a PowerShell script and an Azure automation reference with sample script.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export-powershell