Dışarıya aktar (0) Yazdır
Tümünü Genişlet
EN
Bu içerik dilinizde bulunmamaktadır ancak İngilizce sürümüne buradan bakabilirsiniz.

How to: Use Database Copy (Azure SQL Database)

Updated: July 31, 2014

This topic describes how to use Database Copy (DB Copy) to make a copy of your Microsoft Azure SQL Database. The new database that is created as a result of the copy operation is a fully functioning and independent copy of the source database. The copy operation preserves the service tier and performance level of the source database. Ensure the target server has available quota, otherwise the operation will fail. For all the available methods and scenarios for copying databases, see Copying Databases in Azure SQL Database.

In This Topic

Before You Begin

Methods

The following table lists the methods available to create a copy of a database.

 

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 cross server and cross region copying. 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 cross server and cross region copying. 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.

Restrictions

  • An Azure SQL Database region might consist of multiple physical clusters. Currently, you can only copy a database between two different clusters using PowerShell cmdlets or REST API. Also, you can only copy a database between two different subscriptions using Transact-SQL. 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.

    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.

  • Copying databases can be done using Transact-SQL, PowerShell, or REST API. However, when using Transact-SQL, you are limited to copying between different servers within the same cluster. PowerShell and REST API support copying to a different server in a different physical cluster.

Timing of Copy Operations

The database copy workload affects the performance of the Azure SQL Database servers involved in the copying process. Therefore, the copying process can require an extended time to complete. If the database copy fails, restart the copy 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.

Permissions

In order to copy a database using Transact-SQL, 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. When copying a database using PowerShell or REST API, permissions use certificate-based authentication.

noteNote
Changing the database owner by using the ALTER AUTHORIZATION ON DATABASE statement is not supported by Azure SQL Database. 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.

ImportantImportant
Database auditing settings are not copied over to the new database. If you need auditing on the new database, you must enable it once the database is active. If the new database is in the same region as the original database, you can use the same Azure storage account. If the new database is in a different region than the original database, you should 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.

Arrow icon used with Back to Top link [Top]

Copy to the same server (Azure Management Portal)

To copy your database to a new database on the same server

  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 then in the command bar at the bottom of the screen click Copy. This opens the Copy Database Settings dialog box. Specify a name for the new database, and then in the dialog box click Check to start the copy operation.

Copy to the same server (PowerShell)

To copy your database to a new database on the same server

  1. Start PowerShell, and then 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 Geo-Replication. For more information on Geo-Replication, see Azure SQL Database Business Continuity.

  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"
    
    

Copy to the same server (Transact-SQL)

To copy your database to a new database on the same server

  • 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 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 then 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.

Example (Transact-SQL)

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 following statement 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]

Copy to a different server (Transact-SQL)

To copy your database to a new database on a different logical server

  • 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.

Example (Transact-SQL)

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 following statement 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 following statement 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]

Stop the copying process (Transact-SQL)

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]

Follow Up: After Copy Completes

  • 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 then 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

Topluluk İçeriği

Ekle
Show:
© 2014 Microsoft