Microsoft.Sql/managedInstances/databases ARM Documentation is Incorrect

[aolszowka]

I cannot seem to find the feedback page for the ARM Template (https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/managedinstances/databases?pivots=deployment-language-arm-template) exactly but as this is referenced by the linked guide, I wanted to call this out.

The documentation for createMode is incorrect, at least for a PointInTimeRestore:

createMode	Managed database create mode. PointInTimeRestore: Create a database by restoring a point in time backup of an existing database. SourceDatabaseName, SourceManagedInstanceName and PointInTime must be specified. RestoreExternalBackup: Create a database by restoring from external backup files. Collation, StorageContainerUri and StorageContainerSasToken must be specified. Recovery: Creates a database by restoring a geo-replicated backup. RecoverableDatabaseId must be specified as the recoverable database resource ID to restore. RestoreLongTermRetentionBackup: Create a database by restoring from a long term retention backup (longTermRetentionBackupResourceId required).	'Default''PointInTimeRestore''Recovery''RestoreExternalBackup''RestoreLongTermRetentionBackup'

When attempting to perform a PointInTimeRestore the properties referenced (SourceDatabaseName, SourceManagedInstanceName, PointInTime) do not exist. Rather the following properties are used: restorePointInTime and sourceDatabaseId which are documented in the documentation.

Additionally, the restorePointInTime indicates that the time should be in ISO8601 format, however attempting to use utcNow() results in the following error:

{
  "error": {
    "details": [
      {
        "code": "InvalidResourceIdSegment",
        "message": "",
        "target": "parameters.properties.restorePointInTime"
      }
    ],
    "code": "InvalidResourceIdSegment",
    "message": "The 'parameters.properties.restorePointInTime' segment in the url is invalid."
  }
}

Rather testing has shown that you must provide it in this version of the ISO8601 format: yyyy-MM-ddTHH:mm:ssZ which can be done using utcNow('yyyy-MM-ddTHH:mm:ssZ').

Below is a snippet of the ARM Template that works to perform a PointInTimeRestore accessing a source database that lives in another resoure group in the same subscription that works for me:

{
    "type": "Microsoft.Sql/managedInstances/databases",
    "name": "[concat(variables('destinationSqlManagedInstanceName'), '/', 'AdventureWorks')]",
    "apiVersion": "2021-11-01",
    "location": "[parameters('location')]",
    "properties": {
        "createMode": "PointInTimeRestore",
        "restorePointInTime": "2022-12-14T12:00:00Z",
        "sourceDatabaseId": "[resourceId(variables('sourceResourceGroupName'), 'Microsoft.Sql/managedInstances/databases', variables('sourceSqlManagedInstanceName'), 'AdventureWorks')]"
    }
}

---

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

• ID: fd01ff9b-dd2e-14a8-cd00-a8bdf88ec463
• Version Independent ID: 4f339396-3b28-9a65-661e-f1a07ee31579
• Content: Azure Resource Manager templates - Azure SQL Database & SQL Managed Instance - Azure SQL Database
• Content Source: azure-sql/database/arm-templates-content-guide.md
• Service: sql-db-mi
• Sub-service: deployment-configuration
• GitHub Login: @srdan-bozovic-msft
• Microsoft Alias: srbozovi

[RamanathanChinnappan-MSFT]

@aolszowka Thanks for your feedback! We will investigate and update as appropriate.

[shaktisingh-msft]

Hi @srdan-bozovic-msft,

Could you please look into this document update request. Thanks

[MashaMSFT]

Thanks for your feedback @aolszowka ! @MilanMSFT would you mind taking a look?

#reassign:MilanMSFT

[aolszowka]

This issue is still outstanding, however I wanted to loop back around as we ended up encountering an issue with the above template when attempting to perform a cross-subscription database restore as per the documentation here: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore?view=azuresql&tabs=azure-portal.

Attempting to create a custom RBAC with the following access did not work:

{
    "id": "/subscriptions/REDACTED/providers/Microsoft.Authorization/roleDefinitions/REDACTED",
    "properties": {
        "roleName": "SQL MI DB Backup Reader",
        "description": "Permission to read SQL MI configuration and readBackups so they can be restored in another subscription ",
        "assignableScopes": [
            "/subscriptions/REDACTED"
        ],
        "permissions": [
            {
                "actions": [
                    "Microsoft.Sql/managedInstances/databases/readBackups/action",
                    "Microsoft.Sql/*/read"
                ],
                "notActions": [],
                "dataActions": [],
                "notDataActions": []
            }
        ]
    }
}

Attempting to restore the database, cross-subscription, using the above ARM Template resulted in the following error:

LinkedAuthorizationFailed: The client 'REDACTED' with object id 'REDACTED' has permission to perform action 'Microsoft.Sql/managedInstances/databases/write' on scope '/subscriptions/REDACTED-SUBSCRIPTION-DEST/resourcegroups/REDACTED-RESOURCEGROUP-DEST/providers/Microsoft.Sql/managedInstances/REDACTED-SQLMI-DEST/databases/AdventureWorks'; however, it does not have permission to perform action 'Microsoft.Sql/managedInstances/databases/write' on the linked scope(s) '/subscriptions/REDACTED-SUBSCRIPTION-SOURCE/resourceGroups/REDACTED-RESOURCEGROUP-SOURCE/Microsoft.Sql/managedInstances/REDACTED-SQLMI-SOURCE/databases/AdventureWorks' or the linked scope(s) are invalid.

After opening up Microsoft Support Case #2304130040009072 we connected with Mohsen Shourcheh who indicated that we needed to use the latest preview version of this API as per the RESTful API documentation and also the purple bar indicated on the Point-in-Time-Restore documentation linked above.

However, the ARM Template Documentation, even for the preview API (2022-05-01-preview) is still lacking. After working with support (Mohsen), I can say that the following works, even with the minimal RBAC roles mentioned in the Point-In-Time Restore documentation linked above:

{
    "type": "Microsoft.Sql/managedInstances/databases",
    "name": "[concat(variables('destinationSqlManagedInstanceName'), '/', 'AdventureWorks')]",
    "apiVersion": "2022-05-01-preview",
    "location": "[parameters('location')]",
    "properties": {
        "createMode": "PointInTimeRestore",
        "restorePointInTime": "2022-12-14T12:00:00Z",
        "crossSubscriptionSourceDatabaseId": "[resourceId(variables('sourceSubscriptionId'), variables('sourceResourceGroupName'), 'Microsoft.Sql/managedInstances/databases', variables('sourceSqlManagedInstanceName'), 'AdventureWorks']",
        "crossSubscriptionTargetManagedInstanceId": "[resourceId('Microsoft.Sql/managedInstances', variables('destinationSqlManagedInstanceName'))]"
    }
}

Can we give this documentation some love?

[Stralle]

Hi, @aolszowka thanks for raising this issue, providing rich description and examples, and sorry for the delay! There has been a switch between owners in the area, so I'll be picking this up.

Would example of HTTP request and example of ARM template with "restorePointInTime": "yyyy-MM-DDTHH:mm:ssZ" be good enough or you think there's something else we shall mention in the docs?

From your second comment I realize you resolved the permissions issue since when you changed to use new API.

If that's all, I'll make sure we update our ARM template and REST API docs and will close the issue once it's done.

[aolszowka]

@Stralle I think that's fair; let me know when the docs are updated, just trying to help other DevOps Engineers out and improve Microsoft's documentation at the same time :)

[MashaMSFT]

Hi @aolszowka , Thank you for your submission! My sincere apologies for the delay it's taken to address your concern, we've been working through a very large backlog.

During this time, Git Issues have been deprecated as we've transitioned to a new feedback system. I've added your suggestion to our internal backlog (#250019) that we will continue addressing as priorities and resources allow.

We use your feedback to improve our documentation, so we are grateful for your input, and your time. I apologize for any inconvenience, and thank you for your patience and understanding.

Masha from the SQL Docs team #please-close