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

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 .

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.

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.

Filed under: Blog Tagged: administration, sql server, syndicated