复制 Azure SQL 数据库的事务一致性副本Copy a transactionally consistent copy of an Azure SQL database

通过 Azure SQL 数据库,可以以多种方式在相同或不同的服务器上创建现有 Azure SQL 数据库(单一数据库)的事务一致性副本。Azure SQL Database provides several methods for creating a transactionally consistent copy of an existing Azure SQL database (single database) on either the same server or a different server. 可以使用 Azure 门户、PowerShell 或 T-SQL 复制 SQL 数据库。You can copy a SQL database by using the Azure portal, PowerShell, or T-SQL.

概述Overview

数据库副本是源数据库截至复制请求发出时的快照。A database copy is a snapshot of the source database as of the time of the copy request. 你可以选择同一服务器或不同的服务器。You can select the same server or a different server. 另外,你还可以选择保留其服务层级和计算大小,或在同一服务层级中使用不同的计算大小(版本)。Also you can choose to keep its service tier and compute size, or use a different compute size within the same service tier (edition). 在完成该复制后,副本将成为能够完全行使功能的独立数据库。After the copy is complete, it becomes a fully functional, independent database. 此时,可以将其升级或降级为任何版本。At this point, you can upgrade or downgrade it to any edition. 登录名、用户和权限可单独进行管理。The logins, users, and permissions can be managed independently. 副本是使用异地复制技术创建的,一旦种子设定完成,异地复制链接就会自动终止。The copy is created using the geo-replication technology and once seeding is completed the geo-replication link is automatically terminated. 使用异地复制的所有要求都适用于数据库复制操作。All the requirements for using geo-replication apply to the database copy operation. 有关详细信息,请参阅活动异地复制概述See Active geo-replication overview for details.

Note

在创建数据库副本时,将用到自动数据库备份Automated database backups are used when you create a database copy.

数据库副本中的登录名Logins in the database copy

将某个数据库复制到同一 SQL 数据库服务器时,可以在这两个数据库上使用相同的登录名。When you copy a database to the same 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 on the new database.

将数据库复制到其他 SQL 数据库服务器时,由目标服务器上的复制操作启动的安全主体将成为新数据库的所有者。When you copy a database to a different SQL Database server, the security principal that initiated the copy operation on the target server becomes the owner of the new database.

无论目标服务器如何,所有数据库用户、其权限及安全标识符 (SID) 都会复制到数据库副本中。Regardless of the target server, all database users, their permissions, and their security identifiers (SIDs) are copied to the database copy. 使用包含的数据库用户进行数据访问可以确保复制的数据库具有相同的用户凭据,这样在复制完成后,你可以使用相同的凭据立即进行访问。Using contained database users for data access ensures that the copied database has the same user credentials, so that after the copy is complete you can immediately access it with the same credentials.

如果使用服务器级登录名进行数据访问并将数据库复制到其他服务器,则基于登录名的访问可能无法工作。If you use server level logins for data access and copy the database to a different server, the login-based access might not work. 出现这种情况的原因可能是目标服务器上不存在登录名,或者其密码和安全标识符 (SID) 不同。This can happen because the logins do not exist on the target server, or because their passwords and security identifiers (SIDs) are different. 要了解如何在将数据库复制到其他 SQL 数据库服务器时管理登录名,请参阅灾难恢复后如何管理 Azure SQL 数据库安全性To learn about managing logins when you copy a database to a different SQL Database server, see How to manage Azure SQL database security after disaster recovery. 在复制到其他服务器操作成功后,但在重新映射其他用户之前,只有与数据库所有者关联的登录名或服务器管理员才能登录到复制的数据库。After the copy operation to a different server succeeds, and before other users are remapped, only the login associated with the database owner, or the server administrator can log in to the copied database. 若要在复制操作完成后解析登录名并建立数据访问权限,请参阅解析登录名To resolve logins and establish data access after the copying operation is complete, see Resolve logins.

使用 Azure 门户复制数据库Copy a database by using the Azure portal

要使用 Azure 门户复制数据库,请打开数据库页,并单击“复制” 。To copy a database by using the Azure portal, open the page for your database, and then click Copy.

数据库复制

使用 PowerShell 或 Azure CLI 复制数据库Copy a database by using PowerShell or Azure CLI

若要复制数据库,请使用以下示例。To copy a database, use the following examples.

对于 PowerShell,请使用 New-AzSqlDatabaseCopy cmdlet。For PowerShell, use the New-AzSqlDatabaseCopy cmdlet.

Important

PowerShell Azure 资源管理器 (RM) 模块仍受 Azure SQL 数据库支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. AzureRM 模块至少在 2020 年 12 月之前将继续接收 bug 修补程序。The AzureRM module will continue to receive bug fixes until at least December 2020. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 若要详细了解其兼容性,请参阅新 Azure PowerShell Az 模块简介For more about their compatibility, see Introducing the new Azure PowerShell Az module.

New-AzSqlDatabaseCopy -ResourceGroupName "<resourceGroup>" -ServerName $sourceserver -DatabaseName "<databaseName>" `
    -CopyResourceGroupName "myResourceGroup" -CopyServerName $targetserver -CopyDatabaseName "CopyOfMySampleDatabase"

数据库复制是一个异步操作,但在接受请求后会立即创建目标数据库。The database copy is an asynchronous operation but the target database is created immediately after the request is accepted. 如果需要取消仍在进行的复制操作,请使用 Remove-AzSqlDatabase cmdlet 删除目标数据库。If you need to cancel the copy operation while still in progress, drop the the target database using the Remove-AzSqlDatabase cmdlet.

有关完整的示例 PowerShell 脚本,请参阅将数据库复制到新的服务器For a complete sample PowerShell script, see Copy a database to a new server.

用于管理数据库副本的 RBAC 角色RBAC roles to manage database copy

若要创建数据库副本,需要具有以下角色To create a database copy, you will need to be in the following roles

  • “订阅所有者”或Subscription Owner or

  • “SQL Server 参与者”角色或SQL Server Contributor role or

  • 对源和目标数据库具有以下权限的自定义角色:Custom role on the source and target databases with following permission:

    Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/writeMicrosoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write

若要取消数据库复制,需要具有以下角色To cancel a database copy, you will need to be in the following roles

  • “订阅所有者”或Subscription Owner or

  • “SQL Server 参与者”角色或SQL Server Contributor role or

  • 对源和目标数据库具有以下权限的自定义角色:Custom role on the source and target databases with following permission:

    Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/writeMicrosoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write

若要使用 Azure 门户管理数据库副本,还需要以下权限:To manage database copy using Azure portal, you will also need the following permissions:

Microsoft.Resources/subscriptions/resources/read Microsoft.Resources/subscriptions/resources/write Microsoft.Resources/deployments/read Microsoft.Resources/deployments/write Microsoft.Resources/deployments/operationstatuses/readMicrosoft.Resources/subscriptions/resources/read Microsoft.Resources/subscriptions/resources/write Microsoft.Resources/deployments/read Microsoft.Resources/deployments/write Microsoft.Resources/deployments/operationstatuses/read

若要查看门户上资源组中部署下的操作、跨多个资源提供程序的操作(包括 SQL 操作),还需要以下 RBAC 角色:If you want to see the operations under deployments in the resource group on the portal, operations across multiple resource providers including SQL operations, you will need these additional RBAC roles:

Microsoft.Resources/subscriptions/resourcegroups/deployments/operations/read Microsoft.Resources/subscriptions/resourcegroups/deployments/operationstatuses/readMicrosoft.Resources/subscriptions/resourcegroups/deployments/operations/read Microsoft.Resources/subscriptions/resourcegroups/deployments/operationstatuses/read

使用 Transact-SQL 复制数据库Copy a database by using Transact-SQL

使用服务器管理员登录名或创建了要复制的数据库的登录名登录到 master 数据库。Log in to the master database with the server administrator login or the login that created the database you want to copy. 若要成功复制数据库,非服务器管理员的登录名必须是 dbmanager 角色的成员。For database copy to succeed, logins that are not the server administrator must be members of the dbmanager role. 有关登录名和链接到服务器的详细信息,请参阅管理登录名For more information about logins and connecting to the server, see Manage logins.

使用 CREATE DATABASE ...AS COPY OF 语句开始复制源数据库。Start copying the source database with the CREATE DATABASE ... AS COPY OF statement. T-SQL 语句将继续运行,直到数据库复制操作完成。The T-SQL statement continues running until the database copy operation is complete.

Note

终止 T-SQL 语句不会终止数据库复制操作。Terminating the T-SQL statement does not terminate the database copy operation. 若要终止该操作,请删除目标数据库。To terminate the operation, drop the target database.

将 SQL 数据库复制到同一台服务器Copy a SQL database to the same server

使用服务器管理员登录名或创建了要复制的数据库的登录名登录到 master 数据库。Log in to the master database with the server administrator login or the login that created the database you want to copy. 若要成功复制数据库,非服务器管理员的登录名必须是 dbmanager 角色的成员。For database copying to succeed, logins that are not the server administrator must be members of the dbmanager role.

此命令将 Database1 复制到同一服务器上名为 Database2 的新数据库。This command copies Database1 to a new database named Database2 on the same server. 根据数据库的大小,复制操作可能需要一些时间才能完成。Depending on the size of your database, the copying operation might take some time to complete.

-- execute on the master database to start copying
CREATE DATABASE Database2 AS COPY OF Database1;

将 SQL 数据库复制到不同的服务器Copy a SQL database to a different server

登录到要在其中创建新数据库的目标服务器的 master 数据库。Log in to the master database of the target server where the new database is to be created. 所用登录名的名称和密码应该与源服务器上源数据库的数据库所有者的名称和密码相同。Use a login that has the same name and password as the database owner of the source database on the source server. 目标服务器上的登录名还必须是 dbmanager 角色的成员,或者是服务器管理员登录名。The login on the target server must also be a member of the dbmanager role, or be the server administrator login.

此命令将 server1 上的 Database1 复制到 server2 上名为 Database2 的新数据库。This command copies Database1 on server1 to a new database named Database2 on server2. 根据数据库的大小,复制操作可能需要一些时间才能完成。Depending on the size of your database, the copying operation might take some time to complete.

-- Execute on the master database of the target server (server2) to start copying from Server1 to Server2
CREATE DATABASE Database2 AS COPY OF server1.Database1;

Important

必须将两台服务器的防火墙都配置为允许来自发出 T-SQL CREATE DATABASE ...AS COPY OF 命令的客户端 IP 的入站连接。Both servers' firewalls must be configured to allow inbound connection from the IP of the client issuing the T-SQL CREATE DATABASE ... AS COPY OF command.

将 SQL 数据库复制到不同的订阅Copy a SQL database to a different subscription

可以使用将 SQL 数据库复制到其他服务器部分中的步骤使用 T-SQL 将数据库复制到其他订阅中的 SQL 数据库服务器。You can use the steps in the Copy a SQL database to a different server section to copy your database to a SQL Database server in a different subscription using T-SQL. 确保所用登录名的名称和密码与源数据库的数据库所有者的名称和密码相同。Make sure you use a login that has the same name and password as the database owner of the source database. 此外,登录名在源服务器和目标服务器上都必须是 dbmanager 角色的成员或者是服务器管理员。Additionally, the login must be a member of the dbmanager role or a server administrator, on both source and target servers.

Note

Azure 门户、PowerShell 和 Azure CLI 不支持将数据库复制到其他订阅。The Azure portal, PowerShell, and Azure CLI do not support database copy to a different subscription.

监视复制操作的进度Monitor the progress of the copying operation

可以通过查询 sys.databasessys.dm_database_copiessys.dm_operation_status 视图来监视复制过程。Monitor the copying process by querying the sys.databases, sys.dm_database_copies, and sys.dm_operation_status views. 在复制过程中,新数据库的 sys.databases 视图的 state_desc 列将设置为 COPYINGWhile the copying is in progress, the state_desc column of the sys.databases view for the new database is set to COPYING.

  • 如果复制失败,新数据库的 sys.databases 视图的 state_desc 列将设置为 SUSPECTIf the copying fails, the state_desc column of the sys.databases view for the new database is set to SUSPECT. 对新数据库执行 DROP 语句并稍后重试。Execute the DROP statement on the new database, and try again later.
  • 如果复制成功,新数据库的 sys.databases 视图的 state_desc 列将设置为 ONLINEIf the copying succeeds, the state_desc column of the sys.databases view for the new database is set to ONLINE. 复制已完成并且新数据库是一个常规数据库,可独立于源数据库进行更改。The copying is complete, and the new database is a regular database that can be changed independent of the source database.

Note

如果决定在复制过程中取消复制,请对新数据库执行 DROP DATABASE 语句。If you decide to cancel the copying while it is in progress, execute the DROP DATABASE statement on the new database.

Important

如果需要使用比源小得多的服务目标创建副本,则目标数据库可能没有足够的资源来完成种子设定过程,这可能会导致复制操作失败。If you need to create a copy with a substantially smaller service objective than the source, the target database may not have sufficient resources to complete the seeding process and it can cause the copy operaion to fail. 在这种情况下,请使用异地还原请求在不同服务器和/或不同区域中创建副本。In this scenario use a geo-restore request to create a copy in a different server and/or a different region. 有关详细信息,请参阅使用数据库备份恢复 Azure SQL 数据库See Recover an Azure SQL database using database backups for more informaion.

解析登录名Resolve logins

当新数据库在目标服务器上联机后,使用 ALTER USER 语句将新数据库中的用户重新映射到目标服务器上的登录名。After the new database is online on the target server, use the ALTER USER statement to remap the users from the new database to logins on the target server. 若要解析孤立用户,请参阅孤立用户疑难解答To resolve orphaned users, see Troubleshoot Orphaned Users. 另请参阅灾难恢复后如何管理 Azure SQL 数据库安全性See also How to manage Azure SQL database security after disaster recovery.

新数据库中的所有用户都保持他们在源数据库中已有的权限。All users in the new database retain the permissions that they had in the source database. 启动数据库复制过程的用户成为新数据库的数据库所有者。The user who initiated the database copy becomes the database owner of the new database. 复制成功之后,重新映射其他用户之前,只有数据库所有者才能登录到新数据库。After the copying succeeds and before other users are remapped, only the database owner can log in to the new database.

要了解如何在将数据库复制到其他 SQL 数据库服务器时管理用户和登录名,请参阅灾难恢复后如何管理 Azure SQL 数据库的安全性To learn about managing users and logins when you copy a database to a different SQL Database server, see How to manage Azure SQL database security after disaster recovery.

数据库复制错误Database copy errors

在 Azure SQL 数据库中复制数据库时,可能会发生以下错误。The following errors can be encountered while copying a database in Azure SQL Database. 有关详细信息,请参阅复制 Azure SQL 数据库For more information, see Copy an Azure SQL Database.

错误代码Error code 严重性Severity 说明Description
4063540635 1616 IP 地址为“%.*ls”的客户端已暂时禁用。Client with IP address '%.*ls' is temporarily disabled.
4063740637 1616 创建数据库副本当前处于禁用状态。Create database copy is currently disabled.
4056140561 1616 数据库复制失败。Database copy failed. 源数据库或目标数据库不存在。Either the source or target database does not exist.
4056240562 1616 数据库复制失败。Database copy failed. 源数据库已删除。The source database has been dropped.
4056340563 1616 数据库复制失败。Database copy failed. 目标数据库已删除。The target database has been dropped.
4056440564 1616 数据库复制由于内部错误而失败。Database copy failed due to an internal error. 请删除目标数据库,并重试。Please drop target database and try again.
4056540565 1616 数据库复制失败。Database copy failed. 不允许来自同一源的多个并发数据库复制。No more than 1 concurrent database copy from the same source is allowed. 请删除目标数据库,并重试。Please drop target database and try again later.
4056640566 1616 数据库复制由于内部错误而失败。Database copy failed due to an internal error. 请删除目标数据库,并重试。Please drop target database and try again.
4056740567 1616 数据库复制由于内部错误而失败。Database copy failed due to an internal error. 请删除目标数据库,并重试。Please drop target database and try again.
4056840568 1616 数据库复制失败。Database copy failed. 源数据库已变得不可用。Source database has become unavailable. 请删除目标数据库,并重试。Please drop target database and try again.
4056940569 1616 数据库复制失败。Database copy failed. 目标数据库已变得不可用。Target database has become unavailable. 请删除目标数据库,并重试。Please drop target database and try again.
4057040570 1616 数据库复制由于内部错误而失败。Database copy failed due to an internal error. 请删除目标数据库,并重试。Please drop target database and try again later.
4057140571 1616 数据库复制由于内部错误而失败。Database copy failed due to an internal error. 请删除目标数据库,并重试。Please drop target database and try again later.

后续步骤Next steps