The other day I ended up on a very interesting situation with my client while we were working with Comprehensive Database Performance Health Check. The client had a unique requirement that we can’t run any query even SELECT queries on the production system. Additionally, due to GDPR rules and HIPAA compliance, there was no way I can even see the data or work with the original data. In such exact situations, I am very thankful for the feature to Clone Database Using DBCC CLONEDATABASE.

Clone Database

There are situations when we can’t work on the production environment with the original data. In that scenario, the feature of the Clode Database is very helpful. Let us see the syntax.

The syntax is very simple:


Now let us try to replicate a sample database and see what kind of messages we get.

DBCC CLONEDATABASE (WideWorldImporters,CloneWideWorldImporters)

Here is the message which we see.

Database cloning for ‘WideWorldImporters’ has started with target as ‘CloneWideWorldImporters’.
Database cloning for ‘WideWorldImporters’ has finished. Cloned database is ‘CloneWideWorldImporters’.
Database ‘CloneWideWorldImporters’ is a cloned database. This database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The one line which brings a lot of attention is the database should not be used in the production environment as even though it contains the original schema and statistics, there is no real data inside any of the table. The matter of fact all the tables are empty and if you try to update the command will fail as the cloned database is Read-Only database.

Let us compare the properties of both the database next to each other.

SQL SERVER - Clone Database Using DBCC CLONEDATABASE clonedatabase0

It is very clear from the size of the database, even though the database is cloned it seems like lots of data might be missing. Let us run two identical queries and check the execution plan and their results on the databases.

Let us run the following queries.

FROM WideWorldImporters.Sales.InvoiceLines
WHERE StockItemID = 67
FROM CloneWideWorldImporters.Sales.InvoiceLines
WHERE StockItemID = 67

Now let us check the results.

SQL SERVER - Clone Database Using DBCC CLONEDATABASE clonedatabase1

In the cloned database there are no data.

Now let us check the execution plan for the queries.

SQL SERVER - Clone Database Using DBCC CLONEDATABASE clonedatabase2

When you check the execution plan of the queries you will notice that it is actually identical to each other. This is because even though in the cloned database the data has NOT been cloned the statistics have been carried forward as they were in the original database. The statistics in the database are helping the engine to create an identical plan even till the details till the missing indexes.

SQL SERVER - Clone Database Using DBCC CLONEDATABASE clonedatabase3

Let me know what you think of this blog post. I hope when you can’t access the original data, the similar arrangements can help you to tune your database. Due to newer requirements privacy, I am finding this new feature to clone database very helpful in my consultancy Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Query Without Join Showing Query Plan With Join
Next Post
SQL SERVER – SET NOCOUNT – Impact on Stored Procedure Performance

Related Posts

5 Comments. Leave new

  • So, the advantage using the clone database for reporting can avoid locks on the prod DB. Still this db, if it exists on the same prod db Instance, can consume the resources of the prod server. Any other Pros/Cons of this feature ?

  • Thanks so much. Just wondering on the backup date. If this is active DB the last backup (2016) compromises the data. You should have periodic backup schedule.

  • I use this method for reporting server
    And it’s great

  • Víctor Cárabez
    April 16, 2020 12:04 am

    For Cloning with production purposes (I Think is only supported in MS SQL Server 2019) use:

    DBCC CLONEDATABASE (‘Source’, ‘Destination_ProdReady’)


Leave a Reply