LCS Database movement REST API

You can read my complete guide on Microsoft Dynamics 365 for Finance & Operations and Azure DevOps.

Since last October we’ve been able to try the preview of Microsoft Dynamics 365 for Finance and Operations Database Movement API which allows us to list and download DB backups and start DB refreshes using a REST API.

If you want to join the preview you first need to be part of the MSDyn365FO Insider Program where you can join the “Dynamics 365 for Finance and Operations Insider Community“. Once invited to the Yammer organization you can ask to join the “Self-Service Database Movement / DataALM” group where you’ll get the information to add yourself to the preview and enable it on LCS.

The API

At the moment we can perform the following operations through the RESTful API:

• List DB backups for an LCS project.
• Create a DB refresh between two environments (only Prod and Sandbox environments, like we can do in LCS).
• Get an ongoing operation status.

More operations are expected in 2020! And there’s also a limit of 3 refresh requests per target per 24 hours, going over the limit will resutl in the following response:

{
"IsSuccess": false,
"OperationActivityId": "55eb4327-9346-4c7b-82bd-fe8ef15112c6",
"ErrorMessage": "Maximum allowed API operations are 3 from 2019-09-30T04:01:01.9999999",
"VersionEOL": "9999-12-31T23:59:59.9999999"
}

Let’s see how to call the API using Postman.

Authentication

The first step you’ll need to take is registering a new App on AAD. Just follow the docs or check this post and save your App application Id and secret for later.

Now we need to request a token using Microsoft’s identity platform doing a GET call to the url https://login.microsoftonline.com/common/oauth2/token. I’ve also successfully tried using a token generated with my organization’s tenant so feel free to try common and your tenant.

The token request should look like this:

Note that the grant_type parameter is of type password instead of client_credentials like we do when getting the token to call Dynamics 365’s OData API. Use the AAD’s appId and secret in client_id and client_secret and the API url (https://lcsapi.lcs.dynamics.com) in resource.

We need to provide a username and password which must exist in the LCS project we’ll query and also have the preview key enabled in LCS.

And if everything is OK we’ll get the token:

With the access_token value we can do the calls to the LCS API.

Requests

List backups

Once we have the token we can make the requests. For example, for the list databases endpoint (url: https://lcsapi.lcs.dynamics.com/databasemovement/v1/databases/project/LCS_PROJECT_ID) your Postman call will look like:

And we’ll get a JSON with a list of the DB backups on our LCS Asset Library:

{
    "DatabaseAssets": [
        {
            "Id": "00c82737-5734-4de4-81cf-163ca7c76119",
            "ProjectId": LCS_PROJECT_ID
            "OrganizationId": ORG_ID,
            "Name": "DBbackup",
            "FileName": "DBbackup.bacpac",
            "FileDescription": null,
            "FileLocation": "https://bacpackurlatablob.com",
            "ModifiedDateTime": "2019-10-23T11:55:43.033",
            "CreatedDateTime": "2019-10-23T11:55:43.033",
            "CreatedByName": null,
            "ModifiedByName": null
        }
    ],
    "IsSuccess": true,
    "OperationActivityId": "3c66eab9-b2e5-468e-a72f-ac980d859c05",
    "ErrorMessage": null,
    "VersionEOL": "9999-12-31T23:59:59.9999999"
}

In the response you get a valid link to download the file and several other information, including the IsSuccess field to check that the operation went well.

Database refresh

To trigger a DB refresh from the API you need to do a POST request to the uri https://lcsapi.lcs.dynamics.com/databasemovement/v1/databases/project/{projectId}/source/{sourceEnvironmentId}/target/{targetEnvironmentId}. Using the value of the field environment Id that you can find in the environment’s page in LCS.

The call must have the same headers as the list one and no body is needed. After running the request we’ll receive this response:

{
    "IsSuccess": true,
    "OperationActivityId": "55eb4327-9346-4c7b-82bd-fe8ef15112c6",
    "ErrorMessage": null,
    "VersionEOL": "9999-12-31T23:59:59.9999999"
}

Again the IsSuccess property will tell us if the operation went well.

Get operation status

And the last API service we can access at the moment helps us list the status of an operation calling the following url: https://lcsapi.lcs.dynamics.com/databasemovement/v1/fetchstatus/project/{projectId}/environment/{environmentId}/operationactivity/{operationactivityId}.

The operationactivityId value is the one we got when calling the refresh service. The response will be:

{
    "IsSuccess": true,
    "OperationActivityId": "6a90b45f-1764-4077-b924-3f4671540237",
    "ErrorMessage": null,
    "VersionEOL": "9999-12-31T23:59:59.9999999",
    "ProjectId": "12345",
    "EnvironmentId": "5362377c-bc37-4f92-b30e-fe0c1e664cc0",
    "ActivityId": "55eb4327-9346-4c7b-82bd-fe8ef15112c6",
    "CompletionDate": null,
    "OperationStatus": "InProgress"
}

What could we use this for?

It’s up to you and your needs, but for example we could create a Power App where we’d select the source and target environment and trigger a DB refresh using a Flow. Or call the API for a DB refresh from Production to UAT in our daily/weekly release pipeline to have fresh data overnight.

In the early days of Dynamics 365 FnO, before having the LCS self-service DB movement operations, I created a pair of Powershell scripts that would run each night using the Windows scheduler. The first one on the Tier2 UAT environment to create a bacpac of the DB and upload it to an Azure storage account. The second script downloaded the bacpac to a onebox dev VM and restored the bacpac so we could get a BAK quickly to restore any dev VM. The API could have helped me with this because those were my first PowerShell scripts and looked horrible (but did the job!).

It’s good to see the product evolve and offer us new tools to solve our issues and needs.