Thursday, November 17, 2016

DBCC CLONEDATABASE is now working with SQL Server 2016

You might have used this command before or you might be unaware of this command. This was introduced with SQL Server 2014 SP2 and it allowed as to clone a database easily without getting data but with statistics. This is very useful when you have to troubleshoot a production database but you cannot do much with it because;
  1. You should not use it for troubleshooting during peak hours. 
  2. You cannot do all testing as you want because of some policies and security implementations.
Since this command allows you to take a copy of database without taking data, it is fast and useful for checking various things.

Here is the way of creating a clone:

DBCC CLONEDATABASE(AdventureWorks2014, CloneAdventureWorks2014);

This is the result you get when you execute against SQL Server 2016 (Note that this requires SP1, this does not run in RTM)


This what you see if you execute in RTM


Since this does not have data, you cannot perform all activities performed for troubleshooting.

For example, this is possible and it produces the required result:

USE CloneAdventureWorks2014;
GO

DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'PK_SalesOrderHeader_SalesOrderID')  


But this is does not provide the output as we need:

USE CloneAdventureWorks2014;
GO

SET STATISTICS IO ON
SELECT * FROM Sales.SalesOrderHeader;


No comments: