Creating a Staging Database Instance From a Production Instance on Windows Azure SQL Database

February 7, 2013 — 5 Comments

Debugging and  testing  against a live production database isn’t the greatest idea. At this point in the application’s lifecycle it comes in handy to be able to duplicate the production database to create a temporary staging environment.

To create a copy of your production database, log into the Master database and use the following statement:

CREATE DATABASE [NewDatabaseName] 
    AS COPY OF ServerName.[SourceDatabase]
  • Replace NewDatabaseName by the name of the new Database
  • Replace ServerName by the database server name of the source Database
  • Replace SourceDatabase by the name of the source Database

The CREATE statement will immediately return as completed. However, it doesn’t actually perform copy process, it creates a job that executes asynchronously. The following query can be used to monitor it’s progress.

SELECT * FROM sys.dm_database_copies


Keep in mind that the duplication process will probably not start immediately, give it a few minutes and you should start seeing some progress. Furthermore, depending on the size of Database, the duplication process may take a fair amount of time to complete.

SQL Database aren’t cheap! Be sure to copy what you need and delete it once you’re done. SQL Database fees can  be quite surprising when the end of the month comes around.

Note: This technique should not be considered as a viable backup solution

5 responses to Creating a Staging Database Instance From a Production Instance on Windows Azure SQL Database


    That’s great! I often find myself in need of replicating production database to debug live data issues.



    did you know if is work accros different server on different resource groups ?



    I’ve been banging my head against a wall with this all day. This is great in theory but in practice it just doesn’t work. I can’t get a reliable release to drop the database all the time and then create it. I’m also having very weird failures coming through about relation…



    To be honest, given how long ago this blog post was written, I was sceptical if this’d still work.
    I just used this to clone a production database into a test environment, and it worked flawlessly. Thanks for the tip.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.