Exporting a database that is/was used as SQL Data Sync metadata database


When trying to export a database that is or was used as SQL Data Sync metadata database you can encounter errors like:

Error encountered during the service operation.
Could not extract package from specified database.
The element DataSyncEncryptionKey_1076efa36f054d35a60e717333298486 is not supported in Microsoft Azure SQL Database v12.

or

Error encountered during the service operation.
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71501: Error validating element [TaskHosting]: Schema: [TaskHosting] has an unresolved reference to object [##MS_SyncAccount##].
Error SQL71501: Error validating element [dss]: Schema: [dss] has an unresolved reference to object [##MS_SyncAccount##].

 

The supported format for Azure SQL Database export and import is .bacpac files.

This kind of files can be seen as a table by table export and are not transactionally consistent.

Independently of the chosen method to generate the .bacpac file, you must take into account this lack of transactional consistency.

The way to ensure that the backup file is consistent is to export a database that has no write activity during the export or create a copy of the database and do the export based on that copy.

When the database is/was used as SQL Data Sync metadata database we need to rely on the second method because there are some security-related objects that are not supported in export at the moment.

In order to successfully export the database, we need to:

  1. Create a database copy.
    You can check how to create a copy of the database using the portal at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy-portal (there is also documentation for doing this using T-SQL or PowerShell)
  2. Remove the Data Sync metadata objects from the copy. You can find a script to do it at https://raw.githubusercontent.com/vitomaz-msft/DataSyncMetadataCleanup/master/Data%20Sync%20complete%20cleanup.sql Please make sure you are connected to the copy.
  3. Export the database from the copy.
  4. Delete the database copy.
Comments (0)

Skip to main content