Exporteren (0) Afdrukken
Alles uitvouwen
EN
Deze inhoud is niet beschikbaar in uw taal, maar wel in het Engels.
48 van 89 hebben dit beoordeeld als nuttig - Dit onderwerp beoordelen

How to: Copy Your Databases (Windows Azure SQL Database)

Updated: November 27, 2013

This topic describes how to copy a Windows Azure SQL Database database to a new database on the same or different SQL Database server. The new database is a regular SQL Database database: fully functioning and independent of the source database. For more information, see Copying Databases in Windows Azure SQL Database.

You can back up a SQL Database database by copying it. Copying your database to a different server protects the database against unwanted deletions or modifications. From a database backup perspective, copying a database in SQL Database on a recurring schedule is similar to taking full backups of an on-premises SQL Server database. On each recurrence, after the copy completes successfully, you can drop the database that copied from the previous recurrence. For more information on other backup and restore options, and business continuity features, see Windows Azure SQL Database Backup and Restore and Business Continuity in Windows Azure SQL Database.

In This Topic

Before You Begin

Restrictions

  • A SQL Database subregion might consist of multiple physical clusters. Currently, however, you cannot copy a database between two different clusters. Error message:  If you try to copy a database between two different clusters, the copy operation returns the following error:

    Msg 40532 - Cannot open server “<server name>" requested by the login. The login failed.

    To determine whether copying a database 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, the database copy is possible. For more information, see Using the Ping Command.

    Workaround procedure:  If copying the database is not possible, do the following:

    1. Create two new logical servers in the same subregion (the servers should receive the same IP address).

    2. Perform an offline database migration to move the database to one of these servers. You can use various tools, such as the Import/Export Service. For more information, see SQL Database Import/Export Service or How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database).

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

Timing of Copy Operations

The database copy workload affects the performance of the SQL Database server or servers involved in the copying process. Therefore, the copying process can require an extended time to complete. If the database copying fails, restart the copying process when the demand on your source database is low. You might need to suspend other workloads on the source database until the copying process completes.

Permissions

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

  • On both servers: The login must have the same login name and password on both SQL Database servers.

  • On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your SQL Database server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in SQL Database, see Managing Databases and Logins in Windows Azure SQL Database.

  • 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 the database to another database.

When these permission requirements are met, your login can execute the SQL Database ALTER DATABASE and DROP DATABASE statements against the new database.

noteNote
Changing the database owner by using the SQL Database ALTER AUTHORIZATION ON DATABASE statement is not supported by SQL Database. Like SQL Server, you can create additional users inside the database in SQL Database 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.

Arrow icon used with Back to Top link [Top]

Copy to the Same Server (Transact-SQL)

To copy your database to a new database on 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 in order to copy databases. For more information about logins and connecting to the server, see Managing Databases and Logins in Windows Azure SQL Database and Development: How-to Topics (Windows Azure SQL Database), respectively.

  • Start copying the source database with the SQL Database 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.

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

  • (Optional) 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.

Example (Transact-SQL)

The following code example initiates the database copying process. The example copies database Database1A to a new database named Database1B on the same SQL Database server.

-- Execute on the master database.
-- Start copying.
CREATE DATABASE Database1B AS COPY OF Database1A;

The following code examples can be used to monitor the database copying process by querying the sys.databases and sys.dm_database_copies views for information about the new database named Database1B.

-- Execute on the master database.
-- Retrieve state of the new database, Database1B.
SELECT name, state, state_desc FROM sys.databases WHERE name = 'Database1B'

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve copying details.
SELECT * FROM sys.dm_database_copies copies
Inner JOIN sys.databases databases ON copies.database_id = databases.database_id
      WHERE databases.name = 'Database1B';

Arrow icon used with Back to Top link [Top]

Copy to a Different Server (Transact-SQL)

To copy your database to a new database on a different server

  • Log on 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 (DBO) 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. For more information about logins and connecting to the server, see Managing Databases and Logins in Windows Azure SQL Database and Development: How-to Topics (Windows Azure SQL Database), respectively.

  • Start copying the source database with the SQL Database CREATE DATABASE statement. Executing this statement on the destination server initiates the database copying process. Because copying a database is an asynchronous process, the CREATE DATABASE statement returns before the database completes copying.

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

  • (Optional) 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.

  • (Optional) 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. 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.

Example (Transact-SQL)

The following code example initiates the database copying process from Server2. The example demonstrates copying database Database1A from the SQL Database server named Server1 to a new database named Database2A on Server2.

-- Execute on the master database of Server2
-- Start copying from Server1 to Server2
CREATE DATABASE Database2A AS COPY OF Server1.Database1A;

The following code example monitors the database copying process. The example queries the sys.databases and sys.dm_database_copies views on Server2 for information about the new database named Database2A.

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve state of the new database, Database1B.
SELECT name, state, state_desc FROM sys.databases WHERE name = 'Database1B'

ImportantImportant
The statement below returns a result set only while the copying is in progress. Once the copy is complete and the new database is online, this query will no longer return a result.

-- Execute on the master database.
-- Retrieve copying details.
SELECT * FROM sys.dm_database_copies copies
Inner JOIN sys.databases databases ON copies.database_id = databases.database_id
      WHERE databases.name = 'Database1B';

Arrow icon used with Back to Top link [Top]

Stop the Copying process (Transact-SQL)

If you want to stop the copying process before it completes, you can use the DROP DATABASE statement to drop the destination databases. Dropping the source database also causes the copying 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, earlier in this topic.

Arrow icon used with Back to Top link [Top]

Follow Up: After Copy Completes

  • When the database copying completes, the new database enters one of two states, as follows:

    • If the copying process succeeded, the new database enters the ONLINE state. After the new database is online, it can be managed and used independent of the source database.

    • If the copying process failed, the new database enters the SUSPECT state. The suspect database is unavailable. It cannot be accessed or recovered. In this case, drop the destination database, and try copying the source database again.

    To view the state, query the state_desc column of the sys.databases view.

  • When the copy is complete, the new database has the same edition and maximum size as the source database. For information about how to set and change the edition or maximum size in SQL Database, see Accounts and Billing in Windows Azure SQL Database.

  • After a cross-server copy is complete, the logins, users, and permissions of the copy can be managed independently of the source database. Use the DBO login and the 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).

Arrow icon used with Back to Top link [Top]

See Also

Vindt u dit nuttig?
(1500 tekens resterend)
Bedankt voor uw feedback

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft. Alle rechten voorbehouden.