Copying Databases in Azure SQL Database
Updated: July 31, 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, 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.
In this Topic
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 Methods 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.
|Databases created by the copy feature count toward the Azure SQL Database limit of databases for each Azure SQL Database server. For cost and billing impact, see Azure SQL Database Accounts and Billing.|
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 gives examples of both options.
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 shows same-server copying:
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
You can also copy a database between two different Azure SQL Database servers that are in the same or different regions. 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 shows cross-server copying:
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.
|An Azure SQL Database region might consist of multiple physical clusters. Currently you cannot copy a database between two different clusters using Transact-SQL. 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.
Other Methods to Copy a Database
Using the Database Copy feature (DB Copy) is a quick and easy way to create a copy of your database. If this does not meet your needs, consider using one of the following alternative 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 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.
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.|