SQL SERVER – Clone Database Using DBCC CLONEDATABASE

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:

DBCC CLONEDATABASE (OriginalDB, NewClonedDB)

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

DBCC CLONEDATABASE (WideWorldImporters,CloneWideWorldImporters)
GO

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.

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.

SELECT *
FROM WideWorldImporters.Sales.InvoiceLines
WHERE StockItemID = 67
GO
SELECT *
FROM CloneWideWorldImporters.Sales.InvoiceLines
WHERE StockItemID = 67
GO

Now let us check the results.

In the cloned database there are no data.

Now let us check the execution plan for the queries.

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.

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://darkslategrey-bat-805937.hostingersite.com)