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

February 7, 2013 — 3 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

2013-02-08_02h35_58

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

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

  1. 

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

    Like

  2. 

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

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s