Copying databases in SQL azure

Link: http://msdn.microsoft.com/en-us/library/windowsazure/ff951624.aspx

Microsoft SQL azure database is the relational database service on the Windows azure platform. once you have created a database in SQL Azure, you can back up that database by copying it to a new database in SQL azure. the new database that is created from the copy process is a fully functioning database, independent of the source database once the copy is complete. the new database will have the same edition and maximum size as the source database.

Overview

The database copy feature enables attributes of the back-up scenarios you may be familiar with for on-premises databases:

• Application Data back up: Copy the application database to a new database in order to help protect it from user and application errors. recover the application database state by renaming the new database with the application database name. alternatively, recover a specific subset of data from the new database by use of 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: Copy the application database to a new database before major application updates. If a mistake is made during the upgrade, 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 then perform a data-tier Application (DAC) export to create an export file that has both the definitions of the objects in the database and the data from the user tables. this export file can then be imported to another SQL azure server, or to an instance of the SQL Server database engine. for more information, see How to: export a data-tier Application (SQL azure ).

Note

Databases created by the copy feature count toward the SQL azure database limit of 150 databases for each SQL azure server.

Copying a database

Databases are copied asynchronously, so a connection to the SQL azure server is not needed for the full duration of the process. You can copy a database by logging intoMasterDatabase of the destination server and executing the transact-SQLCreate DatabaseStatement withAs copyClause. Then, you can monitor the copy process by usingSYS. dm_database_copiesAndSYS. DatabasesViews on the destination server.

Same-server copying

When you copy a database to make a new database on the same SQL azure 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 extends strates same-server copying:

In this figure,Database1aIs copied to a new database,Database1b, On the same SQL azure server,Server1. The login that copied the database becomes the DBODatabase1b. All database users, their permissions, and their Security Identifiers (SIDS) fromDatabase1aAre copiedDatabase1b. Because the user SIDS are the same on both databases, the logins fromServer1Maintain the same permissions on both databases.

After the copy is complete,Database1bBecomes a fully functional, independent database. The logins, users, and permissionsDatabase1bCan be managed independentlyDatabase1a.

Cross-server copying

You can also copy a database between two different SQL azure servers that are in the same sub-region or data center. because the new database is created on a different SQL azure server, it is associated with a differentMasterDatabase. 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 extends strates cross-server copying:

In this figure,Database1aIs copied fromServer1To a new database,Database2a, On a different SQL azure server,Server2. The login that copied the database becomes the DBODatabase2a. All database users and their permissions (but not their SIDS) fromDatabase1aAre copiedDatabase2a. The logins fromServer1Cannot be used with the new database because they are associated with a different SQL azure server and becauseDatabase2aUser SIDS are different fromDatabase1aUser SIDS.

Note

A SQL azure sub-region may consist of multiple physical clusters. Currently, you cannot copy a database between two different clusters-the copy will return the error,MSG 40532-cannot open server "<Server Name>" requested by the login. The Login Failed.To determine if a database copy is possible, usePingCommand ("Ping<Server>") On both the source and destination servers to resolve their IP addresses. If the IP addresses are equal then the database copy is possible.

If a database copy is not possible, then do the following:

1. Create two new logical servers in the same sub-region (the servers shoshould receive the same IP address ).
2. Perform an offline database migration to move the database to one of these servers. you can use varous tools to do this, such as the import/export service. for example, see SQL azure import/export service or how to: migrate a database by using the generate scripts wizard (SQL azure database) for more information.

Now you can perform a database copy between these two servers.

After the copy is complete, the logins, users, and permissionsDatabase2aCan be managed independentlyDatabase1a.

NoteAfter the cross-server copy process is complete, use the DBO login andAlter userStatement to map users in the new database to logins on the new SQL azure server. For example:Alter User Username with login = 'loginname'. For more information, see alter user (SQL azure database ).

Permissions

In order to copy a database in SQL Azure, your login requires the following permissions:

• On both servers: The login must have the same login name and password on both SQL azure servers.
• On the destination Server: The login must be a member of the server-levelDbmanagerRole. Note: The server-level principal of your SQL azure server is not a member ofDbmanagerRole, but automatically has the same permissions. For more information about managing logins in SQL Azure, see managing databases and logins in SQL azure.
• On the Source Server: The login must be the DBO of the source database. only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.

When these permission requirements are met, your login can executeAlter DatabaseAndDROP DATABASEStatements against the new database.

Note

Changing the database owner by usingAlter authorization on DatabaseStatement is not supported by SQL azure. Like SQL Server, you can create additional users inside the database in SQL azure and add them toDb_ownerDatabase-level role. These additional users can be linked to a login other than the login that created the database initially. However, these additional users cannot perform a database copy.

Timing

The database copy workload will impact the performance of the SQL azure server (s) involved in the copy process, and may take an extended period of time to complete.

Note

If the database copying fails, restart the copy process when the demand on your source database is low and suspend other workloads on the source database until the copy process completes.

Starting the copy process

Start copying the source database with the SQL azure database CREATE DATABASE statement. You must be connected toMasterDatabase with a login that meets the permission requirements as described in permissions. To initiate cross-server copying, you must be connected toMasterDatabase of the SQL azure server where the new database will be created: the destination server.

 
Create Database destination_database_name as copy of [source_server_name.] source_database_name
 

Executing this statement initiates the database copy process. IfSource_server_nameIs not provided, the source database must be located on the same SQL azure server that your login is connected to. Because this is an asynchronous process,Create DatabaseStatement will return before the database completes copying.

Monitoring a copy

After the database copy begins, you can querySYS. DatabasesAndSYS. dm_database_copiesViews onMasterDatabase of the destination server to retrieve more information about the copying SS. The following Transact-SQL example demonstrates how to obtain the copying progress for a new database namedDatabase1b.

 
-- Execute on the master database -- retrieve state of the new databaseselect name, state, state_desc from sys. databaseswhere name = 'database1b '-- retrieve copying detailsselect * From sys. dm_database_copieswhere database_id = db_id ('database1b ')
 

You pay for the databases you have, for the days you have them. if an error occurs during the copy, drop the target database immediately. for more information on the billing details, see accounts and billing in SQL azure. for a list of database copy errors, see database copy errors.

Transactional consistency

A new database created from the copy process is transactionally consistent with the source database at the point in time when the copy completes.

From a database backup perspective, copying a database in SQL Azure on a recurring schedule is similar to taking full backups of an On-premise SQL Server database. on each recurrence, after the copy completes successfully, you can drop the database that copied from the previous recurrence.

Stopping the copy process

If you want to stop the copy process before it completes, you can useDROP DATABASEStatement to drop the destination databases. dropping the source database will also cause the copy process to be canceled. in order for any of these options to work, use a login that meets the permission requirements as described in permissions.

After the copying completes

When the database copying completes, the new database will enter one of two States, as indicated byState_descColumn ofSYS. DatabasesView. If the copying was successful, the new database will enterOnlineState. After the new database isOnline, It can be managed and used independent of the source database.

If the copy is not successful, the new database will enterSuspectState. When it isSuspect, The new database is unavailable and cannot be accessed or recovered. In this case, drop the destination database and try copying the source database again.

Note

You specify the edition and maximum size of the database when you create it. once you have created a database in SQL Azure, you can back up that database by copying it to a new database in SQL azure. when the copy is complete, the new database will have the same edition and maximum size as the source database. for more information on how to set and change the edition or maximum size of the database in SQL Azure, see accounts and billing in SQL azure.

See alsotasks

How to: Back up your database (SQL azure)

How to: Copy your database to a different server (SQL azure)

Reference

Create Database (SQL azure database)

Concepts

Administration (SQL azure database)