How to create a database copy in SQL Azure for backup

You can back up a SQL Azure 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.

Here's the syntax.

CREATEDATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Azure server is not needed for the full duration of the copy process. The CREATE DATABASE statement will return control to the user before the database copy operation is complete. In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress. You can monitor the copy process with the sys.dm_database_copies and sys.databases views. After the copy process completes successfully, the destination database is transactionally consistent with the source database

Here's the code to monitor the copy process.

SELECT* FROM SYS.DM_DATABASE_COPIES
SELECT* FROM SYS.DATABASES WHERE NAME ='DESTINATION_DATABASE_NAME'


Read the complete MSDN article here:
http://msdn.microsoft.com/en-us/library/ff951624.aspx

0 comments:

Post a Comment

Please let me know if you find it useful!