question

DBYYC avatar image
1 Vote"
DBYYC asked OuryBa-MSFT edited

Copy Azure SQL database to different subscription in DevOps Pipeline

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.

azure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DBYYC Welcome to Microsoft Q&A and Thank you for posting your question. We are looking into this and will get back to you. Thanks

0 Votes 0 ·

Thanks for the help, I certainly hope there is a way to achieve this. I feel like its not a stretch. Also I feel concerned that I am the only one apparently asking this question. Perhaps I've missed a fundamental of the DevOps pattern somewhere....

Again, thanks, looking forward to anything you can uncover.

0 Votes 0 ·

Not the solution , but found a nicely documented page for what effectively we are doing today with the above powershell in our pipeline.


https://gunnarpeipman.com/azure-devops-copy-database/


0 Votes 0 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered DBYYC commented

Hello @DBYYC 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.

90467-copy-db-to-another-subscription.png

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



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well, unfortunately this is the best we can do for now. I still find it hard to believe that there are no clever answers out there to make this solution a bit tidier. I did see that documentation earlier, thanks again. It is a recent document so I'll have to take it for face value.

Though I do hope that someone finds this post and can offer a unique solution to this problem.

Thank you for your thoughts and time.

1 Vote 1 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT edited

Hello @DBYYC the only supported and documented way of copying DB across subscriptions is to use T-SQL to do so: https://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.