Possible to create new Database from Point In Time

问题:

Specifically looking for a way to perform point in time restore to a new Database, preserve the original database; using Managed API instead of REST API or Templates.

Code I have so far:

    public async Task CreateDatabaseFromRestorePointAsync(
       string resourceGroupName, string serverName, 
       string databaseName, DateTime time) {

        using (SqlManagementClient sqlMgmtClient = GetSqlManagementClient())
        {
            DatabaseInner myDb = await sqlMgmtClient.Databases.GetAsync(
               resourceGroupName, serverName, databaseName);            

            var newDb = new DatabaseInner
            {
                Location = myDb.Location,
                CreateMode = CreateMode.PointInTimeRestore,
                // RestorePointInTime = time; 
                Edition = "Basic",
                SourceDatabaseId = myDb.Id                   
            };

            var result = await sqlMgmtClient.Databases.CreateOrUpdateAsync(
               resourceGroupName, serverName, "BackUpRestoreTestDb", newDb);               

            // Throws "The point in time was not specified for restoring live database...'"
        }
    }

Found the following:

• This SO link How to restore an Azure SQL Database to point-in-time using Azure management libraries for .NET.

And same link references a new Database instead of a DatabaseInner. Cant find Database objects namespace.

• Databases - Create or Update
• Microsoft.Sql/servers/databases template reference.

Edit 1: Tried the restore method specifically from Brando Zhang's answer but there are apparently namespace changes. I'm using:

• .Net 4.6.1

• Microsoft.Azure.Management.Sql version 1.6.0-preview

• Microsoft.Azure.Management.Sql.Models

• Microsoft.Azure.Management.Sql.Fluent version 1.2.0

• Microsoft.Azure.Management.Sql.Fluent.Models

Getting the namespace could not be found for DatabaseCreateOrUpdateParameters, DatabaseCreateOrUpdateProperties, DatabaseCreateMode, DatabaseCreateOrUpdateResponse.

SqlManagementClient exists in both ..sql and ..slq.fluent namespaces both of which do not find namespace for .server in the line sqlMgmtClient.Servers.Get(resourceGroupName, serverName).Server;

Searched Microsoft's Azure Management Namespaces with no luck.

But google found DatabaseCreateOrUpdateParameters and its namespace on a spanish MSDN site, which links to above.

回答1:

The confusion here is because there are 3 different API's:

1. Microsoft.Azure.Management.Sql version < 1.0
2. Microsoft.Azure.Management.Sql version >= 1.0 (API reference: https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.management.sql?view=azure-dotnet)
3. Microsoft.Azure.Management.Sql.Fluent, which is based on Microsoft.Azure.Management.Sql version >= 1.0 and is intended to be a more human-friendly design but does not have as much feature support as the base .Sql library. (API reference: https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.management.sql.fluent?view=azure-dotnet)

Brando's answer is for the #1 API. To update the code for the new API (#2), it should look like this:

    // Create a database: configure create or update parameters and properties explicitly
    Database newDatabaseParameters = new Database()
    {
        Location = currentServer.Location,
        CreateMode = DatabaseCreateMode.PointInTimeRestore,
        Edition = databaseEdition,
        SourceDatabaseId = "/subscriptions/{yoursubscriptionid}/resourceGroups/{yourgroupid}/providers/Microsoft.Sql/servers/{sqlservername}/databases/{sqldatabasename}",
        RestorePointInTime = DateTime.Parse("2017-09-22T02:32:08Z"),//Restore Point time
    };

    Database db = sqlMgmtClient.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, newDatabaseParameters);
    return db;

The major differences are:

1. There is no longer separate classes like DatabaseCreateOrUpdateParameters, DatabaseCreateOrUpdateProperties, DatabaseCreateOrUpdateResponse. There is just one class Database.
2. The properties of Database are "flattened" so there is no inner Properties to set.

回答2:

According to your description, I suggest you could try to use this nuget package.

Microsoft Azure SQL Management Library

Then you could use below codes to achieve Point In Time Restore.

Code like this:

Notice, you need registry an application to get tenantId, applicationId, SecretKey, then with subscriptionId to get the authentication token.

About how to register, you could refer to these codes:

Main method:

        var subscriptionId = "subscriptionId ";
        var clientId = "clientId ";
        var tenantId = "tenantId ";
        var secretKey = "secretKey ";
        var azureSqlDatabase = "azureSqlDatabase ";
        var resourceGroup = "resourceGroup ";
        var azureSqlServer = "azureSqlServer "; 
        var databaseEdition = DatabaseEditions.Standard;
        var databasePerfLevel = "S1";

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

        CreateOrUpdateDatabase(sqlManagementClient, resourceGroup, azureSqlServer, azureSqlDatabase, databaseEdition, databasePerfLevel);

The get auth token method:

    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;
    }

The restore method:

    static DatabaseCreateOrUpdateResponse CreateOrUpdateDatabase(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string databaseName, string databaseEdition, string databasePerfLevel)
    {
        // Retrieve the server that will host this database
        Server currentServer = sqlMgmtClient.Servers.Get(resourceGroupName, serverName).Server;

        // Create a database: configure create or update parameters and properties explicitly
        DatabaseCreateOrUpdateParameters newDatabaseParameters = new DatabaseCreateOrUpdateParameters()
        {
            Location = currentServer.Location,
            Properties = new DatabaseCreateOrUpdateProperties
            {
                CreateMode = DatabaseCreateMode.PointInTimeRestore,
                //DatabaseEditions.Standard;
                Edition = databaseEdition,
                SourceDatabaseId = "/subscriptions/{yoursubscriptionid}/resourceGroups/{yourgroupid}/providers/Microsoft.Sql/servers/{sqlservername}/databases/{sqldatabasename}",
                RestorePointInTime = DateTime.Parse("2017-09-22T02:32:08Z"),//Restore Point time
                //S1
                RequestedServiceObjectiveName = databasePerfLevel
            }
        };

        DatabaseCreateOrUpdateResponse dbResponse = sqlMgmtClient.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, newDatabaseParameters);
        return dbResponse;
    }

Result: