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:
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:
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.
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 IsClonedDBThe result is as follows:
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.
