Importing AWS RDS SQL Server backup to Azure SQL

Photo by Sebastian Herrmann on Unsplash

How hard can it be, right?

So this is what I tried

1. Take a backup (.bak) of the RDS SQL Server database into S3 bucket
2. Download the .bak to a local workstation
3. Restore the .bak to a local SQL Server
4. Export Data-tier Application (.bacpac) from the local SQL Server
5. Upload the .bacpac into Azure Blob storage
6. Use Azure portal to import a new database from a Blob storage
7. Waste hours trying to figure out why the import fails

What happens in 7 is that the database schema is created but the import process fails with an error message which tells you nothing as shown below.

Checking the database Import/Export history will not give you any more details either as it shows the same truncated message. Apparently the full error message is not available at all which is frustrating. Most Google results with SQL72012 error are about scenarios where people try to restore an Azure SQL backup to a local server. In that case the error might be caused by a missing contained database support which can be switched on locally using the below SQL statement. However this didn’t help me at all.

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO

After trying all kinds of tricks I ended up installing the SqlPackage.exe which can be used to import a .bacpac file into Azure SQL. After opening the required firewalls to access the DB from my workstation I was finally able to see the whole error message!

The RDS SQL backup contained an AWS specific trigger called rds_deny_backups_trigger which couldn’t be restored (and shouldn’t). After removing the trigger from the local SQL Server and creating the .bacpac once more I was able to successfully import the .bacpac from Blob storage using the Azure portal functionality.