Export (0) Print
Expand All
28 out of 40 rated this helpful - Rate this topic

Copying Databases in Azure SQL Database

Updated: April 24, 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, the copy is charged at the same rate as the source database.

In this Topic:

Overview

The database copy feature supports the following scenarios:

  • 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 new database with a different name. If a mistake is made during the upgrade, you can recover the earlier database state by renaming the new database to the application database name.

  • Application migration: You can perform a data-tier application (DAC) export to create an export file and then import it to a database in a different region. To create a transactionally consistent export you must make sure that the source database is not updated during the export. In cases when blocking updates on the source database is not possible, you can create a database copy and then use it to create the export file. Because database copy is transactionally consistent and can be performed in parallel with other database operations you can create the export file without impacting the online operations of the source database. For more information, see How to: Import and Export a Database (Azure SQL Database).

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]

Available Methods to Copy a Database

The following table lists the methods available to create a copy of a database. It includes service tier and cross regions support information. The detailed information about the different methods is described later in the topic.

Transact-SQL support for creating a copy of the databases supports the copies to be created on the same server as the source or a different server. PowerShell and REST API methods only support copying to the same server.

 

Copy Method Premium Standard Basic Business Web Cross Server/Cross Region

Azure Management Portal

Supports only copying to the same server.

Transact-SQL

(CREATE DATABASE statement with the AS COPY OF clause)

√*

Cross Server is supported but both the servers should be in the same region.

REST API

Supports only copying to the same server as the source database. For premium databases you have option of setting up a cross region replication relationship which allows you to maintain copies of the database on servers across geographical regions. For more information, see Configure Active Geo-Replication (Continuous Copy).

PowerShell

Supports only copying to the same server as the source database. For premium databases you have option of setting up a cross region replication relationship which allows you to maintain copies of the database on servers across geographical regions. For more information, see Configure Active Geo-Replication (Continuous Copy).

* The CREATE DATABASE AS COPY OF command is supported for Premium databases that are created on servers that support Premium, Web and Business databases. If the Premium database is on a server that supports Basic, Standard, and Premium, CREATE DATABASE AS COPY OF will return an error.

Copying a Database

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.

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]

Copying a Premium Database

To copy a Premium Database, you can use the methods described in the earlier sections. The new database is also a Premium database with the performance level. Thus, you must have available Premium database quota on the server..

You can use the PowerShell cmdlets to create a copy of the database. The Start-AzureSqlDatabaseCopy cmdlet can be used to create a database copy when used without specifying the –ContinuousCopy parameter.

For more information, see Start-AzureSqlDatabaseCopy

Related Tasks

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft. All rights reserved.