Azure SQL bacpac Fails on Special Characters in Stored Procedures

Question

Trying to move a database from an Azure GOV tenant to a standard Azure tenant. From what I can tell, the export is failing due to special characters in the Store Procedures (slashes, dollar signs, etc.) These are properly escaped and work as standalone T-SQL scripts. I can drop all of the SPs, move the database, and then restore. But there has to be a better way.

Has anyone else had an issue with special characters in the body of stored procedures? I am open to other ways of trying to move between tenants, but have come up empty-handed.

Answer 1

Adding correct argument handling to your code would be a more solid method to handle this.

Use cmd.Parameters.AddWithValue(String parameterName, Object value) | SqlParameterCollection.Add Method

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.add?redirectedfrom=MSDN&view=dotnet-plat-ext-5.0#System_Data_SqlClient_SqlParameterCollection_Add_System_String_System_Object_

It will be simpler to utilise special characters as arguments if you run the SQL by creating a SQLCommand object and adding parameters to it.

Alternative

To migrate resources, you can utilise the Azure interface, Azure PowerShell, Azure CLI, or the REST API. Move resources to a new resource group or subscription | https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/move-resource-group-and-subscription

During the move procedure, both the source and target groups are locked. Until the relocation is finished, write and delete activities on resource groups are banned. You can't add, alter, or delete resources in resource groups if they're locked. This does not imply that the resources have been frozen. Applications that use the databases will not experience any downtime if you migrate an Azure SQL logical server and its databases to a different resource group or subscription. They still have access to the databases and can read and write to them. Although the lock can last up to four hours, most manoeuvres are completed in considerably less time.

You must ensure the following before moving resources across subscriptions: Both the source and destination subscriptions must be located in the same folder.

1. In both subscriptions, a single user account must be able to generate and delete resources.
2. You must migrate all SQL databases on that server at the same time.

If the SQL server and the destination directory are in separate directories, you can transfer the SQL server to a temporary, trial subscription, then move that subscription to the target directory (from the old portal), then complete the move in the new portal using the target directory.