Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server DBCC CLONEDATABASE Example


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Microsoft has recently released SQL server 2014 SP2 in which a new DBCC command - DBCC CLONEDATABASE is introduced.  I found this command very useful and interesting too. In this tip we will explore the functionality of DBCC CLONEDATABASE.

Solution

Many times we have issues in our environment and we want to analyze a snapshot of the database when the issue occurred with the statistics, structure, indexes, stored procedures, etc.  Normally we do so by restoring the database to another environment to the closest point in time or script out the database to load onto another instance, but this can be time consuming, complex and may require additional storage.

With the release of Microsoft SQL Server 2014 SP2 Microsoft solves this problem by introducing the DBCC CLONEDATABASE command. As the name alludes, this command is for creating a database clone.  This command creates a new database that contains the schema of all the objects and statistics from the specified source database without any data.

SQL Server DBCC CLONEDATABASE Creation Process

The syntax for the DBCC CLONEDATABASE is:

DBCC CLONEDATABASE (source_database_name, target_database_name)

As per MSDN, it uses a snapshot of the source database for the transactional consistency that is needed to perform the copy. This prevents blocking and concurrency problems when these commands are executed. If a snapshot cannot be created, DBCC CLONEDATABASE will fail.

The DBCC CLONEDATABASE works with the following steps:

SQL Server DBCC CLONEDATABASE Creation Process
  • Validate the source database
  • Get Shared locks for the source database
  • Create internal snapshot of the source database
  • Create a clone database (this is an empty database which is inherited from model)
  • Get an eXclusive lock for the clone database
  • Copy the system metadata from the source to the destination database
  • Copy all schema for all objects from the source to the destination database
  • Copy the statistics for all indexes from the source to the destination database
  • Release all database locks

As soon as the command has finished running, the internal snapshot is dropped. TRUSTWORTHY and DB_CHAINING options are turned off for a cloned database.

SQL Server DBCC CLONEDATABASE Example

Let's explore the DBCC CLONEDATABASE functionality now.  For the test I am using the Adventureworks2014 database which is about 7 GB based on my testing.

DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone)

SQL Server DBCC CLONEDATABASE Example

The DBCC CLONEDATABASE will generate the following message:

Database cloning for 'AdventureWorks2014' has started with target as 'AdventureWorks2014_Clone'.
Database cloning for 'AdventureWorks2014' has finished. Cloned database is 'AdventureWorks2014_Clone'.
Database 'AdventureWorks2014_Clone' is a cloned database.
A cloned 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. 

Note the warning the command generates with the database creation that it should be used for diagnostic purposes only.

In the Databases folder of SQL Server Management Studio we can see that the database has been created and is in a 'Read Only' state.

Cloned databases has been created in Databases folder of SQL Server Management Studio we can see that the database has been created and is in a 'Read Only' state

Validating the SQL Server Database File Properties

Now if we compare the properties of this clone database, with the source database we will see differences in terms of the database size.

Validating the SQL Server Database File Properties

We can also query the system views to see the basic file properties:

SELECT
 db.name AS DBName,
 type_desc AS FilType,
 Physical_Name AS Location
FROM
 sys.master_files mf
INNER JOIN
 sys.databases db on db.database_id=mf.database_id
 where db.name in('AdventureWorks2014','AdventureWorks2014_Clone')

query the system views to see the basic file properties

As we can see the file names for the destination database uses the naming convention of the "source_file_name" plus "underscore_random_number".

Now if we review the file growth of the Model, source and clone database we can see that the growth properties of the clone database match the model database.

SELECT   'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files where database_id in  (DB_id('AdventureWorks2014'),DB_ID('AdventureWorks2014_Clone') ,
DB_ID('model'))
ORDER BY type_desc

file growth of the Model, source and clone database

Execution plan comparison on Source and Cloned SQL Server database

Now to see the actual usage of the clone database, let's run a query against the source database and include the execution plan.

select A.salesorderID,A.Orderdate,A.DueDate,A.SalesOrderNumber,
A.onlineorderflag,B.Carriertrackingnumber,B.Unitprice,B.UnitPriceDiscount
from [Sales].[SalesorderHeaderEnlarged] A inner join
[Sales].[SalesOrderDetailEnlarged] b
on A.SalesorderID=B.SalesOrderID order by A.Orderdate desc

Execution plan comparison on Source and Cloned SQL Server database


SQL Server Query Results

Now run the same query against the clone database and see the execution plan.

Execution plan comparison on Source and Cloned SQL Server database


SQL Server Query Results

Based on this example, we can see that the clone database generates the same basic execution plan as of the source database without any data.  This enables us to generate the same kind of execution plan, so we can troubleshoot an issue.

One item to note is that the Clone database is read only and will not change.  So the statistics will be at the point in time when the clone was created despite changes in the source database.

SQL Server Database Clone vs. Database Snapshot

One common question is: what is the difference between the database clone and database snapshot operations?  A SQL Server Database Snapshot is a read-only, static view of a SQL Server database.  The engine keeps the Database Snapshot consistent by copying the modified pages from the source database to the snapshot database. While DBCC CLONEDATABASE creates a copy of the schema and statistics only of a user database without any data and no changes are reflected in the cloned database.

Learn more about SQL Server Database Snapshots:

Restrictions

Clone database can be created for the following databases:

  • The source database must be a user database. Cloning a system database (master, model, msdb, tempdb, distribution, 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 an explicit transaction.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools