none
Unable to restore using ARM Template to SQLDB RRS feed

  • Question

  • Hello,

    I am currently trying to use ARM Template to deploy two databases on the same server.

    For each database I have a bacpac file I want to restore and for such I am using this kind of template:

    {
              "name": "AirlinePerformance-OLTP",
              "type": "databases",
              "apiVersion": "2015-01-01",
              "location": "[resourceGroup().location]",
              "tags": {
                "displayName": "Database"
              },
              "properties": {
                "edition": "[variables('databaseEdition')]",
                "collation": "[variables('databaseCollation')]",
                "requestedServiceObjectiveName": "[variables('databaseServiceObjectiveName')]"
              },
              "dependsOn": [
                "[variables('sqlServerName')]"
              ],
              "resources":[
                {
                  "name": "Import",
                  "type": "extensions",
                  "apiVersion": "2014-04-01-preview",
                  "properties": 
                    {
                      "storageKeyType": "StorageAccessKey",
                      "storageKey": "...",
                      "storageUri": [
                          "https://xxxx.blob.core.windows.net/datafiles/AirlinePerformance-OLTP.bacpac"
                      ],
                      "administratorLogin": "[parameters('sqlUsername')]",
                      "administratorLoginPassword": "[parameters('sqlPassword')]",
                      "operationMode": "Import"
                    },
                    "dependsOn": [
                      "[resourceId('Microsoft.Sql/servers/databases', variables('sqlServerName'), 'AirlinePerformance-OLTP')]"
                    ]
                }
              ]
            }

    This is the exception I am getting:

    {
        "id": "/subscriptions/eccb8f49-1e09-4948-a674-0340d77c935d/resourceGroups/pwclabs-lab1-645011/providers/Microsoft.Resources/deployments/c1b01fc2-8a12-48f2-bc0c-2f8b840226f7/operations/44D3EB69B30D2A38",
        "operationId": "44D3EB69B30D2A38",
        "properties": {
          "provisioningOperation": "Create",
          "provisioningState": "Failed",
          "timestamp": "2019-09-07T06:34:21.9119355Z",
          "duration": "PT0.2855292S",
          "trackingId": "731e9caa-84b2-4772-a652-1818e98429cc",
          "statusCode": "BadRequest",
          "statusMessage": {
            "code": "BadRequest",
            "message": "An error occurred while processing this request.",
            "target": null,
            "details": [],
            "innererror": []
          },
          "targetResource": {
            "id": "/subscriptions/eccb8f49-1e09-4948-a674-0340d77c935d/resourceGroups/pwclabs-lab1-645011/providers/Microsoft.Sql/servers/adflabsqlqb6ac/databases/AirlinePerformance-ODS/extensions/Import",
            "resourceType": "Microsoft.Sql/servers/databases/extensions",
            "resourceName": "adflabsqlqb6ac/AirlinePerformance-ODS/Import"
          }
        }
      }

    Does anyone knows why I am getting a bad request?

    Thank you


    Luis Simoes

    Saturday, September 7, 2019 6:42 AM

All replies

  • Hi

    I think we can create new databases in 3 modes (1 - Blank), (2- restore from any specific existing server), (3- Sample offered by Azure).

    For this case, the template seems to be referring the manually entered but can you try with generation template from GUI which refers to existing server which doesn't need to provide storage cred and many else explicitly.

    Added example ARM template as below. Hope this would be helpful.

    {
        "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
        "contentVersion": "1.0.0.0",
        "parameters": {
            "databaseName": {
                "type": "string"
            },
            "serverName": {
                "type": "string"
            },
            "location": {
                "type": "string"
            },
            "createMode": {
                "type": "string"
            },
            "recoverableDatabaseId": {
                "type": "string"
            },
            "skuName": {
                "type": "string"
            },
            "tier": {
                "type": "string"
            },
            "zoneRedundant": {
                "type": "bool",
                "defaultValue": false
            },
            "licenseType": {
                "type": "string",
                "defaultValue": ""
            },
            "readScaleOut": {
                "type": "string",
                "defaultValue": "Disabled"
            },
            "numberOfReplicas": {
                "type": "int",
                "defaultValue": 0
            },
            "minCapacity": {
                "type": "string",
                "defaultValue": ""
            },
            "autoPauseDelay": {
                "type": "string",
                "defaultValue": ""
            },
            "databaseTags": {
                "type": "object",
                "defaultValue": {}
            },
            "enableADS": {
                "type": "bool",
                "defaultValue": false
            },
            "enableVA": {
                "type": "bool",
                "defaultValue": false
            }
        },
        "resources": [
            {
                "condition": "[parameters('enableVA')]",
                "apiVersion": "2016-01-01",
                "type": "Microsoft.Storage/storageAccounts",
                "name": "[variables('storageName')]",
                "location": "[parameters('location')]",
                "sku": {
                    "name": "Standard_LRS"
                },
                "kind": "Storage",
                "properties": {}
            },
            {
                "apiVersion": "2017-10-01-preview",
                "location": "[parameters('location')]",
                "tags": "[parameters('databaseTags')]",
                "name": "[concat(parameters('serverName'), '/', parameters('databaseName'))]",
                "properties": {
                    "createMode": "[parameters('createMode')]",
                    "recoverableDatabaseId": "[parameters('recoverableDatabaseId')]",
                    "zoneRedundant": "[parameters('zoneRedundant')]",
                    "licenseType": "[parameters('licenseType')]",
                    "readScale": "[parameters('readScaleOut')]",
                    "readReplicaCount": "[parameters('numberOfReplicas')]",
                    "minCapacity": "[parameters('minCapacity')]",
                    "autoPauseDelay": "[parameters('autoPauseDelay')]"
                },
                "sku": {
                    "name": "[parameters('skuName')]",
                    "tier": "[parameters('tier')]"
                },
                "type": "Microsoft.Sql/servers/databases"
            },
            {
                "condition": "[parameters('enableADS')]",
                "apiVersion": "2017-03-01-preview",
                "type": "Microsoft.Sql/servers/securityAlertPolicies",
                "name": "[concat(parameters('serverName'), '/Default')]",
                "dependsOn": [
                    "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]"
                ],
                "properties": {
                    "state": "Enabled",
                    "disabledAlerts": [],
                    "emailAddresses": [],
                    "emailAccountAdmins": true
                }
            },
            {
                "condition": "[parameters('enableVA')]",
                "apiVersion": "2018-06-01-preview",
                "type": "Microsoft.Sql/servers/vulnerabilityAssessments",
                "name": "[concat(parameters('serverName'), '/Default')]",
                "properties": {
                    "storageContainerPath": "[if(parameters('enableVA'), concat(reference(variables('storageName'), '2018-02-01').primaryEndpoints.blob, 'vulnerability-assessment'), '')]",
                    "storageAccountAccessKey": "[if(parameters('enableVA'), listKeys(variables('storageName'), '2018-02-01').keys[0].value, '')]",
                    "recurringScans": {
                        "isEnabled": true,
                        "emailSubscriptionAdmins": true,
                        "emails": []
                    }
                },
                "dependsOn": [
                    "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]",
                    "[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
                    "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/securityAlertPolicies/Default')]"
                ]
            }
        ],
        "variables": {
            "subscriptionId": "[subscription().subscriptionId]",
            "resourceGroupName": "[resourceGroup().name]",
            "uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('resourceGroupName'), parameters('location'))]",
            "storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]"
        }
    }


    Thanks Please mark as answer if my post is helped to solve your problem and vote as helpful if it helped so that forum users can benefit

    Saturday, September 7, 2019 8:22 AM
  • Hi @naveen, 

    I ended up making it work... the problem was on the DependsOn that was not set up properly...

    Thanks anyway for your suggestion.

    Regards,

    LS


    Luis Simoes

    Saturday, September 7, 2019 10:33 AM
  • Great Luis. 

    Thanks Please mark as answer if my post is helped to solve your problem and vote as helpful if it helped so that forum users can benefit

    Saturday, September 7, 2019 11:10 AM
  • Hi

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please  mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. 

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, September 12, 2019 4:36 AM