Copying Databases in Windows Azure SQL Database
Updated: November 27, 2013
After you create a database in Windows Azure SQL Database, you can copy it to a new database on the same of a different SQL Database server. When the copying process completes, the new database is a fully functioning database that is independent of the source database. The new database is transactionally consistent with the source database as of the time when the copy completes. The completion information in the row is updated every 5 minutes. The new database has the same edition and maximum size as the source database.
Since the new database is a transactionally consistent copy of the source database, it could potentially be used as a backup option for the Windows Azure SQL Database database. For more information on backup strategies for Windows Azure SQL Database, see Windows Azure SQL Database Backup and Restore.
In this Topic:
The database copy feature provides many of the back-up scenarios that are provided for on-premises databases:
Application data back up: Copy the application database to a new database with a different name to help protect your data from user and application errors. You can recover the application database state by renaming the new database to the application database name. Alternatively, you can recover a specific subset of data from the new database by using Transact-SQL queries.
Application development and testing: Copy the production database to a new database that can be used for development and testing.
Application upgrade: Before major application updates, copy the application database to a new database with a different name. If a mistake is made during the upgrade, you can recover the earlier database state by renaming the new database to the application database name.
Application migration: The database copy feature creates a transactionally consistent copy of the database. You can perform a data-tier application (DAC) export to create an export file. The export file will have both the definitions of the objects in the database and the data from the user tables. You can then import the export file to another SQL Database server or to an instance of the SQL Server Database Engine. For more information, see How to: Import and Export a Database (Windows Azure SQL Database).
|Databases created by the copy feature count toward the SQL Database limit of 150 databases for each SQL Database server.|
Copying a Database
Databases are copied asynchronously, so a connection to the SQL Database server is not needed for the full duration of the process. You can copy a database by logging into the master database of the destination server and executing the Transact-SQL CREATE DATABASE statement with the AS COPY OF clause. Then, you can monitor the copy process by using the sys.dm_database_copies and sys.databases views on the destination server.
You can copy a database to the same SQL Database server, using a different database name, or you can copy the database to a different SQL Database server. This section considers both of these alternatives.
When you copy a database to make a new database on 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 (DBO) on the new database when it is created. The following figure illustrates same-server copying:
In this figure,
Database1A is copied to a new database,
Database1B, on the same SQL Database server,
Server1. The login that copied the database becomes the DBO of
Database1B. All database users, their permissions, and their security identifiers (SIDs) from
Database1A are copied to
Database1B. Because the user SIDs are the same on both databases, the logins from
Server1 maintain the same permissions on both databases.
After the copy is complete,
Database1B becomes a fully functional, independent database. The logins, users, and permissions of
Database1B can be managed independently of
You can also copy a database between two different SQL Database servers that are in the same sub-region or data center. Because the new database is created on a different SQL Database server, it is associated with a different master database. All users in the new database maintain the permissions that they had in the source database. The security principal you use to copy the database becomes DBO on the new database when it is created and is assigned a new security identifier (SID). The following figure illustrates cross-server copying:
In this figure,
Database1A is copied from
Server1 to a new database,
Database2A, on a different SQL Database server,
Server2. The login that copied the database becomes the DBO of
Database2A. All database users and their permissions (but not their SIDs) from
Database1A are copied to
Database2A. The logins from
Server1 cannot be used with the new database because they are associated with a different SQL Database server and because the
Database2A user SIDs are different from the
Database1A user SIDs.
After the cross-server copy process is complete, the logins, users, and permissions of
Database2A can be managed independently of
Database1A. Use the DBO login and the Transact-SQL ALTER USER statement to map users in the new database to logins on the new SQL Database server. For example:
ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER (Windows Azure SQL Database).
How to: Copy Your Databases (Windows Azure SQL Database)
How to: Import and Export a Database (Windows Azure SQL Database)
How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database)