I love the follow-up questions as they demonstrate that my blog posts are read and help everything think of their own environment. Here is the follow-up question I have received to my earlier blog post about Clone Database Using DBCC CLONEDATABASE. This time the question is about how to Copy Database Without Statistics and Query Store.
The original question about using DBCC CLONEDATABASE had come up during the consulting engagement of the Comprehensive Database Performance Health Check. The follow-up question actually came up by a reader who was actually using the older methods to script out the entire database as mentioned here.
The question from the user was that when we Clone the database with the DBCC CLONEDATABASE at that time the database is also coping the statistics and query store data. Is there any way they can only copy the schema and no other information. Honestly, I see no reason to do this task as the database which you generate is going be READ ONLY without any meaningful statistics associated with it.
Here is the script which you can use to create a copy of the database Without Statistics and Query Store.
DBCC CLONEDATABASE (WideWorldImporters,CloneWideWorldImporters) WITH NO_STATISTICS, NO_QUERYSTORE; GO
Please note that the database which will be created with the above method will be READ-ONLY and will not contain any statistics or query store data.
When you run the query against this database the execution plan will also be different from the original database and also will complain about no proper statistics.
Now if you want to use this database to store new data or for anything else, there is a very simple trick. You can always make the database Read-Write by running the following script.
USE [master] GO ALTER DATABASE [CloneWideWorldImporters] SET READ_WRITE WITH NO_WAIT GO
Once you run the above script the database will be now available to re-populate as well as do the further modification. I find this trick very helpful. You can also do the same with the help of SQL Server Management Studio and here is the image of the database property.
I find this trick very useful when I am involved in a scenario where I can’t access the original database and I have to tune my T-SQL Scripts. You can read more about this feature here.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
