Azure ARM Template SQL Azure Database Bacpac Import Fails on Second Deployment

Question

I'm using the New-AzureRmResourceGroupDeployment Powershell cmdlet to deploy to a resource group using an Azure Resource Template. This is a test environment, so I want to restore a database from a .bacpac file to seed the database with a realistic volume of data.

The following snippet deploys successfully on the first deployment to this resource group, because there isn't an existing database, but fails on any subsequent deployment.

{
                "name": "[variables('databaseName')]",
                "type": "databases",
                "location": "[resourceGroup().location]",
                "apiVersion": "2014-04-01-preview",
                "dependsOn": [
                    "[variables('databaseServerName')]",
                    "[concat('Microsoft.Sql/servers/', variables('databases.ServerName'))]"
                ],
                "tags": {
                    "displayName": "testDatabase"
                },
                "properties": {
                    "collation": "[variables('databaseCollation')]",
                    "edition": "[variables('databaseEdition')]",
                    "maxSizeBytes": "1073741824",
                    "requestedServiceObjectiveName": "[variables('databaseServicePlan')]"
                },
                "resources": [
                    {
                        "name": "Import",
                        "type": "extensions",
                        "apiVersion": "2014-04-01-preview",
                        "dependsOn": [
                            "[variables('databaseName')]"
                        ],
                        "properties": {
                            "storageKeyType": "[variables('databaseBackupStorageKeyType')]",
                            "storageKey": "[parameters('databaseBackupStorageKey')]",
                            "storageUri": "[concat(parameters('databaseBackupStorageLocation'), '/', parameters('backupFileName'))]",
                            "administratorLogin": "[variables('databaseAdminLogin')]",
                            "administratorLoginPassword": "[variables('databaseAdminPassword')]",
                            "operationMode": "Import"
                        }
                    }
                ]
            }

The error when failure occurs:

Resource Microsoft.Sql/servers/databases/extensions '[resource-group-name]/[database-name]/Import' failed with message 'The ImportExport operation with Request Id 'b1f54bdd-6c98-4feb-a86f-656a5c6f1cc5' failed due to 'Error encountered during the service operation. 

Data cannot be imported into target because it contains one or more user objects. Import should be performed against a new, empty database.

Perhaps I've misunderstood how these templates are deployed - I thought ARM patched the environment. Does anyone know of a way I can inform ARM to only create / update the database (and sub-resources) if the configuration for those resources have changed?

Alternatively, if there's a better way to restore a database using a resource template I'd love to hear about it.

Any help or advice greatly appreciated!

Thanks in advance,

Rob

Answer 1

As the error message states, you can only import data into an empty database or while creating a new database.

Are you trying to import data into the same database every time or into a new database each time? If the latter, then I will coordinate with a member of my team who is more familiar with ARM templates to help fix the errors in the template.

Answer 2

from the sound of it you want to recreate the same test database with each deployment. Correct?

As nnuemah says, you must have either an empty database or no database prior to import of a bacpac. We are looking at supporting deployment of dacpac files (in addition to import of bacpac files) which might address your scenario, although they are more aimed at idempotent schema deployment rather than test data restore as a scenario. In the meantime are you able to delete the database out of band prior to deploying the template that imports the bacpac?

You can find out more about dacpacs and bacpacs and differences here: https://msdn.microsoft.com/en-us/library/ee210546.aspx

I will also look for other template options that might work in the interim.

Cheers, Bill

Answer 3

Unfortunately, as Bill said, deployment of dacpac files is not yet supported, but it is something we are looking into. And currently bacpacs are not idempotent. Once dacpacs are supported, you should be able to accomplish what you are describing.

Answer 4

I have the same issue. The good thing for me is that everything else in my deployment template gets committed even though my database deployment fails. But getting those errors is unsettling.

Referencing Kirk Evans from Microsoft, it is a known issue that the engineers are aware of. Se https://blogs.msdn.microsoft.com/kaevans/2016/03/28/deploy-bacpac-to-azure-sql-database-using-arm/#comment-56605

A solution to this is to use the possibility for partial templates to reference a web application, that emits the database deployment part. This web application could then query the database for existence and emit the partial content based on the result.