Export (0) Print
Expand All

Copying Databases in Azure SQL Database

Updated: June 12, 2014

The database copy operation copies a Microsoft Azure SQL Database to a new database. The copy can be created on the same or a different logical server. When the copying process completes, the new database is a fully functioning database that is independent of the source database. The new database is transactionally consistent with the source database at the time when the copy completes. The service tier, size, and the performance level of the database of the copy are the same as the source database.

Since the new database is the same service tier and performance level of the database. Each copy adds to the total database count and is billed at the same rate as the source database. For more information, see SQL Database Pricing Details.

In this Topic:

Overview

The following scenarios require making a copy of your source database to a new database. The methods used in these scenarios depend on whether or not the source and destination databases reside in the same server or different server, same region or different region, same subscription or different subscriptions. These methods are described in details in the Other Methods to Copy a Database section later in this topic.

  • Application development and testing: Copy the production database to a new database that can be used for development and testing.

  • Application upgrade: Before major application updates, copy the application database to a backup database with a different name. If the upgrade process fails or completes with errors, you can switch to the pre-upgrade version of the database by simply renaming the backup database to the application database name.

  • Application migration: You may need to migrate your application which involves moving your database as well.

noteNote
Databases created by the copy feature count toward the Azure SQL Database limit of 150 databases for each Azure SQL Database server. For cost and billing impact, see Azure SQL Database Accounts and Billing.

Arrow icon used with Back to Top link [Top]

Using Database Copy

When using the database copy feature, databases are copied asynchronously, so a connection to the Azure SQL Database server is not needed for the full duration of the process. You can copy a database by logging into the master database of the destination server and executing the Transact-SQL CREATE DATABASE statement with the AS COPY OF clause. Then, you can monitor the copy process by using the sys.dm_database_copies and sys.databases views on the destination server.

You can copy a database to the same Azure SQL Database server, using a different database name, or you can copy the database to a different Azure SQL Database server. This section considers both of these alternatives.

Same-Server Copying

When you copy a database to make a new database on the same Azure SQL Database server, the same logins can be used on both databases. The security principal you use to copy the database becomes the database owner (DBO) on the new database when it is created. The following figure illustrates same-server copying:

Copy database to the same SQL Database server

In this figure, Database1A is copied to a new database, Database1B, on the same Azure SQL Database server, Server1. The login that copied the database becomes the DBO of Database1B. All database users, their permissions, and their security identifiers (SIDs) from Database1A are copied to Database1B. Because the user SIDs are the same on both databases, the logins from Server1 maintain the same permissions on both databases.

After the copy is complete, Database1B becomes a fully functional, independent database. The logins, users, and permissions of Database1B can be managed independently of Database1A.

Cross-Server Copying

You can also copy a database between two different Azure SQL Database servers that are in the same region. Because the new database is created on a different Azure SQL Database server, it is associated with a different master database. All users in the new database maintain the permissions that they had in the source database. The security principal you use to copy the database becomes DBO on the new database when it is created and is assigned a new security identifier (SID). The following figure illustrates cross-server copying:

Copy database to a different SQL Database server

In this figure, Database1A is copied from Server1 to a new database, Database2A, on a different Azure SQL Database server, Server2. The login that copied the database becomes the DBO of Database2A. All database users and their permissions (but not their SIDs) from Database1A are copied to Database2A. The logins from Server1 cannot be used with the new database because they are associated with a different Azure SQL Database server and because the Database2A user SIDs are different from the Database1A user SIDs.

ImportantImportant
An Azure SQL Database region might consist of multiple physical clusters. Currently you cannot copy a database between two different clusters. Also, you cannot copy a database between two different subscriptions. For more information regarding restrictions, see Restrictions.

After the cross-server copy process is complete, the logins, users, and permissions of Database2A can be managed independently of Database1A. Use the DBO login and the Transact-SQL ALTER USER statement to map users in the new database to logins on the new Azure SQL Database server. For example: ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER.

Arrow icon used with Back to Top link [Top]

Other Methods to Copy a Database

Copying a database using the Database Copy feature (DB Copy) is the most straightforward method but considering its restrictions described earlier in some cases you may need to consider using one of the alternative methods. .

A few things to note about the supported options:

Point in Time Restore allows you to create a copy of a past version of the database. For example, if you need to create a copy of the database version prior to the last upgrade. Point in Time Restore is available only in the new service tiers. For more information on Point in Time Restore, see Azure SQL Database Backup and Restore.

Active Geo-Replication is available only on databases using the Premium service tier. It allows you to control the timing of the copy completion. You should use it when you need close coordination of the end of copy with other actions in your workflow. For more information, see Active Geo-Replication for Azure SQL Database.

Import/Export service should be considered when other options are not available in your particular scenario. For information on Import/Export, see How to: Import and Export a Database (Azure SQL Database).

The table below lists the options available for different use cases:

 

Case Subscription Region Server Supported Options

Case A

Same

Same

Same

  • DB Copy (T-SQL, REST, or PowerShell)

  • Point in Time Restore

Case B

Same

Same

Different

  • DB Copy (T-SQL)

  • DB Copy (T-SQL, REST, or PowerShell) to the same server + Export/Import

  • Point In Time Restore to the same server + Export/Import

  • Active Geo Replication

Case B

Same

Different

Different

  • DB Copy (T-SQL, REST, or PowerShell) to the same server + Export/Import

  • Point in Time Restore to the same server + Export/Import

  • Active Geo-Replication

Case C

Different

Same

Different

  • DB Copy (T-SQL, REST, or PowerShell) to the same server + Export/Import

  • Point In Time Restore to the same server + Export/Import

Case C

Different

Different

Different

  • DB Copy (T-SQL, REST, or PowerShell) to the same server + Export/Import

  • Point in Time Restore to the same server + Export/Import

Use Case A
When source and destination are within the same server, it’s the most straightforward way to copy database using DB Copy or Point in Time Restore. As described earlier, Point in Time will allow you to create a copy of the database to a point in time in the past, while DB Copy will copy database as of current time.

Use Case B
When the source and destination are in the same subscriptions, but different servers,DB Copy using T-SQL can be used to copy to a different logical server. However if the logical server is on a different physical cluster, the copy will fail. To determine if the logical servers are in the same cluster, use 'Ping' against both servers to check if they share the same IP address. If the destination server is on a different cluster, then use the following steps:

  1. Create an intermediate (staging) copy of the database in the same server as the source, You can use either DB Copy or Point in Time Restore.

  2. Export the copy to a BACPAC file in Azure BLOB Storage.

  3. Import the BACPAC file created in Step 2 into a database in the desired subscription/region/server.

  4. Drop the intermediate (staging) database created in Step 1.

For Premium Databases: As an alternative, you may choose to use Active Geo-Replication to create a secondary database as a copy of the source database. The following steps are required to create a database copy:

  1. Create a secondary database in the target server.

  2. When the replication process is in the catch-up state stop the secondary.

  3. The new copy becomes an independent database available for read and write operations.

  4. If the copy needs to have a different name from the source database rename the copy.

Use Case C
When the source and destination are in different subscriptions, irrespective of same regions or different regions, you can use the process described for Use Case B, with the exception of Active Geo-Replication. Active Geo-Replication does not work across subscriptions.

The following sections describe how the DB copy feature works when copying to the same or different logical server.

Related Tasks

See Also

Community Additions

ADD
Show:
© 2014 Microsoft