DBCC CLONEDATABASE in SQL Server 2014

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

dbcc clonedatabase

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

dbcc clonedatabase stats
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?

SQL Server 2016 – Perform Volume Maintenance

One of the server settings that I always enable when configuring a new instance of SQL is database instant file initialisation

In case you don’t know what this is, it is a setting that can be granted to the service account that the SQL database engine runs under that prevents SQL Server from “zeroing out” new space when a data file is created or expanded.

This improves the performance of CREATE/ALTER DATABASE statements, RESTORE statements and AUTOGROWTH operations. A full detailed article to how and why can be found here.

It’s cool to see that you can now enable this when installing SQL Server: –

SQL2016 Perform Volume Maintenance

I can’t think of any downside to having this enabled (off the top of my head, there’s probably one or two) and it’s good to see that Microsoft know that most people enable it so adding it as an option in the installer is great imho.