Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 | SansSQL

Sunday, October 30, 2016

Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

DBCC CLONEDATABASE is a new DBCC command introduced in SQL Server 2014 SP 2 which is used for creating the clone of a specified user database which helps in troubleshooting the performance issues related to the query optimizer.

When a clone of the database is created using DBCC CLONEDATABASE, it will create a schema and statistics only copy of the specified database and does not contain any copy of the data.

Creating the clone is as simple as passing the source database name and clone database name to the DBCC command.
DBCC CLONEDATABASE ('SansSQL', 'SansSQL_Clone') 

Output of the DBCC Command

Once the cloning is completed, the cloned database will be in Read-Only mode.

SELECT name, database_id, is_read_only  
FROM sys.databases 
WHERE name in ('SansSQL', 'SansSQL_Clone') 

So what actually happens when we issue the DBCC CLONEDATABASE command on a database?
It will start with few validations before the clone is created, The following validations are performed by DBCC CLONEDATABASE. The command fails if any of the validations fail.
  • The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.) isn't allowed.
  • The source database must be online or readable.
  • A database that uses the same name as the clone database must not already exist.
  • The command isn't in a user transaction.
If all the validations succeed, DBCC CLONEDATABASE will do the following operations:
  • Creating primary data file and log file
  • Adding secondary dataspaces
  • Adding secondary files
The destination database files will inherit the size and growth settings from the model database and the file names of the destination database will follow the source_file_name _underscore_random_number convention. 

SELECT database_id, file_id, type_desc, name, physical_name 
FROM sys.master_files 
WHERE DB_NAME(database_id) in ('SansSQL', 'SansSQL_Clone') 


Then the DBCC CLONEDATABASE will do a Internal Database Snapshot with the following steps
  • Validate the source database
  • Get S lock for the source database
  • Create snapshot of the source database
  • Create a clone database (this is an empty database which inherits from model)
  • Get X lock for the clone database
  • Copy the metadata to the clone database
  • Release all DB locks
Using the below command, we can check if a database is a clone or a normal database.
SELECT DATABASEPROPERTYEX('SansSQL','isClone') AS SansSQL_DB_CloneStatus 
      ,DATABASEPROPERTYEX('SansSQL_Clone','isClone') AS SansSQL_CloneDB_CloneStatus


Reference: https://support.microsoft.com/en-in/kb/3177838

6 comments:

Unknown said...

nice post

luckys said...
This comment has been removed by a blog administrator.
Scarlette Patterson said...
This comment has been removed by the author.
Scarlette Patterson said...

It is important to use resources that help you solve your problems quickly and efficiently! If you are a student, it is important to use blogs by professional writers, for example, if you do not know how to write a cause and effect essay https://domyessay.com/blog/how-to-write-a-cause-and-effect-essay using this resource you can easily understand what you need to do.

Scarlette Patterson said...

Great article! Everything is clear and understandable! I love such resources, where there is a lot of information on the topic and without unnecessary things! This is very important for me because time is the most valuable resource! I recently needed to write poem analysis essay https://essayservice.com/blog/poem-analysis-essay and thanks to this blog I easily coped with the task because everything is written there clearly and consistently!

Robert Foster said...

Our professional paper writers WritePaper.com are keen on writing academic papers and are always up for a challenge. Our aim is to help people succeed with every assignment on any discipline through our writing. We work with trusted payment companies, such as Visa and MasterCard. To help you make up your mind, see how other customers feel about our service. We want to share a special discount with you on your first purchase. Please leave your email, and we’ll send you a 10% OFF coupon with an exclusive promo code. It helps to complete the order more efficiently, especially when specialists need to clarify some details. After seeing your call, “write my essay for me,” our best writers contact you with proposals to cover your request. We guarantee a wide selection of specialists for you to choose the most suitable by price, rating, and experience. If you can’t wait to see your perfect essay sample, click on the order button and fill in the application form. Here you provide us such details as the number of pages, paper type, discipline, topic, and deadline.

Post a Comment

Ads