This blog was due from a long time.  With the release of SQL Server 2014 Service pack 2, a new DBCC command included that is DBCC CloneDatabase. My primary job role is to do query tuning and I was looking for this kind of functionality. Using this command, it is quite easy   to copy the database schema and statistics. Which can be used for query performance testing without copying table data.

Let’s create a Clone of an existing database.

CloneDB

As the message says it is used only for diagnostic purpose and the created database shouldn’t be used as production database. It creates a read only copy of existing database.

CloneDB1

The cloning of database performs below operation.

  • First it creates a new database that uses the same file layout as the source database with default size as per model database.
  • Create an internal snapshot of source database
  • Copies system metadata, schema of all objects and statistics for all indexes of source database

As it creates an internal snapshot of source database, it will not cause any blocking while creating the clone database.

Now let’s run below query on both source and clone database.

If you look at the plan, both will have same execution plan except for the actual values.

clonedb3

You can verify a database is a clone database or not using database property IsClone.

This is very helpful when you wanted to troubleshoot any performance issue. Make sure not to run index rebuild or update statistics in clone database otherwise it will remove all statistics information.