top of page

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:

DBCC CLONEDATABASE

See you soon!

RECENT POST
bottom of page