SQL Server 2016 SP1 introduced several interesting enhancements that caught the attention of many database professionals. One of these killer features, which is also available in SQL Server 2014 SP2, is the ability to generate statistics and create a schema-only copy of a database. This feature can be of great value, especially when it comes to testing and troubleshooting performance problems related to the query optimizer.
So, how does this feature work? Let’s dive into the specifics:
When you execute the command to generate a schema-only copy of a database, SQL Server creates a new database using the same file layout as the source database. However, the size of the new database is based on the size of the model database. Essentially, a read-only database is created using an internal snapshot mechanism. Additionally, the schema for all objects, along with the statistics of all indexes, is copied from the source database to the new database.
After the database is created, you can easily identify it as a clone by checking its properties. Simply execute the following command:
SELECT DATABASEPROPERTYEX ('My_Sample_mybkp', 'IsClone')
GOIf the command returns a value of 1, it means the database is a clone. Any other value or NULL indicates that it is not a cloned database.
It’s important to note that a cloned database should only be used for diagnostic purposes and is not supported for use in a production environment.
Now, let’s take a look at an example to better understand the concept. In the original database, the data files are close to 600MB. However, when we create a schema-only copy of the database, the size of the cloned database is significantly smaller, only 16MB. This is because we have copied only the schema and statistics, not the actual data.
As you can see, this feature can be incredibly useful for testing and troubleshooting scenarios. It allows you to quickly create a replica of a database without the overhead of copying the entire dataset. This can save time and resources, especially when dealing with large databases.
So, will you be using this feature in your environment? What is your use case for it? I would love to hear your thoughts and experiences in the comments below.