SQL Server 2012 SP4 Released – Clone Database Added

Hello!

One of the most interesting features of SQL Server 2012 Service Pack 4 is the availability of the 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 with only 4 arguments required. 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:

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:

 

Leave a Reply

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