0

I have an Azure SQL Server database with a Geo-replication on Azure, which I would like to regularly backup via sqlpackage.exe (s. command below) using the replicated read-only database.

However, when executing it, it runs into an error (SQL71501) - the error occurs both on the primary as on the secondary database.

As this is a production database with thousands of users, is there any way to fix that in an "open heart surgery", i. e. without taking down the service, copying the database, fixing stuff then deploying that as the life database?

The command I run:

sqlpackage.exe" /a:Export /scs:"Data Source=tcp:xxx.database.windows.net;Authentication=Active Directory Password;Initial Catalog=xxx_Copy;UID=xxx;PWD=xxx" 
               /tf:C:\Databases\xxx-$(((get-date).ToUniversalTime()).ToString("yyyy-MM-dd-T-HH_mm_ss")).bacpac

The result I get:

Validating schema

Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71501: Error validating element [dss]: Schema: [dss] has an unresolved reference to object [##MS_SyncAccount##].

Error SQL71501: Error validating element [TaskHosting]: Schema: [TaskHosting] has an unresolved reference to object [##MS_SyncAccount##].

1

This occurs when Azure SQL Data Sync was used on the database to sync with another database. The remnants from the DSS schema used by SQL Data Sync do not allow to export the database due to inconsistencies.

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 the portal. (there is also documentation for doing this using T-SQL or PowerShel.

  2. Remove the Data Sync metadata objects from the copy. You can find a script to do it here ... Please make sure you are connected to the copy.

  3. Export the database from the copy.

  4. Delete the database copy.

For more information, visit this article.

1
  • 1
    Thanks, this is exactly what I needed to tidy up a database that was setup for a data sync but the data sync objects and tables were no longer required.
    – medina
    May 28 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy