In SQL Server 2014 SP2 an interesting new DBCC command was included, DBCC CLONEDATABASE
This command creates a “clone” of a specified user (not supported for the system databases) database that contains all objects and statistics of the specified database. Hmm, could be useful, but, how does it work? Let’s have a look.
First create a database: –
USE [master]; GO CREATE DATABASE [Test]; GO
And then create a test table: –
USE [TEST]; GO CREATE TABLE dbo.TestTable (PK_ID INT IDENTITY(1,1), ColA VARCHAR(10), ColB VARCHAR(10), ColC VARCHAR(10), CreatedDate DATE, CONSTRAINT [PK_ID] PRIMARY KEY (PK_ID)); GO
Insert some data and then make sure stats have been generated: –
INSERT INTO dbo.TestTable (ColA,ColB,ColC,CreatedDate) VALUES (REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE()); GO 100000 EXEC sp_updatestats; GO
Now we can run the DBCC CLONEDATABASE command: –
DBCC CLONEDATABASE ('test','testclone'); GO
And verify that a read only copy of the database has been generated: –
So, let’s have a look at the data in the new database: –
SELECT TOP 1000 [PK_ID] ,[ColA] ,[ColB] ,[ColC] ,[CreatedDate] FROM [testclone].[dbo].[TestTable]; GO
No data! Ok, so let’s have a look at the stats: –
USE [testclone]; GO EXEC sp_spaceused 'dbo.testtable'; GO DBCC SHOW_STATISTICS(N'testtable',PK_ID); GO
There’s the stats, SQL thinks that there’s 1000 rows in the table, pretty cool.
What we’ve ended up with is a read only database with no data but the objects and stats of the target database.
First thing, I’d be doing is backing that clone up and bringing it down to my local instance. Want to see how code will execute against production but don’t want to touch that prod environment? Here’s your answer.
@Microsoft, can we have this for other versions of SQL please?
3 thoughts on “DBCC CLONEDATABASE in SQL Server 2014”
This functionality has existed since SQL Server 2005 – see https://support.microsoft.com/en-us/kb/914288. That KB article reveals the supported and documented steps to generate a statistical clone. Those steps are not as convenient as the undocumented dbcc clonedatabase, but unlike undocumented commands, that KB’s functionality will not change without advanced public notice. In older versions of SQL Server it was also possible to sp_configure ‘allow’, 1 reconfigure with override, and hack (BCP) the statistics into another database, which is in part why the documented and supported method was published for 2005 and above.
How is a cloned database different than a database snapshot?
A database snapshot is a read only copy of the source database, which keeps the data consistent as to when the snapshot was taken. More info here: – https://msdn.microsoft.com/en-us/library/ms175158.aspx
DBCC CLONEDATABASE creates a copy of the database with no data, just the database objects and statistics.