Copy a transactionally consistent copy of a database in Azure SQL Database
Applies to: Azure SQL Database
Azure SQL Database provides several methods for creating a copy of an existing database on either the same server or a different server. You can copy a database by using Azure portal, PowerShell, Azure CLI, or Transact-SQL.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
A database copy is a transactionally consistent snapshot of the source database at the point in time when the copy request is initiated. You can select the same server or a different server for the copy. Also you can choose to keep the backup redundancy and compute size of the source database, or use a different backup storage redundancy and/or compute size within the same service tier. It's also possible to copy a database in the Standard service tier to either the Standard or General Purpose tier and a database in the Premium service tier to either the Premium or Business Critical tier.
After the copy is complete, the new database is a fully functional and independent database to the source database. The logins, users, and permissions in the copied database are managed independently from the source database. The copy is created using geo-replication technology. Once replica seeding is complete, 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
The Azure portal, PowerShell, and the Azure CLI don't support database copy to a different subscription.
For databases in the Hyperscale service tier, the target database determines whether the copy is a fast copy, or a size-of-data copy:
Fast copy: When the copy is done in the same region as the source, the copy is created from the snapshots of blobs, this copy is a fast operation regardless of the database size.
Size-of-data copy: When the target database is in a different region than the source or if the database backup storage redundancy (Local, Zonal, Geo) from the target differs from the source database, the copy operation is a size-of-data operation. Copy time isn't directly proportional to size, as page server blobs are copied in parallel.
When you copy a database to the same 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.
When you copy a database to a different server, the security principal that initiated the copy operation on the target server becomes the owner of the new database.
Regardless of the target server, all database users, permissions, and 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. This can happen because the logins don't exist on the target server, or because those passwords and security identifiers (SIDs) are different. For more information about managing logins when you copy a database to a different 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.
To copy a database by using the Azure portal, open the page for your database, and then choose Copy to open the Create SQL Database - Copy database page. Fill in the values for the target server where you want to copy your database to.
You can copy a database by using PowerShell, the Azure CLI, and Transact-SQL (T-SQL).
For PowerShell, use the New-AzSqlDatabaseCopy cmdlet.
Important
The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. The AzureRM module will continue to receive bug fixes until at least December 2020. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 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. If you need to cancel the copy operation while still in progress, drop the target database using the Remove-AzSqlDatabase cmdlet.
For a complete sample PowerShell script, see Copy a database to a new server.
Monitor the copying process by querying the sys.databases, sys.dm_database_copies, and sys.dm_operation_status 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 thesys.databases
view for the new database is set toSUSPECT
. Execute the DROP statement on the new database, and try again later. - If the copying succeeds, the
state_desc
column of thesys.databases
view for the new database is set toONLINE
. The copying is complete, and the new database is a regular database that can be changed independent of the source database.
Note
If you decide to cancel the copying while it's 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 might not have sufficient resources to complete the seeding process and it can cause the copy operation to fail. In this scenario use a geo-restore request to create a copy in a different server and/or a different region. For more information, see Recover an Azure SQL Database using database backups.
To create a database copy, you need to be in the following roles:
- Subscription Owner or
- SQL Server Contributor role or
- Custom role on the source server with following permissions:
- Microsoft.Sql/servers/databases/read
- Microsoft.Sql/servers/databases/write and
- Custom role on the target server with following permissions:
- Microsoft.Sql/servers/read
- Microsoft.Sql/servers/databases/read
- Microsoft.Sql/servers/databases/write
To cancel a database copy, you need to be in the following roles:
- Subscription Owner or
- SQL Server Contributor role or
- Custom role on the target database with following permission:
- Microsoft.Sql/servers/databases/delete
To manage database copy using the Azure portal, you also need the following permissions:
- Microsoft.Resources/subscriptions/resources/read
- Microsoft.Resources/deployments/read
- Microsoft.Resources/deployments/write
- Microsoft.Resources/deployments/operationstatuses/read
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 need these additional permissions:
- Microsoft.Resources/subscriptions/resourcegroups/deployments/operations/read
- Microsoft.Resources/subscriptions/resourcegroups/deployments/operationstatuses/read
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. 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 sign in to the new database.
To learn about managing users and logins when you copy a database to a different server, see How to manage Azure SQL Database security after disaster recovery.
The following errors can be encountered while copying a database in Azure SQL Database. For more information, see Copy an Azure SQL Database.
Error code | Severity | Description |
---|---|---|
40635 | 16 | Client with IP address '%.*ls' is temporarily disabled. |
40637 | 16 | Create database copy is currently disabled. |
40561 | 16 | Database copy failed. Either the source or target database does not exist. |
40562 | 16 | Database copy failed. The source database has been dropped. |
40563 | 16 | Database copy failed. The target database has been dropped. |
40564 | 16 | Database copy failed due to an internal error. Please drop target database and try again. |
40565 | 16 | Database copy failed. No more than 1 concurrent database copy from the same source is allowed. Please drop target database and try again later. |
40566 | 16 | Database copy failed due to an internal error. Please drop target database and try again. |
40567 | 16 | Database copy failed due to an internal error. Please drop target database and try again. |
40568 | 16 | Database copy failed. Source database has become unavailable. Please drop target database and try again. |
40569 | 16 | Database copy failed. Target database has become unavailable. Please drop target database and try again. |
40570 | 16 | Database copy failed due to an internal error. Please drop target database and try again later. |
40571 | 16 | Database copy failed due to an internal error. Please drop target database and try again later. |