Using DBCC Clonedatabase

I haven’t messed with the new DBCC CloneDatabase option in SQL Server 2014/12016 (depending on patches), but recently I saw this:

2017-04-28 17_39_32-Argenis Fernandez on Twitter_ _Anyone out there using TFS that can send me a bac

On a quiet Friday afternoon, this seemed like a good time to experiment. We (Redgate Software) have a salesdemo VM that we use to show various Redgate products to customers and clients. On the VM, we have a TFS install that shows how we plug into that platform.

We have a slick demo system on EC2 where I can fire up a self-service VM for use anytime. Since DBCC CloneDatabase needs recent SQL Server versions, I had to use that. My export of the demo VM from late last year is SQL 2012 Sad smile.

Once the system was up, I found the SQL instance that hosted the TFS databases and connected with SSMS. Then what?

I ran a quick search and found the MS support article: How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1

This explains that the basic syntax is

DBCC CLONEDATABASE (source, target)

The source is the database to clone, and the target is the new database you want created. Using that, I quickly ran this twice, once for the tfs_config database and once for the tfs_defaultcollection db. I used create names for the targets.

DBCC CLONEDATABASE (tfs_config, argenistfs_config)

DBCC CLONEDATABASE (tfs_defaultcollection, argenistfs_defaultconfig)

With these database, I ran backups and uploaded these to a share for Argenis to use. The clones contain schema and stats, but no data, so they’re small. The live databases are a few GB, but the clones are small.

2017-04-28 17_49_58-Public

I haven’t used the cloned databases for anything, but there are articles out there that will help you to use this to work on issues in your main system. SQL Performance has one from Erin Stellato and so does Brent Ozar.

This is going to be one of those tools that will be helpful for DBAs in the future, so play around with it.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to Using DBCC Clonedatabase

  1. Brendan Mason says:

    It’s worth noting that if you include statistics in the clone, it does technically contain some data since the statistics themselves will have data in the histograms. Just something to consider if the database contains sensitive data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s