How to Copy Azure SQL Database using ARM Template

Question

Not sure if it is supported in ARM. I could find power-shell references only.

Answer 1

You cannot currently deploy a dacpac with an ARM template. The link above uses PowerShell but not ARM. You can create however create a database from a source database as a copy using an ARM template.

A simple way to find an example template for any Azure action is to perform the action in the portal - in this case, copy a database - and then open the appropriate resource group blade in the portal, list the deployments, locate the deployment just submitted and open it. Then select ViewTemplate from the menu bar and examine both the Template tab and the Parameters tab. These show you the full template and the parameter values actually used. You can then download the template, with accompanying Powershell script.

For database copy, here is the template:

{
  "$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"
    },
    "sourceDatabaseId": {
      "type": "string"
    },
    "requestedServiceObjectiveName": {
      "type": "string"
    }
  },
  "resources": [
    {
      "apiVersion": "2014-04-01-preview",
      "location": "[parameters('location')]",
      "name": "[concat(parameters('serverName'), '/', parameters('databaseName'))]",
      "properties": {
        "createMode": "[parameters('createMode')]",
        "sourceDatabaseId": "[parameters('sourceDatabaseId')]",
        "requestedServiceObjectiveName": "[parameters('requestedServiceObjectiveName')]"
      },
      "type": "Microsoft.Sql/servers/databases"
    }
  ]
}  

For database copy createMode = 'Copy'

And be sure to provide a fully qualified resourceId formatted as follows:

"/subscriptions/<sub-id>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/Servers/<server-name>/databases/<database-name>"

Make sure the resource group name capitalization is correct and that the server name is all lower case.

Answer 2

You can use the sourceDatabaseId property to reference another database. Then you can specify various createModes depending on what type of database you would like to create:

{ "properties": { "createMode": "OnlineSecondary", "sourceDatabaseId": "[resourceId('Microsoft.Sql/servers/databases', variables('sql01Name'), 'databasename')]" } }

http://msdn.microsoft.com/en-us/library/azure/mt163685.aspx

Answer 3

The answer above from @Bill Gibson - MSFT works if you are using a Microsoft.Sql/servers resource, however if you're using a Microsoft.Sql/managedInstances resource you'll need to use the appropriate Microsoft.Sql/managedInstance/databases - ARM Template.

The following works for me to perform a PointInTimeRestore accessing a source database that lives in another resource group (the variables and parameters are left as an exercise to the reader):

{
    "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')]"
    }
}

The documentation is broken in a few ways:

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 this is not the same as what is returned by utcNow(). 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').

I have created an issue to try and get the documentation fixed up here: https://github.com/MicrosoftDocs/azure-docs/issues/102717