I recently had to copy an Azure SQL database (SQL db) from one subscription to an Azure SQL Server instance in another subscription. All of the help I found suggested going to the database and hitting the COPY option. Unfortunately, when I did, I ran into a problem.
There was no subscription option, and the only servers listed were on the current subscription. I should point out that if you are copying from one Azure Managed Instance (MI) to another, then you have plenty of options, including selecting the subscription. Here, however, all I had was Server and Database Name.
So, next I tried Powershell and ended up trying the command New-AzSqlDatabaseCopy.
New-AzSqlDatabaseCopy -ResourceGroupName "" `
-ServerName $sourceserver -DatabaseName "" `
-CopyResourceGroupName "myResourceGroup" -CopyServerName $targetserver `
-CopyDatabaseName "CopyOfMySampleDatabase"
And it also didn’t have an option to select a source subscription, and when I tried just putting in the source server, it couldn’t find it. Note: I could have been doing something wrong, but if so I couldn’t figure it out. Also, there is most likely another way to do this, probably something from DBATools, etc. So if you have other options, feel free to put them in the comments.
In the end, I found that the CREATE DATABASE command had options that would work perfectly.
-- Connect to Destination instance
CREATE DATABASE [NewDatabase] AS COPY OF [SourceServer].[SourceDatabase]
(SERVICE_OBJECTIVE = ELASTIC_POOL( name = [ElasticPoolName] ) );
Super easy, was as fast as I could have hoped for, and even had an option to dump it into the Elastic Pool we needed. Since it’s T-SQL I didn’t need to specify the subscription, it found the server without an issue. It was particularly nice since I was able to script out multiple moves and run them quickly. And let’s face it, I’m the most comfortable with T-SQL.
