Wednesday, August 25, 2010

Backing Up Your SQL Azure Database Using Database Copy

by Wayne Walter Berry via SQL Azure Team Blog on 8/25/2010 2:32:28 PM

With the release of Service Update 4 for SQL Azure you now have the ability to make a snapshot of your running database on SQL Azure. This allows you to quickly create a backup before you implement changes to your production database, or to create a test database that resembles your production database.

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.

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

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 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 need to belong to 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. I will show how to do this in a future blog post.

Why Copy to Another Server?

You will obtain the same resource allocation in the data center if you copy to the same server or a different server. Each server is just an endpoint – not a physical machine, see or blog post entitled: A Server Is Not a Machine for more details. So why copy to another server? There are two reasons:

• You want the new database to have a different admin account in the SQL Azure portal than the destination database. This would be desirable if you are copying the database to testing server from a production database, where the testers owned the testing server and could create and drop database as they desired.
• You want the new database to fall under a different service account for billing purposes.

Summary

More information about copying can be found in the MSDN article: Copying Databases in SQL Azure. Do you have questions, concerns, comments? Post them below and we will try to address them.