Skip to content

Latest commit

 

History

History
78 lines (57 loc) · 5.79 KB

sql-database-copy-transact-sql.md

File metadata and controls

78 lines (57 loc) · 5.79 KB
title description services documentationcenter author manager editor ms.assetid ms.service ms.custom ms.devlang ms.date ms.author ms.workload ms.topic ms.tgt_pltfrm
Copy an Azure SQL database using Transact-SQL | Microsoft Docs
Create copy of an Azure SQL database using Transact-SQL
sql-database
CarlRabeler
jhubbard
40ea3718-33f8-41af-90cb-3aa15059365e
sql-database
migrate and move
NA
02/07/2017
carlrab
data-management
article
NA

Copy an Azure SQL database using Transact-SQL

The following steps show you how to copy a SQL database with Transact-SQL to the same server or a different server. The database copy operation uses the CREATE DATABASE statement.

Note

You can also copy a SQL database using the Azure portal or PowerShell.

To complete the steps in this article, you need the following:

Copy your SQL database

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 to copy databases. 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 completes copying.

Copy a SQL database to 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 to copy databases.

This command copies Database1 on to a new database named Database2 on the same server. Depending on the size of your database the copy operation may take some time to complete.

-- Execute on the master database.
-- Start copying.
CREATE DATABASE Database1_copy AS COPY OF Database1;

Copy a SQL database to a different 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.

This command copies Database1 on server1- to a new database named Database2 on server2. Depending on the size of your database the copy operation may take some time to complete.

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

Monitor the progress of the copy 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. 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.

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 copy operation completes

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

Next steps