DBCC CloneDatabase

Download PDF

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)

Clone1

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.

Clone2

 

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;

Clone3

 

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

clone4

 

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

Tagged with: ,
One comment on “DBCC CloneDatabase
  1. manu says:

    Thanks for sharing.

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.