Copy a transactionally consistent copy of an Azure SQL database

Azure SQL Database provides several methods for creating a transactionally consistent copy of an existing Azure SQL database on either the same server or a different server. 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.

Note

Automated database backups are used when you create a database copy.

Logins in the database copy

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. All database users, their permissions, and their security identifiers (SIDs) are copied to the database copy.

When you copy a database to a different SQL Database server, the security principal on the new server becomes the database owner on the new database. If you use contained database users for data access, ensure that both the primary and secondary databases always have the same user credentials, so that after the copy is complete you can immediately access it with the same credentials.

If you use Azure Active Directory, you can completely eliminate the need for managing credentials in the copy. However, when you copy the database to a new server, the login-based access might not work, because the logins do not exist on the new server. 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 copying succeeds and before other users are remapped, only the login that initiated the copying, the database owner, can log in to the new database. To resolve logins after the copying operation is complete, see Resolve logins.

Copy a database by using the Azure portal

To copy a database by using the Azure portal, open the page for your database, and then click Copy.

Database copy

Copy a database by using PowerShell

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

Important

The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

To copy a database by using PowerShell, use the New-AzSqlDatabaseCopy cmdlet.

New-AzSqlDatabaseCopy -ResourceGroupName "myResourceGroup" `
    -ServerName $sourceserver `
    -DatabaseName "MySampleDatabase" `
    -CopyResourceGroupName "myResourceGroup" `
    -CopyServerName $targetserver `
    -CopyDatabaseName "CopyOfMySampleDatabase"

For a complete sample script, see Copy a database to a new server.

Copy a database by using Transact-SQL

Log in to the master database with the server-level principal login or the login that created the database you want to copy. For database copying to succeed, logins that are not the server-level principal must be members of the dbmanager role. For more information about logins and connecting to the server, see Manage logins.

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 copying is complete.

Copy a SQL database to the same server

Log in to the master database with the server-level principal login or the login that created the database you want to copy. For database copying to succeed, logins that are not the server-level principal must be members of the dbmanager role.

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.
-- Start copying.
CREATE DATABASE Database2 AS COPY OF Database1;

Copy a SQL database to a different server

Log in to the master database of the destination server, the 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 of the source database on the source 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.

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)
-- Start copying from Server1 to Server2
CREATE DATABASE Database2 AS COPY OF server1.Database1;

Important

Both servers' firewalls must be configured to allow inbound connection from the IP of the client issuing the T-SQL COPY command.

Copy a SQL database to a different subscription

You can use the steps descrbed in the previous section to copy your database to a SQL Database server in a different subscription. Make sure you use a login that has the same name and password as the database owner of the source database and it is a member of the dbmanager role or is the server-level principal login.

Note

The Azure portal does not support copy to a different subscription because Portal calls the ARM API and it uses the subscription certificates to access both servers involved in geo-replication.

Monitor the progress of the copying operation

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

Resolve logins

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. 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 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, can log in to the new database.

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.

Next steps