Eksportér (0) Udskriv
Udvid alt
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

How to: Use Database Copy (Azure SQL Database)

Updated: June 12, 2014

This topic is about how to use Database Copy (DB Copy) to make copy your Microsoft Azure SQL Database. The new database that is created as a result of the copy operation is a regular Azure SQL Database database, fully functioning and independent of the source database. The copy is created with the same service tier and performance level as the source database. For all the available methods and scenarios for copying databases, see Copying Databases in Azure SQL Database.

In This Topic

The following table lists the methods available to create a copy of a database. It includes service tier and cross regions support information.

 

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

Azure Management Portal

Supports only copying to the same server.

ImportantImportant
See the restriction section below for more information

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 Available Methods to Copy a Database.

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 Available Methods to Copy a Database.

  • 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. As part of the capacity management, Azure SQL Database may periodically move your server to a different cluster. However, the servers belonging to the same subscription are kept together within the same cluster.



    If you copy a database between two clusters belonging to different subscriptions, the copy operation may fail with the following error:

    Msg 40532 - Cannot open server “<server name>" requested by the login. The login failed.

    To determine whether copying a database is possible:  Use a ping command ("ping <server>") on both the source and destination servers to resolve their IP addresses. If the IP addresses are equal, the database copy is possible. For more information, see Using the Ping Command.

    Workaround procedure:  If copying the database is not possible, do the following:

    1. Create two new logical servers in the same subregion using the same subscription (the servers should receive the same IP address).

    2. Perform an offline database migration to move the database to one of these servers. You can use various tools, such as the Import/Export Service. For more information, see SQL Database Import/Export Service or How to: Migrate a Database by Using the Generate Scripts Wizard (SQL Database).

  • Copying databases can be done using Transact-SQL, PowerShell, or REST API. However only the Transact-SQL method allows you to copy a database to a different server, but on the same physical cluster. PowerShell and REST API only support copying to the same server as the source database.

The database copy workload affects the performance of the Azure SQL Database server or servers involved in the copying process. Therefore, the copying process can require an extended time to complete. If the database copying fails, restart the copying process when the demand on your source database is low. You might need to suspend other workloads on the source database until the copying process completes.

In order to copy a database in Azure SQL Database, your login requires the following permissions:

  • On both servers: The login must have the same login name and password on both Azure SQL Database servers.

  • On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your Azure SQL Database server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in Azure SQL Database, see Managing Databases and Logins in Azure SQL Database.

  • On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, or the server level principal can copy the database to another database.

When these permission requirements are met, your login can execute the Azure SQL Database ALTER DATABASE and DROP DATABASE statements against the new database.

noteNote
Changing the database owner by using the Azure SQL Database ALTER AUTHORIZATION ON DATABASE statement is not supported by Azure SQL Database. Like SQL Server, you can create additional users inside the database in Azure SQL Database and add them to the db_owner database-level role. These additional users can be linked to a login other than the login that created the database initially. However, these additional users cannot perform a database copy.

Premium quota: The copy operation preserves the service tier and performance level of the source database. When copying a Premium database you must make sure that the target logical server has available Premium quota. Otherwise the operation will fail.

Arrow icon used with Back to Top link [Top]

  1. Sign in to the Azure Management Portal using your Microsoft account.

  2. Navigate to the SQL DATABASES tab.

  3. Highlight the database from the Databases list and click the copy button from the command bar at the bottom of the screen. This opens the Copy Database Settings dialog. Specify a name for the new database and click the check button on the dialog to start the copy operation.

  1. Start PowerShell, and connect to your Azure subscription.

  2. Use the Start-AzureSqlDatabaseCopy cmdlet to create a copy of your database.

    Note that the –ContinuousCopy parameter for this cmdlet is not used in this case. This parameter is only used to set up Active Geo-Replication for a Premium database. For more information on configuring an Active Geo-Replication, see Configure Active Geo-Replication (Continuous Copy).

  3. Usage Example: This example copies the database "Orders" to the database "Orders Copy". The original database and the copy reside on the same server, "abc".

    PS C:\>Start-AzureSqlDatabaseCopy -ServerName "abc" -DatabaseName "Orders" -PartnerDatabase "Orders Copy"
    
    

  • Log on to the master database using the server-level principal login or the login that created the database you want to copy. Logins that are not the server-level principal must be members of the dbmanager role in order to copy databases. For more information about logins and connecting to the server, see Managing Databases and Logins in Azure SQL Database and Azure SQL Database Development: How-to Topics, respectively.

  • Start copying the source database with the Azure SQL Database CREATE DATABASE statement. Executing this statement initiates the database copying process. Because copying a database is an asynchronous process, the CREATE DATABASE statement returns before the database completes copying.

  • Monitor the copying process by querying the sys.databases and sys.dm_database_copies views.

    • While the copying is in progress, the state_desc column of the sys.databases view for the new database is set to COPYING.

    • If the copying fails, the state_desc column of the sys.databases view for the new database is set to SUSPECT. In this case, execute the DROP statement on the new database, and try again later.

    • If the copying succeeds, the state_desc column of the sys.databases view for the new database is set to ONLINE. In this case, the copying is complete and the new database is a regular database, able to be changed independent of the source database.

  • (Optional) If you decide to cancel the copying while it is in progress, execute the DROP DATABASE statement on the new database. Alternatively, executing the DROP DATABASE statement on the source database also cancels the copying process.

The following code example initiates the database copying process. The example copies database Database1A to a new database named Database1B on the same Azure SQL Database server.

-- Execute on the master database.
-- Start copying.
CREATE DATABASE Database1B AS COPY OF Database1A;

The following code examples can be used to monitor the database copying process by querying the sys.databases and sys.dm_database_copies views for information about the new database named Database1B.

-- Execute on the master database.
-- Retrieve state of the new database, Database1B.
SELECT name, state, state_desc FROM sys.databases WHERE name = 'Database1B'

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve copying details.
SELECT * FROM sys.dm_database_copies copies
Inner JOIN sys.databases databases ON copies.database_id = databases.database_id
      WHERE databases.name = 'Database1B';

Arrow icon used with Back to Top link [Top]

  • Log on to the master database of the destination server, the Azure SQL Database server where the new database is to be created. Use a login that has the same name and password as the database owner (DBO) of the source database on the source Azure SQL Database server. The login on the destination server must also be a member of the dbmanager role or be the server-level principal login. For more information about logins and connecting to the server, see Managing Databases and Logins in Azure SQL Database and Azure SQL Database Development: How-to Topics, respectively.

  • Start copying the source database with the Azure SQL Database CREATE DATABASE statement. Executing this statement on the destination server initiates the database copying process. Because copying a database is an asynchronous process, the CREATE DATABASE statement returns before the database completes copying.

  • Monitor the copying process by querying the sys.databases and sys.dm_database_copies views.

    • While the copying is in progress, the state_desc column of the sys.databases view for the new database is set to COPYING.

    • If the copying fails, the state_desc column of the sys.databases view for the new database is set to SUSPECT. In this case, execute the DROP statement on the new database and try again later.

    • If the copying succeeds, the state_desc column of the sys.databases view for the new database is set to ONLINE. In this case, the copying is complete and the new database is a regular database, able to be changed independent of the source database.

  • (Optional) If you decide to cancel the copying while it is in progress, execute the DROP DATABASE statement on the new database. Alternatively, executing the DROP DATABASE statement on the source database also cancels the copying process.

  • (Optional) After the new database is online on the destination server, use the ALTER USER statement to remap the users from the new database to logins on the destination server. All users in the new database maintain the permissions that they had in the source database. The user who initiated the database copy becomes the database owner of the new database and is assigned a new security identifier (SID). After the copying succeeds and before other users are remapped, only the login that initiated the copying, the database owner (DBO), can log on to the new database.

The following code example initiates the database copying process from bipk56ex. The example demonstrates copying database Database1A from the Azure SQL Database server named vipk56ex5c to a new database named Database2A on Server2.

-- Execute on the master database of bipk56ex
-- Start copying from Server1 to Server2
CREATE DATABASE Database2A AS COPY OF vipk56ex5c.Database1A;

The following code example monitors the database copying process. The example queries the sys.databases and sys.dm_database_copies views on Server2 for information about the new database named Database2A.

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve state of the new database, Database1B.
SELECT name, state, state_desc FROM sys.databases WHERE name = 'Database1B'

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve copying details.
SELECT * FROM sys.dm_database_copies copies
Inner JOIN sys.databases databases ON copies.database_id = databases.database_id
      WHERE databases.name = 'Database1B';

Arrow icon used with Back to Top link [Top]

If you want to stop the copying process before it completes, you can use the DROP DATABASE statement to drop the destination database. In order for this to work, use a login that meets the permission requirements as described in Permissions, earlier in this topic.

Arrow icon used with Back to Top link [Top]

  • When the database copying completes, the new database enters one of two states, as follows:

    • If the copying process succeeded, the new database enters the ONLINE state. After the new database is online, it can be managed and used independent of the source database.

    • If the copying process failed, the new database enters the SUSPECT state. The suspect database is unavailable. It cannot be accessed or recovered. In this case, drop the destination database, and try copying the source database again.

    To view the state, query the state_desc column of the sys.databases view.

  • When the copy is complete, the new database has the same service tier, performance level and maximum size as the source database. For information about how to set and change the service tier or performance level in Azure SQL Database, see Changing Database Service Tiers and Performance Levels.

  • After a cross-server copy is complete, the logins, users, and permissions of the copy can be managed independently of the source database. Use the DBO login and the 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 (Azure SQL Database).

Arrow icon used with Back to Top link [Top]

See Also

Vis:
© 2014 Microsoft