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?
DBA From The Cold 