DBCC CLONEDATABASE is one of those features that I don’t use often but it’s really cool when I need it. I was recently asked to create schema only copies of ~40 databases. This DBCC command was originally created to, and I quote, “investigate performance issues related to the query optimizer“. How would a schema only copy of a database help with that? Well, it also copies the statistics and query store information (if any). That way when the system creates query plans they are created using same information as the original database.
I’ll be honest, I’ve never used it for that though. My favorite use is to create that schema only database. In this case they wanted a set of staging databases, but I’ve also used it to create/re-create a testing environment with no data.
But Ken, why don’t you just script out the database and run the script in a newly created database? Well, I certainly could, and in some cases I’ve had to. There are a couple of down sides to this though. It’s a lot more labor intensive. Creating the script (usually through the GUI), creating the database, and then running it can be time consuming. And I have to tell you, in some of my older databases, that script won’t run cleanly. For example, older stored procedures (that obviously haven’t been tested recently) return an error because the table structures they reference have changed and they are referencing a column that isn’t there anymore.
At that point, unless I want to create the required columns, create the stored procedures, and then drop the columns (and then probably carefully compare everything to make sure I didn’t miss anything) I only have one other choice. I can restore the database, truncate all of the tables, and then shrink it to the size I want. Extremely time consuming if it’s a larger database and a pain in the neck regardless.
Which brings us back to DBCC CLONEDATABASE. (FYI DBATools’ Invoke-DbaDbClone is basically a PowerShell interface to DBCC CLONEDATABASE). This is incredibly easy to use, and fairly quick too.
DBCC CLONEDATABASE (SourceDBName, NewDBName) WITH VERIFY_CLONEDB;
The documentation says you need to use VERIFY_CLONEDB clause if you plan to use this in production. I’m guessing in production means anything permanent such as a test database or even dev database. It also tells the command to exclude statistics and query store information.
Now, as much as I love this command, it doesn’t always work. If your model database has any user objects in it then it’s going to fail. Also if the source database is old enough you might get some failures as well. This is because (as far as I know) DBCC CLONEDATABASE operates by copying the model database (that’s how new databases are created in general) and then behind the scenes copying over all of the object information by copying the data in the system tables. Of course those system tables have primary keys and if there are duplicates (i.e. a object in model has the same object_id as one of the new objects) then you are going to get a primary key error.
Of course this only works if the source and destination are on the same instance, so if I need them somewhere else I’m still going to have to copy the database (either by detaching and moving, or backup and restore) but that’s still a lot faster with a database with no data in it.