Database cloning is a new feature supported in SQL Server. Only SQL Server 2014 SP2 and SQL Server 2016 SP1 support cloning.
The command syntax is very simple
DBCC CLONEDATABASE (source_database_name, target_database_name)
For example the next command
DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone)
will create database AdventureWorks2014_Clone with files created in the same folders used by AdventureWorks2014 but with sizes as the model database. The command creates an internal snapshot of AdventureWorks2014 and copies the system metadata, all schema, all objects and all stats and indexes to AdventureWorks2014_Clone.
The files of the cloned database will additionally have _underscore_random number to make them different from the original files.
The following command will make a clone of a database but without the statistics and query store information.
DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone) WITH NO_STATISTICS,NO_QUERYSTORE
When you create a CLONE database the command gives an informational message
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.
You can clone a cloned database. For example the following command will make a clone of AdventureWorks2014_Clone to AdventureWorks2014_Clone2. The database can be in READ-ONLY state while cloning.
This command works
DBCC CLONEDATABASE (AdventureWorks2014_Clone, AdventureWorks2014_Clone2)
There is a difference between a Database clone and a Database snapshot: The cloned database doesn’t depend on the changes in the source database after its creation, whilst the snapshot database does continue to be a read-only static view of the changes in the source database.
Some restrictions:
- DBCC CLONEDATABASE cannot copy the encrypted objects of the database.
- DBCC CLONEDATABASE doesn’t move the data of the source database to the target.
- Cloning a system database isn’t allowed.
- The source database must be online or readable.