Sunday, October 24, 2010

SQL Azure Backup Using Database Copy

Backup is currently a major gap in the SQL Azure product, but with support for Database Copy TSQL command has given at least one option for directly backing up a SQL Azure database by copying a source Azure database to a newly created  Azure database.
The TSQL command structure below provides will produce a copy of a database:
CREATE DATABASE  database_destination_name
AS COPY OF [source_server_name.]database_source_name

Note that this TSQL syntax allows for copying to a different Azure server. This is not necessary to protect the data from a disaster recovery point of view since the data is replicated across 3 geographic locations, but it might be required if you wish to copy the database to a different admin account or perhaps a different Azure account that would be billed separately to the source database account. If the database is copied to a new server the exact same login/password pair executing the command must exist on both the source server and destination server.
The copying process will not result in any downtime for the source database, and the copy will be a full copy complete with transactional data (note that this will be the transaction data as at the end time of the copying process not the start time of the copying process).
To monitor the copying process and determine if there were any copying errors you can query the sys.dm_database_copies view. The below command will retrieve all the data regarding the copying process:
Select * from sys.dm_database_copies
A major drawback to using Database Copy for backup is cost – each copy/backup will be charged as a new SQL Azure database.

No comments:

Post a Comment