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
Alexandre Brisebois ☁ 
That’s great! I often find myself in need of replicating production database to debug live data issues.
LikeLike
did you know if is work accros different server on different resource groups ?
LikeLike
I don’t know, this was written a while ago ad may be worth a test or two. Let us know what you find out ;)
LikeLike
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…
http://stackoverflow.com/questions/43609507/the-databases-operational-in-server-sql01-and-staging-in-server-sql01-ar
LikeLike
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.
LikeLike