{{'2016-11-09T08:37:00.0000000' | utcToLocalDate }}
Richie Lee

Using CloneDatabase

One of the most interesting features of SQL Server 2014 Service Pack 2 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. So I ran this on a very small (50mb) database, and within a few seconds it was completed. I then ran this on AdventureWorks2014, which is 250mb, and the time to clone was roughly the same. Your mileage may vary.

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:


So 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:



comments powered by Disqus