Export (0) Print
Expand All

Copying Databases in Azure SQL Database

Updated: July 10, 2015

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, max size, and performance level of the database 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.

Databases created by the copy feature count toward the Azure SQL Database limit of databases for each Azure SQL Database server.

The following scenarios may require making a copy of your source database. The methods used in these scenarios depend on whether the source and destination databases reside on the same server or different servers, same region or different regions, same subscription or different subscriptions. These methods are described in detail in the Other ways to copy a database section later in this topic.

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

  • Application upgrade: Before major application updates, you can 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: When migration application, it may be necessary to also migrate the associated databases.

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 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 logical server using a different database name or you can copy the database to a different logical server. This section gives examples of both options.

When you copy a database to make a new database on the same logical 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.

For example, consider the case where you copy a database, Database1A, to a new database, Database1B, on the same Azure SQL Database logical 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.

You can also copy a database between two different logical servers that are in the same or different regions. Because the new database is created on a different logical 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).

For example, consider the case where you copy a database, Database1A, Server1 to a new database, Database2A, on a different logical 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 logical server and because Database2A user SIDs are different from Database1A user SIDs.

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 ALTER USER statement to map users in the new database to logins on the new logical server. For example: ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER.

An Azure SQL Database region might consist of multiple physical clusters. You can only copy a database between two different clusters using PowerShell cmdlets or REST API. Also, you can only use Transact-SQL to copy a database between subscriptions. A logical server may occasionally move to a different physical cluster, but servers belonging to the same subscription are kept together within the same cluster. Use a ping command ("ping <server>") on both the source and destination servers to discover their IP addresses. If they’re the same, you can use database copy. For more information, see Using the Ping Command.

Using the Database Copy feature (DB Copy) is quick and easy. If it doesn’t work in your situation, consider these alternatives:

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 an upgrade. Point in Time Restore is available only in Basic, Standard, and Premium 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.

The Import/Export service should be considered when other options are not available in your particular scenario. Using Database Copy to create a copy of the database prior to exporting to your final location ensures the export is transactionally consistent. For information on Import/Export, see How to: Import and Export a Database (Azure SQL Database)

Database auditing settings are not copied over to the new database regardless of which copy method you choose. If you need database auditing for the new database, you must enable it once the database is active and online. Also, if you create the new database in a different region, use a storage account that is located in the same region as the new database. For more information on auditing for Azure SQL Database, see Database Auditing.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft