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.

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.

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.

clonedb3

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.

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *