DBCC CloneDatabase

The Service Pack 2 release of SQL Server 2014 introduces a new DBCC command called DBCC CloneDatabase.

Books online states:

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

Which sounds really interesting. Get all the schema and statistics but no data.

Some immediate uses come to mind:

• Using in a development or test environment to use production statistics to view query plans.
• When dealing with Database Corruption, a way to spin up an empty copy of a database for troubleshooting work.
• A great way to archive the schema without the data.
• Spinning up multiple developer copies of a database on a test server.

To clone the database QueryTraining to a new database called AttackOfClones.

DBCC CloneDatabase (QueryTraining, AttackOfClones)

Here is the output when you run CloneDatabase.

Database cloning for 'QueryTraining' has started with target as 'AttackOfClones'.
Database cloning for 'QueryTraining' has finished. Cloned database is 'AttackOfClones'.
Database 'AttackOfClones' 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.

Note the bit about not supported for use in a production environment.

Notice that after running DBCC CloneDatabase, and refreshing the object explorer that the cloned database is read only.

If you want to set the database read write, and no longer be read only, you can do the following.

ALTER DATABASE [AttackOfClones] SET  READ_WRITE WITH NO_WAIT;

Here is a list of tables in the cloned database compared to the original database.

I still have some investigation to do here, but this looks interesting.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!