I have been writing about how some of the interesting enhancements made with SQL Server 2016 SP1 have caught my attention. This one is a killer feature that can be of great value when you are trying to do testing. Having said that, I also want to call out that this feature has been added with SQL Server 2014 SP2 too. When you get into performance problems related to the query optimizer, then this capability will be of great advantage. In this blog post we will learn about Generate Statistics and Schema Only Copy of the Database.
To get into the specifics on how the command would look like:
The text is shown below for reference:
Database cloning for ‘AS_Sample’ has started with target as ‘My_Sample_mybkp’.
Database cloning for ‘AS_Sample’ has finished. Cloned database is ‘My_Sample_mybkp’.
Database ‘My_Sample_mybkp’ is a cloned database.
A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Once the command is run, this creates a new database using the same file layout, but using the size of the model database. Fundamentally a read-only database is created using an internal snapshot mechanism. In addition to it, the schema for all objects are created as copies along with the statistics of all indexes from the source database.
The object explorer after the database is created would look like:
To illustrate the actual database size is different from the original database, you can see from the below image. Here the original database data files are close to 600MB while the cloned database is just 16MB.
If you select the table’s data, you will see there is no data in these tables on the Cloned database. This is fundamentally because we have copied just the schema and statistics – not the data.
To check if a selected database is a clone or not, use the following database properties:
SELECT DATABASEPROPERTYEX ('My_Sample_mybkp', 'IsClone')
GOIf the command returns a 1, it means the database is a clone. Any other value of NULL means this is not a cloned database.
As I wrap up this blog, I would like to understand if you will be using this feature in your environment? What is your use case for the same? Do let me know via comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Hello Pinal,
Fabulous post, as always !
It could be very helpful during performance tuning exercise when Production data is either too critical to be shared or matching resources to proceed with Prod DB restore are not available. It is simpler than generating database scripts with few custom configurations including setting ‘script statistics’ option to script statistics and histogram. Not 100% sure if outcome is exactly same for both approaches.
Appreciate your great contribution to SQL community.
Br,
Anil
That’s a great point @Anil.