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!