Current Options for Backing up Data with SQL Azure
Backups are performed for a number of reasons including: security against hardware failure and protection against user error. SQL Azure provides good options for handling both these scenarios. This white paper will cover the current backup operations for SQL Azure.
Backing Up For Hardware Failure
SQL Azure maintains multiple instances of your data at any one time, on different independent hardware in the data center. We call these instances replicas. At any one time, we are keeping three replicas of data running – one primary replica and two secondary replicas. We use a quorum based commit scheme where data is written to the primary and one secondary replica before we consider the transaction committed. At the same time we are perform periodic offsite backups of the data in case of a catastrophic failure at the data center. These mechanisms protect against hardware failure and are in essence a backup for your data in case of hardware failure.
Backing Up Against User Error
The second type of backup that is needed is one to protect against user error. The type of errors that we are concerned about are ones in which the user modified the data or the schema and there is a need to roll back to a previous version of the database. To protect against these errors, users can create a copy of their database and store it within the datacenter. This copy can be put online in place of the database at any time, or copied back to the database to restore the backup.
The backup is performed in the SQL Azure datacenter using a transactional mechanism without downtime to the source database. The database is copied in full to a new database in the same datacenter. You can choose to copy to a different server (in the same data center) or the same server with a different database name.
A new database created from the copy process is transactionally consistent with the source database at the point in time when the copy completes. This means that the snapshot time is the end time of the copy, not the start time of the copy.
The Transact SQL looks like this:
CREATE DATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name
To copy the Adventure Works database to the same server, I execute this:
CREATE DATABASE [AdvetureWorksBackup] AS COPY OF [AdventureWorksLTAZ2008R2]
This command must be execute when connected to the master database of the destination SQL Azure server.
Monitoring the Copy
You can monitor the currently copying database by querying a new dynamic managed view called sys.dm_database_copies.
An example query looks like this:
Here is my output from the Adventures Works copy above:
When you copy a database to a different SQL Azure server, the exact same login/password executing the command must exist on the source server and destination server. The login must have db_owner permissions on the source server and dbmanager on the destination server. More about permissions can be found in the MSDN article: Copying Databases in SQL Azure.
One thing to note is that the server you copy your database to does not have to be in the same service account. In fact you can give or transfer your database to a third party by using this database copy command. As long the user transferring the database has the correct permissions on the destination server and the login/password match you can transfer the database.
More information about copying can be found in the MSDN article: Copying Databases in SQL Azure.