SQL SERVER – CLONEDATABASE: Generate Statistics and Schema Only Copy of the Database

CLONEDATABASE can Generate Statistics and schema-only diagnostic copies without copying table rows. I use that distinction when investigating optimizer behavior.

I noticed this feature while reviewing SQL Server 2016 enhancements. It also appeared in SQL Server 2014 SP2. The useful distinction is what travels with the copy: metadata and statistics, rather than the business data.

-- Diagnostic example only. Use a new destination name.
DBCC CLONEDATABASE (N'AS_Sample', N'My_Sample_mybkp');

SELECT DATABASEPROPERTYEX(N'My_Sample_mybkp', N'IsClone') AS is_clone,
       DATABASEPROPERTYEX(N'My_Sample_mybkp', N'Updateability') AS updateability;

The source must exist and the destination must not. Review the supported objects and options for your build before creating a clone. Use it for diagnosis, not as the starting point for a production database.

The original size comparison

My historical source data files were close to 600 MB. The clone was about 16 MB. Those sizes belonged to that instance. They aren’t expected sizes for every database.

Verify the clone

IsClone = 1 identifies a clone. A NULL result needs a check of the name, access and property applicability. The current lab uses a different database name from the historical sample.

The separately named AdventureWorks2025 test clone is online, read-only and verified. Use its name when checking the pictured properties. It isn’t the same database as the historical size comparison.

A diagnostic clone is not a data backup, it is a schema-and-statistics copy with a limited purpose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.