0

I want to export my SQL Azure database to a file test.bacpac, but I failed:

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Error validating element [dbo].[IsMyUserExisted]: The element [dbo].[IsMyUserExisted] cannot be deployed as the script body is encrypted.

The question is, why can't I back up my database like in SQL Server 2008, 2017 etc (just backup database, and then restore database).

   "C:\Program Files (x86)\Microsoft Visual Studio\2019\Preview\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe" /a:Export /ssn:"servername" /sdn:"databasename" /su:"username" /sp:"passwordhere" /tf:"myfile.bacpac" ExcludeObjectsTypes=StoredProcedures

but the property ExcludeObjectsTypes=StoredProcedures is invalid

I also tried "/p:ExcludeObjectsTypes=StoredProcedures" but still get an error.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chandra Arifin
  • 43
  • 1
  • 11
  • This is not an on-topic question for Stack Overflow, you should ask at [https://dba.stackexchange.com/](https://dba.stackexchange.com/) – Stu Apr 04 '21 at 09:33
  • Does the stored procedure has with encryption option? – Leon Yue Apr 05 '21 at 02:03

1 Answers1

5

Azure SQL Database does not support the WITH ENCRYPTION option for migrating objects such as stored procedures, user defined functions, triggers, or views. Therefore, migrating objects compiled with that option is not possible. You will need to remove the WITH ENCRYPTION option.

It means that Azure SQL doesn't support export/migrate database which contains these encrypted object, we will always get the error like this: enter image description here

You must unencrypt this procedure then backup the database. After the database restored, find this stored procedure and encrypt it again.

Please ref this blog: https://thomaslarock.com/2013/05/migrate-encrypted-procedures-azure-sql-database/

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23