DBCC CloneDatabase

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.

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.

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.

USE AdventureWorks2012
Go
select p.FirstName,p.LastName,pe.EmailAddress, pp.PhoneNumber from person.person p 
INNER JOIN Person.EmailAddress pe ON p.BusinessEntityID = pe.BusinessEntityID
INNER JOIN person.PersonPhone pp On p.BusinessEntityID = pp.BusinessEntityID
WHERE pe.BusinessEntityID <=9
Go

USE AdventureWorks2012_Clone
Go
select p.FirstName,p.LastName,pe.EmailAddress, pp.PhoneNumber from person.person p 
INNER JOIN Person.EmailAddress pe ON p.BusinessEntityID = pe.BusinessEntityID
INNER JOIN person.PersonPhone pp On p.BusinessEntityID = pp.BusinessEntityID
WHERE pe.BusinessEntityID <= 9

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

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

SELECT DATABASEPROPERTYEX('AdventureWorks2012_Clone', '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.