If you ever need to move a copy of a SQL database in Azure across servers then here is a quick easy way.
So let’s say you need to take a copy of database called [Rack] within Subscription A that is on server ABCSQL1 and name it database [NewRack] within subscription B on server called RBARSQL1 (The SQL Servers are in totally different data centers too).
Connect to the server (destination) within subscription B – as you can see no SQL databases.
Using a login which has permission on both source and destination server you need to issue the following when on the destination server (RBARSQL1) under the master database:
CREATE DATABASE [NewRack] AS COPY OF [ABCSQL1].[Rack]
Then do not forget about your SQL logins too.
If you try a fully qualified server name it will fail because it seems to append the ‘database.windows.net’ automatically!
For example:
CREATE DATABASE [NewRack] AS COPY OF [ABCSQL1.database.windows.net].[Rack]
Error message: The remote partner server name ‘ ABCSQL1.database.windows.net.database.windows.net‘ could not be resolved.
Do you know if it’s possible to accomplish the same task using Azure PowerShell? It doesn’t seem to work to copy between subscriptions using New-AzureRmSqlDatabaseCopy cmdlet.
LikeLike
Try this https://gallery.technet.microsoft.com/scriptcenter/Restoring-SQL-Azure-582d6cde
LikeLike
yes, I’ve seen this article too. But it describes a bit different case. They suggest to create entire new SQL Server in the same subscription and then change subscription for it, whereas I already have existing SQL Server in different subscription and I need to create database copy there.
LikeLiked by 1 person
It isn’t working for me with New-AzSqlDatabaseCopy as well.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy has T-SQL way to achieve this.
LikeLike