‘DBCC CLONEDATABASE’ in SQL Server 2014 SP2


In SQL Server 2014 SP2 an interesting new DBCC command was included, DBCC CLONEDATABASE

This command creates a new database (“clone”) that contains the schema of all the objects and statistics from the specified source database (not supported for the system databases).

'DBCC CLONEDATABASE' in SQL Server 2014 SP2

About DBCC CLONEDATABASE
You can generate a clone of a database by using DBCC CLONEDATABASE in order to investigate a performance issue related to the query optimizer.

Note: The newly generated database generated from DBCC CLONEDATABASE isn’t supported to be used as a production database and is primarily intended for troubleshooting and diagnostic purposes. We recommend detaching the cloned database after the database is created.

When to use DBCC CLONEDATABASE?
DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues.

The cloning of the source database is performed by the following operations:
• Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
• Creates an internal snapshot of the source database.
• Copies the system metadata from the source to the destination database.
• Copies all schema for all objects from the source to the destination database.
• Copies statistics for all indexes from the source to the destination database.

Syntax

DBCC CLONEDATABASE (source_database_name, target_database_name)

• source_database_name
This argument is the name of the database whose schema and statistics need to be copied.
• target_database_name
This argument is the name of the database which the schema and statistics from the source database will be copied to. This database will be created by DBCC CLONEDATABASE and should not already exist.

Example – Creating a clone of the AdventureWorks database
The following example executes DBCC CLONEDATABSE for the AdventureWorks database.

Transact-SQL

-- Generate the clone of Test database.
DBCC CLONEDATABASE (Test, testclone);
GO

'DBCC CLONEDATABASE' in SQL Server 2014 SP2

Note: All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.