Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Clone a SQL Server Database Using DBCC CLONEDATABASE


By:   |   Read Comments (5)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

During your work as SQL Server DBA, you might need to create a copy of a production database to simulate testing scenarios on the production schema and statistics, or troubleshoot query performance issues without interrupting production users. Is there a quick and easy way to create a copy of a production database that includes only the source database schema and statistics?

Solution

In SQL Server, there are many ways that can be used to create a copy of a database on the same SQL Server or on a remote SQL Server, such as scripting the database using the Generate Scripts database task and applying the generated script on the same server or to a remote SQL Server. This method will create a new database with the same schema as the source database, but it will not script the source database statistics automatically, you need to enable scripting the statistics from the advanced scripting options before generating the script, otherwise you will not be able to fully simulate production troubleshooting and testing as the production statistics will not be generated on the database copy.

Another way is to take a backup from the source database and restore the backup to a SQL instance. This will give you a complete copy of the database, but you may not have space or want to have all of the data residing on another server.

SQL Server DBCC CLONEDATABASE

In SQL Server 2014 SP2, Microsoft introduced a new DBCC command that can be used to create a read-only copy of an existing database in one shot that includes the schema and statistics of the source database but no data, this command is DBCC CLONEDATABASE.

The process of creating a cloned database is performed by creating a new database inheriting the database file properties from the model system database. Then the system metadata, schema and statistics are copied from the source database to the new destination database.

In order to create a cloned copy for a specific database, the source database should be an online readable user database, as creating a cloned copy of a system database is not allowed.

Before we create a cloned database, we need to make sure that the current SQL Server instance supports the new DBCC command. The version should be SQL Server 2014 SP2 or later as shown below:

Check the SQL Server Version

The statement that is used to create the cloned database is very simple, you only need to provide the source database name and the cloned database name. The new database will be created in the same SQL instance, so you need to make sure that the name of the cloned database that you provide in the DBCC command is not a name of any other database within the current instance, otherwise the cloned database creation will fail.

Assume that we need to create a copy of our MSSQLTipsDemo database that includes its schema and statistics to troubleshoot a performance issue for one of our queries, the DBCC CLONEDATABASE command will be:

DBCC CLONEDATABASE ('MSSQLTipsDemo', 'MSSQLTipsDemoClone')

After completing the query execution successfully, the below result message will be displayed:

Successfully creating a SQL Server Database via DBCC CLONEDATABASE

As described in the previous result message, the created database shouldn’t be used as production database and that it is created for troubleshooting purposes only.

The command that is used to create the cloned database is safe, as it creates an internal snapshot of the source database to create the cloned copy, without causing any blocking during the database creation.

If we refresh the Databases node from the Object Explorer in SQL Server Management Studio, we can see a new database created in gray, indicating that the database is in a Read-Only state as shown below:

The database can now be used and you can change it to a Read-Write state easily by applying the ALTER DATABASE statement below:

USE [master]
GO
ALTER DATABASE [MSSQLTipsDemoClone] SET READ_WRITE WITH NO_WAIT
GO

As mentioned previously, the cloned copy will have the same schema and statistics as the source database, but no data.  Below we can see that all of the table objects are the same for both databases.

Comparison of Tables in SQL Server Management Studio between the original and cloned databases

A new database property called IsClone can be used to check if the database is a cloned copy. To check if our database is cloned, we can run the below query:

SELECT DATABASEPROPERTYEX('MSSQLTipsDemoClone', 'isClone') AS IsClonedDB

The result is as follows:

IsClonedDB SQL Server Database Property

Now, you can perform the testing scenarios or troubleshooting that you need on the newly created database.  You can also detach it and attach it to a development server, depending on your situation and the purpose for creating the cloned database.

Next Steps
  • Make sure not to use the cloned copy database as a production database as it is not supported by Microsoft.
  • It is better to detach the cloned copy database and attach it to a test server in order to apply your troubleshooting and testing without affecting the production server.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

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     



Thursday, June 29, 2017 - 1:25:01 PM - itismeap Back To Top

This doesn't appear to clone Service Broker related schemaa which is a little disapointing


Sunday, November 20, 2016 - 1:36:26 AM - Victor Back To Top

It's nice BUT without DATA ???

What exactly can i do with this, usually we need some data.

No meaning at all.


Saturday, August 27, 2016 - 2:25:39 PM - Humayoon khan Back To Top

 It is very helpful tip and exqctly what i needed this week. Thanks

 


Monday, August 15, 2016 - 12:56:45 PM - Anne Back To Top

Thanks,  this is good, we have another option to create schema only and statistics for a production database.

But usually we also need the data for tables that have domain data or lookup data. So we still need a step to import or populate data for those tables to do testing cases.


Friday, August 12, 2016 - 11:06:24 AM - Timothy A Wiseman Back To Top

Excellent tip.  I have generally restored additional copies from backups for testing, but as you say this can take a while and consumes vast amounts of space.  The clone technique you describe here seems like it could be excellent when you need to do testing wtih the schema and statistics.


Learn more about SQL Server tools