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

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

About these ads