I am trying to copy a SQL database across onto a different subscription and I’m currently stuck.

The first step was to read the link here

But I get this error:

Msg 45137, Level 16, State 1, Line 3
Insufficient permission to create a database copy on server ‘destination’. Ensure that the user login ‘db_copy_login’ has the correct permissions on the source and target servers.

This is despite the login db_copy_login being part of the db_manager role, the login can create an empty database without any issues.

As I have got admin rights on my Entra ID across the 2 databases (source/destination) over different subscriptions but same tenant.

I can use the code shown here:

CREATE DATABASE my_new_db AS COPY OF [source_sql_server].[source_sql_db]

Then I got this error:

Msg 47603, Level 16, State 1, Line 1
All servers linked by GeoDr should have the same key material as the encryption protector of the primary server. Please add the key ‘https://source-keyvault.vault.azure.net/keys/sql-tde/xxxxxxxxx’ with the same key material to the secondary server ‘destination_sql_server’. (https://aka.ms/sqltdebyokgeodr)

Turns out that you cannot restore an encryption key across subscriptions either.

What do I do next ?

Use SSMS to export the database as a .dacpac to a storage account with the intention of importing.

I got all sorts of errors as the database needs to be cleaned up, for example there are some views referencing tables that does not exists. This got me thinking, how else can I copy an encrypted database across subscriptions?