Geeks With Blogs
Scott Klein

On October 24th, Microsoft released Service Update 4 for SQL Azure. The major component of this release was the live release of Database Copy functionality, which allows you to make a real-time "backup", or shapshot of your database into a different database. Backup functionality has been at the top of feature requests for SQL Azure for a long time, so this feature is certainly welcome.

However, this great feature has some limitations. For example, there is no built-in way to automate a backup. For now it all manual via the CREATE DATABASE statement. Also, if you want to create multiple backups, you will need to create multiple new databases for each new copy. Keep in mind that there is a cost for each database. However, the Database Copy is a nice and convenient way to backup your database, and this blog post will walk you through the steps to automate the backup, or Copy, of a SQL Azure database using SQL Server Integration Services (SSIS) and a SQL job. This one of the topics covered in the book Herve and I are writing, Pro SQL Azure by Apress.

As a quick intro, creating a Database Copy is done via the CREATE DATABASE statement, as follows:

CREATE DATABASE targetdatabasename AS COPY OF sourcedatabasename

Simple as that. So, the first step in automating the Database Copy is to create a new SSIS project.

Once the project is created, place two Execute SQL Tasks on the Control flow, then link them.

Next, create a new connection to your SQL Azure master database. Be sure to test the connection to ensure you can properly connect to your SQL Azure database.

In the first Execute SQL Task, set the properties as shown in the following Figure. The Connection property should point to the connection you just created, and the SQL Statement property should simply be:


This database should be the target (Copy) database.

In the second Execute SQL Task, set the properties as shown in the following Figure. The Connection property should point to the same connection, and the SQL Statement property should simply be:

CREATE DATABASE targetdatabasename AS COPY OF sourcedatabasename

Your SSIS package is now complete. Test your package by running it manually. If all goes well, both tasks should be green when the package is finished executing.

Now, this example assumes that the target (Copy) database already exists when you first run this. If it doesn't, you will have to execute the command manually first via SQL Server Management Studio. Unfortunately you can't wrap the DROP DATABASE statement in an IF EXISTS statement because if you do you will get an error stating that the DROP DATABASE statement must be the only statement in the batch.

At this point, you can deploy your package to an on-premise SSIS Server. To automate the backup, create and schedule a SQL job that calls this SSIS package. For example, you can schedule the job to run nightly at 1:00 AM which will back up your database each night at 1:00 AM.


Posted on Thursday, September 9, 2010 7:22 PM SQLServerPedia Syndication | Back to top

Comments on this post: Automating a SQL Azure Database Copy

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © ScottKlein | Powered by: