Using CloneDatabase

One of the most interesting features of SQL Server 2014 Service Pack is the new management command DBCC CLONEDATABASE. The idea of it is to create an “empty” copy of the database; all the metadata and statistics of the original and clone are identical, but the clone contains no data.

The syntax of the command is very straightforward.

DBCC CLONEDATABASE (source_database_name, target_database_name)

So I ran this on a very small (50mb) database, and within a few seconds it was completed. Ithen ran this on AdventureWorks2014, which is 250mb, and the time to clone was roughly the same. Your mileage may vary.

DBCC CLONEDATABASE ('AdventureWorks2014', 'CloneAdventureWorks2014')

The console window provides some information on what is happening while the command is running:

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

Noticeably however, when the command is complete, the cloned database is in read-only mode:

CLone1So this is interesting, and it’s important to note that the main reason that this new command has been made available is to assist MIcrosoft in troubleshooting databases for customers. But there are other uses; most notably unit testing/testing query plans etc. However it is easy enough to alter to read/write:



Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s