@how2codeinfo | Svenska Svenska | English English

How to Copy Azure SQL Database to another Subscription

Johan Åhlén by Johan Åhlén • Updated

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 Azure SQL Database in Azure Portal

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:

The trick is to login as administrator into your target database server and run this T-SQL statement:

CREATE 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!

Azure Azure SQL Database

More about Azure SQL Database

Getting started with Azure SQL Elastic Database Pools

More about Azure

Getting Started with Azure Purview
Getting started with Azure SQL Elastic Database Pools
How to Connect to a SOAP API from Azure Data Factory