How to export SQL Database directly to blob storage programmatically

user_544066 0
Viewed 2226

I need to programmatically backup/export a SQL Database (either in Azure, or a compatible-one on-prem) to Azure Storage, and restore it to another SQL Database. I would like to use only NuGet packages for code dependencies, since I cannot guarantee that either the build or production servers will have the Azure SDK installed. I cannot find any code examples for something that I assume would be a common action. The closest I found was this:

https://blog.hompus.nl/2013/03/13/backup-your-azure-sql-database-to-blob-storage-using-code/

But, this code exports to a local bacpac file (requiring RoleEnvironment, an SDK-only object). I would think there should be a way to directly export to Blob Storage, without the intermediary file. One thought was to create a Stream, and then run:

services.ExportBacpac(stream, "dbnameToBackup")

And then write the stream to storage; however a Memory Stream wouldn't work--this could be a massive database (100-200 GB).

What would be a better way to do this?

azure-storage azure-sql-database c#
4 Answers

You can use Microsoft.Azure.Management.Fluent to export your database to a .bacpac file and store it in a blob. To do this, there are few things you need to do.

  1. Create an AZAD (Azure Active Directory) application and Service Principal that can access resources. Follow this link for a comprehensive guide.
  2. From the first step, you are going to need "Application (client) ID", "Client Secret", and "Tenant ID".
  3. Install Microsoft.Azure.Management.Fluent NuGet packages, and import Microsoft.Azure.Management.Fluent, Microsoft.Azure.Management.ResourceManager.Fluent, and Microsoft.Azure.Management.ResourceManager.Fluent.Authentication namespaces.
  4. Replace the placeholders in the code snippets below with proper values for your usecase.

  5. Enjoy!

        var principalClientID = "<Applicaiton (Client) ID>";
    var principalClientSecret = "<ClientSecret>";
    var principalTenantID = "<TenantID>";


    var sqlServerName = "<SQL Server Name> (without '.database.windows.net'>";
    var sqlServerResourceGroupName = "<SQL Server Resource Group>";


    var databaseName = "<Database Name>";
    var databaseLogin = "<Database Login>";
    var databasePassword = "<Database Password>";


    var storageResourceGroupName = "<Storage Resource Group>";
    var storageName = "<Storage Account>";
    var storageBlobName = "<Storage Blob Name>";


    var bacpacFileName = "myBackup.bacpac";


    var credentials = new AzureCredentialsFactory().FromServicePrincipal(principalClientID, principalClientSecret, principalTenantID, AzureEnvironment.AzureGlobalCloud);
    var azure = await Azure.Authenticate(credentials).WithDefaultSubscriptionAsync();

    var storageAccount = await azure.StorageAccounts.GetByResourceGroupAsync(storageResourceGroupName, storageName);


    var sqlServer = await azure.SqlServers.GetByResourceGroupAsync(sqlServerResourceGroupName, sqlServerName);
    var database = await sqlServer.Databases.GetAsync(databaseName);

    await database.ExportTo(storageAccount, storageBlobName, bacpacFileName)
            .WithSqlAdministratorLoginAndPassword(databaseLogin, databasePassword)
            .ExecuteAsync();
user_6848530
Related