SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.


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

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...