How to Copy Azure SQL Database to another Subscription
Azure SQL Databases comes with built-in backup and restore capabilities, but how do you copy an Azure SQL Database between subscriptions? It is actually fast and easy to do. I will describe how to do it in this article.
If you have used on-premise SQL Server, you have probably learned that the easiest way to copy a database between servers is to do a backup to a file and then restore the backup file on the target server. However, Azure SQL Databases don't support backing up to or restoring from files.
There is a Copy database function in the Azure portal, but it can only copy within the same subscription / resource group.
Copy your Azure SQL database to another subscription
There is an easy way to copy your database to another subscription, but it has a few requirements:
- You can't do it from the Azure portal. You need to use a tool like SQL Server Management Studio (which you can download for free).
- In the target subscription, your database server needs to have the same admin account name and password as the source database server.
- You need to use SQL authentication when logging in.
- You need to ensure firewalls are open so that you can connect to both the source and target from SQL Server Management Studio.
The trick is to login as administrator into your target database server and run this T-SQL statement:
1CREATE DATABASE [new_database_name] AS COPY OF [source_server_name].[source_database_name];
Note the square brackets. They are necessary if your names include any special characters, such as dash.
The source server name is the name of your database server in Azure. For example if the server address is foo.database.windows.net, the server name is foo. Server names are always globally unique in Azure, so there is no need to specify the name of the subscription or anything else.
The copy will include database users. This will work fine if the users are fully contained users (that they have been created with username/password directly in the database). However, if they are tied to server logins, you will have to resolve the mappings so they connect to the logins on the new server.
Good luck!