Cannot export Azure SQL database

Question

When I try to export my database via the 'Export' button in the Azure portal, I get the following error:

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

Though I did at one point try the DataSync service, I never ended up using it and am certainly not using it now. I regret even trying this service because it created schemas in my database that I do not know how to remove completely.

Now, it seems that these schemas are preventing me from exporting my database.

At the very least, it would be nice to resolve this error. However, it would be better if I could remove all traces of the DataSync service.

Please note that I have already used the script in the accepted solution to this question: How to remove SQL Azure Data Sync objects manually. That removed the DataSync schema but I am still left with the 'dss' and 'TaskHosting' schemas.

I have also tried emailing support, as recommended by the accepted solution in this thread: https://social.msdn.microsoft.com/Forums/azure/en-US/8b68b44b-c98a-4b38-8aab-36a30a7fafd9/the-element-datasyncencryptionkeyid-is-not-supported-in-microsoft-azure-sql-database-v12-when?forum=ssdsgetstarted. That email simply bounced back.

Additionally, if I go into Azure portal, it does not show any sync groups or sync agents whatsoever.

Answer 1

I hope you have asked this question prior to proceed performing the steps outlined on those old forum threads that were created when SQL Data Sync 2.0 did not exist. SQL Data Sync is no longer in preview, this month went to GA and has a deprovision utility available.

On the computer where you installed SQL Agent, under the c:\program files (x86)\Microsoft SQL Azure Data Sync directory you should find an utility named deprovisionUtil.exe. If you run it with the switch /F it will remove the DSS schema and all its objects.

If this is not possible now, please create a support ticket. Azure Support will clean up the sync database.

Answer 2

First off, let me thank @alberto-morillo for his help. There is a good chance the SQL Agent would have worked had the situation been different.

In the end, I contacted Azure Support and they gave me the following script. Everything worked fine afterwards.

declare @n char(1)

set @n = char(10)



declare @triggers nvarchar(max)

declare @procedures nvarchar(max)

declare @constraints nvarchar(max)

declare @views nvarchar(max)

declare @FKs nvarchar(max)

declare @tables nvarchar(max)

declare @udt nvarchar(max)



-- triggers

select @triggers = isnull( @triggers + @n, '' ) + 'drop trigger [' + schema_name(schema_id) + '].[' + name + ']'

from sys.objects

where type in ( 'TR') and name like '%_dss_%'



-- procedures

select @procedures = isnull( @procedures + @n, '' ) + 'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'

from sys.procedures

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- check constraints

select @constraints = isnull( @constraints + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'

from sys.check_constraints

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- views

select @views = isnull( @views + @n, '' ) + 'drop view [' + schema_name(schema_id) + '].[' + name + ']'

from sys.views

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- foreign keys

select @FKs = isnull( @FKs + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'

from sys.foreign_keys

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- tables

select @tables = isnull( @tables + @n, '' ) + 'drop table [' + schema_name(schema_id) + '].[' + name + ']'

from sys.tables

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- user defined types

select @udt = isnull( @udt + @n, '' ) +

    'drop type [' + schema_name(schema_id) + '].[' + name + ']'

from sys.types

where is_user_defined = 1

and schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'

order by system_type_id desc





print @triggers

print @procedures

print @constraints

print @views

print @FKs

print @tables

print @udt



exec sp_executesql @triggers

exec sp_executesql @procedures

exec sp_executesql @constraints

exec sp_executesql @FKs

exec sp_executesql @views

exec sp_executesql @tables

exec sp_executesql @udt



GO

declare @n char(1)

set @n = char(10)

declare @functions nvarchar(max)



-- functions

select @functions = isnull( @functions + @n, '' ) + 'drop function [' + schema_name(schema_id) + '].[' + name + ']'

from sys.objects

where type in ( 'FN', 'IF', 'TF' )

and schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'





print @functions

exec sp_executesql @functions

GO



--update

DROP SCHEMA IF EXISTS [dss]

GO

DROP SCHEMA IF EXISTS [TaskHosting]

GO

DROP SCHEMA IF EXISTS [DataSync]

GO

DROP USER IF EXISTS [##MS_SyncAccount##]

GO

DROP ROLE IF EXISTS [DataSync_admin]

GO

DROP ROLE IF EXISTS [DataSync_executor]

GO

DROP ROLE IF EXISTS [DataSync_reader]

GO





declare @n char(1)

set @n = char(10)





--symmetric_keys

declare @symmetric_keys nvarchar(max)

select @symmetric_keys = isnull( @symmetric_keys + @n, '' ) + 'drop symmetric key [' + name + ']'

from sys.symmetric_keys

where name like 'DataSyncEncryptionKey%'



print @symmetric_keys



exec sp_executesql @symmetric_keys



-- certificates

declare @certificates nvarchar(max)

select @certificates = isnull( @certificates + @n, '' ) + 'drop certificate [' + name + ']'

from sys.certificates

where name like 'DataSyncEncryptionCertificate%'



print @certificates



exec sp_executesql @certificates

GO



print 'Data Sync clean up finished'