Copy Azure SQL database to different subscription in DevOps Pipeline

Question

Hi, I'm not the only one wanting to do this I'm sure.

We have two subscriptions in Azure, one for production and the other for "Dev/Test". The Dev/Test subscription has a much more favorable costing environment than the production counter part.

What we would like to do as part of our DevOps practices is move a copy of our production databases to our Dev/Test subscription in a DevOps pipeline. Currently we have a weekly job to copy production data to a "Development" server in the same production subscription. We accomplish this with a short script mainly focused around the PS command New-AzSqlDatabaseCopy. In this script it calls for a few parameters, none of which are a subscription ID.

$resultCopy = New-AzSqlDatabaseCopy `
                -ResourceGroupName "$sourceResourceGroup" `
                -ServerName "$sourceServer" `
                -DatabaseName "$sourceDbName" `
                -CopyResourceGroupName "$targetResourceGroup" `
                -CopyServerName "$targetServer" `
                -CopyDatabaseName $dbCopyName `
                -ElasticPool "$targetElasticPool"

Reading MS documentation there is no way to do this type of copy with this command. I have seen some TSQL solutions but they do no look very graceful at all.

How have you others managed to move SQL Databases from one subscription to another in your pipelines? Are there DevOps Marketplace tools to do this for me in which I have not yet discovered?

Your Expert help is appreciated.

Thanks in advance.

Answer 1

Hello @Dan Barbary Unfortunately it is not possible to copy an Azure SQL Database to a different subscription either using Azure Portal, PowerShell or Azure CLI According to this document.

You can use the steps in the Copy a SQL Database to a different server section to copy your database to a server in a different subscription using T-SQL. Make sure you use a login that has the same name and password as the database owner of the source database. Additionally, the login must be a member of the dbmanager role or a server administrator, on both source and target servers. Please let me know if that helps. Thanks

Answer 2

Hello @Dan Barbary the only supported and documented way of copying DB across subscriptions is to use T-SQL to do so: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-a-different-subscription. Further, if the destination subscription is in a different AAD tenant as well, you would have to necessarily use SQL authentication to connect and issue the required T-SQL commands. Hope that helps . Please always feel free to reach out if you have any further queries. Thanks