DBCC CLONEDATABASE in SQL 2016 SP1
Service Pack 1 in SQL Server 2016 introduces new management command "DBCC CLONEDATABASE". This command will generate a schema and statistics in the new database, which is in a state with READ_ONLY. When we look in more detail what this command does:
∙ Creates a new destination database and internal snapshot of database.
∙ Copies all schema for object, system metadata and statistics for all indexes.
∙ Creates primary data file(.mdf) and log file(.ldf).
∙ Add secondary dataspaces and files.
This DBCC command is supported in Microsoft SQL Server 2014 Service Pack 2 and SQL Server 2016 SP1.
Syntax of DBCC CLONEDATABASE is following:
Arguments source_database_name, target_database_name are clear.
- NO_STATISTICS - If you type this parameter then in clone of database will not table/index statistics.
- NO_QUERYSTORE - If you type this Argument, QUERYSTORE will be excluded in clone database.(only SQL 2016 SP1)
Let's show some example:
Database cloning for 'Pigeon_DB' has started with target as 'Pigeon_DB_Clone'. Database cloning for 'Pigeon_DB' has finished. Cloned database is 'Pigeon_DB_Clone'. Database 'Pigeon_DB_Clone' 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.
In Management Studio The new database will look like this:
See you soon!