Backups are performed for a number of reasons including: security against hardware failure and protection against user error. Windows Azure SQL Database provides good options for handling both these scenarios. This white paper will cover the current backup operations for SQL Database.

For the latest information on SQL Database backup and restore strategy, see Windows Azure SQL Database Backup and Restore article in the Windows Azure SQL Database MSDN library.

Backing Up For Hardware Failure

SQL Database 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 Database 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.

Getting Started

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 Database 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:

SELECT *

FROM sys.dm_database_copies

Here is my output from the Adventures Works copy above:


Permissions Required

When you copy a database to a different SQL Database 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 Windows Azure SQL Database.

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.

Summary

More information about copying can be found in the MSDN article: Copying Databases in Windows Azure SQL Database.

 Other Languages

 This article is also available in other languages, including Russian.

         Wiki: Доступные в данный момент возможности по резервированию данных в Windows Azure SQL Database (ru-RU)