T-SQL Tuesday #80 - I want DBCC CLONEDATABASE available on all (supported) versions of SQL Server
2016-11-17 update: according to
DBCC CLONEDATABASE is now also available in SQL Server 2016 (SP1).
I have chosen to blog about a cool command, which have been added to the SP2 (service pack 2) for SQL Server 2014:
This command enables you to take a copy of the schema and the statistics for an existing database into a new database. According to the documentation, DBCC CLONEDATABASE
Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
Creates an internal snapshot of the source database.
Copies the system metadata from the source to the destination database.
Copies all schema for all objects from the source to the destination database.
Copies statistics for all indexes from the source to the destination database.
Well, let’s have a look at the beast…
I installed a fresh SQL 2014 on my local Hyper-V environment (you might want to take a look at my post on setting up Hyper-V on your laptop, if need a little help setting everything up. See link below). Downloaded the service pack SP2 and installed it. And attached a demo database (in this case AdventureWorks2012DW).
Then I ran DBCC CLONEDATABASE:
My own read-only copy with all table definitions from the original database.
If we right-click on one of the tables, we can see that the statistics is populated
even though the table doesn’t contain any data:
On my test system (8 GB memory, 8 cores, SSD storage), cloning the demo database took less than 17 seconds:
So, why do I think that this a birthday gift? Well, right now I work on a big data warehouse project, where the client is using SSIS and Biml to auto-generate the staging layer (and more). The staging layer in a data warehouse is a landing zone for the data from source systems, and we typically want tables in here to be an exact copy of the source system objects schema-wise. The new DBCC CLONEDATABASE comes in very handy here, because the client operates with environments DEV, TEST, UAT and PROD, and source system databases will not have the same schema in these four environments. Sometimes the data warehouse team will not get notified about changes to schemas in PROD before they have been deployed, and ETL processes will either break, or data from new tables and/or columns will not be staged (and therefore not be moved up into the data warehouse layers). With DBCC CLONEDATABASE, we can ask the IT operations people to produce a copy of the source system schemas to lower environments in a supported, non-intrusive way, where internal auditing rules like “don’t copy production data to development environments” doesn’t get in the way.
AWESOME! And the size of the clone is a mere 7MB:
If you can live with just using datafile, then the size shrinks to around 4MB:
So with DBCC CLONEDATABASE, we could take daily copies of production schemas as part of a backup process, and keep them forever. Might even check them into TFS as well for easy use for developers.
That is a really useful features. Now, if we could have it on all supported versions of SQL Server, it would be even better. I created a connect item for this feature request (see link below.) If you agree with me, then vote it up.
That’s it for this months T-SQL Tuesday. Thanks for reading along…
Read more about T-SQL Tuesday #080 here:
Read more about DBCC CLONEDATABASE here:
Get SQL Server 2014 SP2 here:
Connect item for support on 2008R2, 2012 and 2016:
Read more about how you can use Hyper-V to set up your own local data center here:
Read more about Biml here: