如何以编程方式直接将SQL数据库导出到Blob存储 [英] How to export SQL Database directly to blob storage programmatically

问题描述

我需要以编程方式将SQL数据库(在Azure中或兼容的一个本地部署)备份/导出到Azure存储，然后将其还原到另一个SQL数据库.我只想将NuGet包用于代码依赖性，因为我不能保证生成服务器或生产服务器都将安装Azure SDK.我找不到任何我认为将是常见动作的代码示例.我发现的最接近的是:

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:

但是，此代码将导出到本地bacpac文件(需要RoleEnvironment，这是仅SDK的对象).我认为应该有一种无需中介文件即可直接导出到Blob存储的方法.一种想法是创建一个Stream，然后运行:

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")

然后将流写入存储；但是内存流不起作用-这可能是一个庞大的数据库(100-200 GB).

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

有什么更好的方法?

推荐答案

根据我的测试，

Based on my test, the sql Microsoft Azure SQL Management Library 0.51.0-prerelease support directly export the sql database .bacpac file to the azure storage.

我们可以使用sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,exportRequestParameters)将. bacpac 文件导出到 azure存储空间.

We could using sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,exportRequestParameters) to export the .bacpac file the azure storage.

但是我们在最新版本的Microsoft Azure SQL管理库SDK中找不到ImportExport.因此，我们只能使用sql Microsoft Azure SQL管理库0.51.0-prerelease SDK.

But we couldn't find ImportExport in the lastest version of Microsoft Azure SQL Management Library SDK. So we could only use sql Microsoft Azure SQL Management Library 0.51.0-prerelease SDK.

有关如何使用sql的更多详细信息，Microsoft Azure SQL管理库将sql备份导出到azure blob存储，您可以参考以下步骤和代码.

More details about how to use sql Microsoft Azure SQL Management Library to export the sql backup to azure blob storage, you could refer to below steps and codes.

先决条件:

在Azure AD中注册一个应用程序并为其创建服务原理.有关如何注册应用程序和获取访问令牌的更多详细步骤，请参阅

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.

详细代码:

注意:用您注册的Azure AD信息替换clientId，tenantId，secretKey，subscriptionId.用您自己的sql数据库和存储替换azureSqlDatabase，resourceGroup，azureSqlServer，adminLogin，adminPassword，storageKey，storageAccount.

Notice: Replace the clientId,tenantId,secretKey,subscriptionId with your registered azure AD information. Replace the azureSqlDatabase，resourceGroup，azureSqlServer，adminLogin，adminPassword，storageKey，storageAccount with your own sql database and storage.

static void Main(string[] args)
{

    var subscriptionId = "xxxxxxxx";
    var clientId = "xxxxxxxxx";
    var tenantId = "xxxxxxxx";
    var secretKey = "xxxxx";
    var azureSqlDatabase = "data base name";
    var resourceGroup = "Resource Group name";
    var azureSqlServer = "xxxxxxx"; //testsqlserver 
    var adminLogin = "user";
    var adminPassword = "password";
    var storageKey = "storage key";
    var storageAccount = "storage account";
    var baseStorageUri = $"https://{storageAccount}.blob.core.windows.net/brandotest/";//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 Microsoft.Azure.TokenCloudCredentials(subscriptionId, GetAccessToken(tenantId, clientId, secretKey)));
        var export = sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,
                        exportRequestParameters); //do export operation

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

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

    catch (Exception exception)
    {

        //todo

    }

}

private static string GetAccessToken(string tenantId, string clientId, string secretKey)
{
    var authenticationContext = new AuthenticationContext($"https://login.windows.net/{tenantId}");
    var credential = new ClientCredential(clientId, secretKey);
    var result = authenticationContext.AcquireTokenAsync("https://management.core.windows.net/",
        credential);

    if (result == null)
    {
        throw new InvalidOperationException("Failed to obtain the JWT token");
    }

    var token = result.Result.AccessToken;
    return token;
}

结果如下:

1.发送请求以告知sql server开始导出到Azure Blob存储

1.Send request to tell sql server start exporting to azure blob storage

2.继续发送请求以监视数据库导出的操作状态.

2.Continue sending request to monitor the database exported operation status.

3.完成导出操作.

这篇关于如何以编程方式直接将SQL数据库导出到Blob存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！