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.
The database copy feature enables many 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).
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 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.databases views on the destination server.
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 illustrates same-server copying:
In this figure,
Database1A is copied to a new database,
Database1B, on the same SQL Azure 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 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 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 Azure 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 Azure server and because the
Database2A user SIDs are different from the
Database1A user SIDs.
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, use a ping command ("
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:
- Create two new logical servers in the same sub-region (the servers should receive the same IP address).
- Perform an offline database migration to move the database to one of these servers. You can use various 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.
After the copy is complete, the logins, users, and permissions of
Database2A can be managed independently of
Note After the cross-server copy process is complete, use the DBO login and the
ALTER USER statement 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).
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-level
dbmanagerrole. Note: the server-level principal of your SQL Azure server is not a member of the
dbmanagerrole, 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 execute the
ALTER DATABASE and
DROP DATABASE statements against the new database.
Changing the database owner by using the
ALTER AUTHORIZATION ON DATABASE statement is not supported by SQL Azure. Like SQL Server, you can create additional users inside the database in SQL Azure and add them to the
db_owner database-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.
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.
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 to the master database with a login that meets the permission requirements as described in Permissions. To initiate cross-server copying, you must be connected to the master database 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. If
source_server_name is 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, the
CREATE DATABASE statement will return before the database completes copying.
Monitoring a Copy
After the database copy begins, you can query the
sys.dm_database_copies views on the master database of the destination server to retrieve more information about the copying progress. The following Transact-SQL example demonstrates how to obtain the copying progress for a new database named
-- execute on the master database -- retrieve state of the new database select name, state, state_desc from sys.databases where name = ’Database1B’ -- retrieve copying details select * from sys.dm_database_copies where 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.
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 use the
DROP DATABASE statement 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 by the
state_desc column of the
sys.databases view. If the copying was successful, the new database will enter the
Online state. After the new database is
Online, it can be managed and used independent of the source database.
If the copy is not successful, the new database will enter the
Suspect state. When it is
Suspect, 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.
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.
TasksHow to: Back Up Your Database (SQL Azure)
How to: Copy Your Database to a Different Server (SQL Azure)