SQL Server

Getting developers production data stinks

DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but… Production data can be huge. Hundreds of gigabytes if you’re lucky, several terabytes if you’re not. Then once it gets there, you have other considerations. Either you lock down Dev, or you come up with a process to scramble data. This poses another set of problems because once data is scrambled, you lose some of the nuances of your actual data. Pattern matching scrambled data is useless and I hate it.

One alternative has been to script out a statistics only copy of your database. Which is cool, but kind of clunky. Microsoft has sought to ease that a bit with… yet another DBCC command.

That doesn’t solve any of those problems.

There’s gonna be a lot of HOOP-la!

SQL 2014 SP2 introduces DBCC CLONEDATABASE! Let’s get this straight. it’s not really meant to solve those problems. It’s meant to give you a minimal copy of your data to reproduce and diagnose performance issues with, without adding further load to your production server. But it suffers from the same problem that any statistics only copy of your database will. Let’s go through some of the issues.

Get your clone on

The syntax is really simple. Command. Database name. Clone database name.

This will produce a Read-Only ‘copy’ of your database on the same server as the database you cloned.

Here’s where the problems begin!

Production values are still visible

If you run DBCC SHOW_STATISTICS, or go through the GUI, you can see the steps in the histogram. For my StackClone database, the command looks about like this.

And there’s a bunch of names! Not all the names, but enough to be dangerous. I’m only showing the top 10 because a picture of the whole thing would be tacky and useless, like a condom in your wallet.

Hey, sailor.
Hey, sailor.

TIME and IO wait for no man

And they certainly don’t register anything with only statistics to reference.

On real data, we get back this:

On the clone, we get back this:

This immediately narrows the range of issues you can troubleshoot down. If you want to do any sort of meaningful performance tuning, you’re already short a couple of the best indicators that you’re on the right track.

Read-Only Means Read-Only

When your database springs to life, like what probably happened to Lazarus, it’s all grey and sticky.

Reading Is Fundamental
Reading Is Fundamental

Okay, maybe not Lazarus. Maybe that dude Tooms from X-Files that eats livers and takes wicked long naps. The down side here is that new queries won’t auto-create statistics. So if you run a query, and you don’t already have statistics for a column, you just get a warning.


I mean, yeah, you can make it Read-Write to get around this, but you should probably move it off to another server before doing this. I’m guessing you don’t want a database sitting in prod that’s virtually indistinguishable from your other databases.

Updating statistics and Rebuilding indexes ruins everything

The other downside of making it Read-Write, is that if a maintenance task comes along and checks the data, it wipes out your existing statistics. This is also true of index Rebuilds, which if I haven’t beaten it into you already, updates statistics with a full scan. And hey, forget about adding new indexes. SQL is hip to your empty table game. You’ll have to add them in Prod, and then re-clone your database, or script that single statistic out and add it to your database clone. Sound like fun? Yeah? You’re a real sick puppy.

Questions and answers

This is pretty handy for a few different scenarios. Just be aware of the limitations and risks. If you’re out there reading this, and you’ve got a good process in place for scrubbing production data, leave a comment. I’m sure lots of other people out there could use the advice.

Thanks for reading!

Brent says: DBCC CLONEDATABASE is just one of many cool things in SQL 2014 SP2. There’s new memory grant columns in sys.dm_exec_query_stats, tons more stuff in execution plans, and even faster performance on partitioned tables. The dev teams are pouring a lot of effort into backporting awesome improvements into 2014. I’ve never been so excited about Microsoft’s commitment to SQL Server.

Previous Post
New Sample Databases From Microsoft
Next Post
New Course: Statistics – SQL Server’s Guessing Game

25 Comments. Leave new

  • Very Useful Information.Nice article!

    • wait just a darned second…is it april 1st?

      my reaction on reading the title of this article. this is pretty cool, though. pretty dang cool.

      on the doc page it doesn’t list constraints–surely those come along for the ride? constraints being a child-object of tables/views?

  • Great feature, any idea if this will make its way to SQL 2016 as well (soon) ?
    Would be silly not to.

    • Erik Darling
      July 13, 2016 8:38 am

      I’d imagine so, but I don’t know for sure. I’d keep an eye on release notes for 2016 CUs.

      • Hi everyone,
        just for information: tried it on Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
        Developer Edition (64-bit) , got for
        DBCC CLONEDATABASE(Test, TestClone)

        Msg 2526, Level 16, State 3, Line 1
        Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

  • Ajay Dwivedi
    July 20, 2016 11:25 am

    Hi Eric,

    I am getting below error while executing CLONEDATABASE query on my local SO db. What could be the issue?

    Database cloning for ‘StackOverflow’ has started with target as ‘StackClone’.
    Msg 2601, Level 14, State 1, Line 1
    Cannot insert duplicate key row in object ‘sys.sysowners’ with unique index ‘nc1’. The duplicate key value is (Neon).

  • andrew sonoferik
    August 12, 2016 2:17 am

    . I’m only showing the top 10 because a picture of the whole thing would be tacky and useless, like a condom in your wallet.”
    +1 for that alone

  • Thought this was gonna be an awesome new tool.
    I really wanted to use this to refresh our QA environment. Remove all data refill from production.
    Got pretty far, just can’t reindex the indexed views.
    Save all the indexes
    Clone the DB
    Backup the cloned DB
    Restore the cloned DB over the original. (So that the data and log file names stay the same.)
    Resize the data and log files to what they were before.
    Rebuild any missing indexes. (This is where things came to a stop!)
    Drop the cloned DB
    Fill the new QA DB with selected data from production.

    Where’s that DacPac script of mine…..?

  • Nisarg Upadhyay
    September 7, 2016 6:37 am

    Hi Erik,
    Clone can not be used for database corruption detection.
    I have restored a copy of corrupt database (Page Corruption) and create clone of it. When run dbcc checkdb it came out clean.


  • Hi Brent,
    I am using clones for my developers to run pre-deployment schema change script testing. So no excuses, all code is tested before deployment to development databases. I also have a copy on QA environments for me to test the code push before I ruin QA (not that I do not trust my developer friends;)
    I forgot and left a clone on production and Mr. Hallengren’s maintenance plan failed dbcc with error 8992. I am guessing this would be normal for a clone as this error refers to meta data misalignment with objects and there is no data in a clone. Is this something anyone has run into during testing?

  • Btw Igor, you need to put the database names in quotes e.g.
    DBCC CLONEDATABASE (N’AdventureWorks2014′, N’AdventureWorks2014_CLONE’);

  • “If a snapshot cannot be created, DBCC CLONEDATABASE will fail. ”
    I think this will rule out databases with read-only filegroups – from what I know you cannot snapshot a database with read-only filegroups. I have seen this before because CheckDB takes a snapshot for schema stability – but if read-only filegroups exist it cannot take a snapshot and instead utilises table locks. If I get chance I’ll spin up a test machine and try it out but don’t expect that any time soon 🙂

  • Thanks for the Article.
    We have been using database cloning to refresh non-prod server (restore+clone+drop original DB) for some databases (logging DBs/sensitive info).
    We have seen one issue/bug. Cloned database somehow changes EXECUTE AS OWNER clause of SPs to “CALLER”. And the weird thing is SP definition shows EXECUTE AS OWNER but SP properties shows EXECUTE AS CALLER.
    I know it is very bad to have execute as owner (which now I cant do anything about).
    But this behavior is not documented any where. Do you have any insight on this?

  • It appears that the “Cannot insert a duplicate key row in object ‘sys.sysowners’ with unique index ‘nc1’. The duplicate key value is” issue is still happening with SQL 2017 Dev Edition. I have applied all CU’s and the issue still remains.

    • Kevin Shurtleff
      April 4, 2019 8:33 am

      I too am having that same error, SQL 2017 Enterprise Edition CU13.

    • Kevin Shurtleff
      April 4, 2019 8:41 am

      For me at least, I have SCOM monitoring, and the error was on the NT SERVICE\HealthService. This user has permissions to all databases for alerting purposes. I removed permissions for this user from the database and the clone worked successfully and without error. I then just added the permissions for SCOM back onto the database. If this is production and its a permissions issue with something more important than monitoring, it may be more difficult, otherwise just remove the user temporarily and then add it back after the clone to see if that works for you.

  • Do you know how to solve the wrong values that left from the source database at sys.partitions ?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.