Azure SQL: Three ways to copy databases between Azure SQL Servers

  • Article

There are many use cases for having to copy an Azure SQL Database from one Azure SQL Server to another but how to achieve this?

The why

One of those use cases is when you’re running both a production and test environment and you want to use the production data for testing purposes. Copying can be done under the same subscription or between different tenants / subscriptions, depending on the option you choose.

The how

Multiple methods are available to copy data from one Azure SQL Database to another. For all methods mentioned we’ll use a source Azure SQL Server named azfsql01 with a Azure SQL Database named azfsdb01. The target Azure SQL Server is named azfsql02.

  • Via the Azure portal (Cross-Subscription, Cross-Tenant, “Internal”)
  • Via a SQL query (Cross-Subscription, “Internal”)
  • Via Powershell (Internal)

Via the Azure portal

For this method we’ll assume you want to copy a database between subscriptions in two different tenants. While logged in to the source subscription via the Azure Portal go to the required Azure SQL Database. From the GUI select Export.

In the menu that shows up set the name for the to be created bacpac file and set the storage account container. Enter the credentials from the source Azure SQL Server. Click OK to start the export.

Depending on the database size this could take some time, so in order to monitor the progress go back to the Azure SQL Server and select “Import/Export history”.

As mentioned under The How there are multiple methods to copy a database so if you’re familiar with azcopy you could for example use that to copy (Click for documentation) the bacpac file to the other subscription’s container. I will stick to the GUI method for this article's purpose. Go to your current Storage Account container, click the bacpac file and click Download.

Now go to your other subscription via the Azure Portal and open the Storage Account Container you want to use there and upload the bacpac file.

Once that is done head over to the Azure SQL Server within this subscription and click Import database.

In the menu that shows up select the bacpac file under Storage, configure the pricing tier if required, set the new database name and enter the credentials from the target Azure SQL Server. Click OK to start the import. Again this can be monitored via the Import/Export history menu.

Once completed you’ve succesfuly copied the database from subscription A to subscription B its Azure SQL Server.

Via a SQL query

Three things to keep in mind for this to work:

  1. This query should be executed on the master database of the target server
  2. You’ll have to make sure you use a login that has the same username and password as the database owner of the source database, and it’s a member of the dbmanager role or is the server-level principal login.
  3. A Fully Qualified Domain Name is not supported by the “AS COPY OF” argument thus you’ll only use the servername, no database.windows.net.

Establish a connection with the target Azure SQL Server using SSMS and open a new query window in the master database. Execute the following query and wait for it to complete, which might take a while depending on the database size.

CREATE DATABASE  azfsdb01 AS  COPY OF  azfsql01.azfsdb01;

Once completed the database will be available on the target Azure SQL Server.

Via Powershell

Establish a connection to Azure via your own Powershell or use the Azure Cloud Shell. Next execute the cmdlet stated below. This cmdlet will copy the azfsdb01 database which resides at Azure SQL Server azfsql01 to Azure SQL Server azfsql02 and name it azfsdb01-copy.

New-AzSqlDatabaseCopy -ResourceGroupName AZF-RSG-SOURCE `
    -ServerName azfsql01 `
    -DatabaseName azfsdb01 `
    -CopyResourceGroupName AZF-RSG-TARGET `
    -CopyServerName azfsql02 `
    -CopyDatabaseName azfsdb01-copy

Synopsis

All roads lead to Rome, that’s for sure. Some methods will allow you to copy between subscriptions/tenants and some require you to stay in the same subscription/tenant. Some can also be used to copy from on-premise to Azure. Pick the method that suits you best and have fun in executing it. Do you know a cool method to copy Azure SQL Databases? Make sure to leave a comment.