About DBCC CLONEDATABASE
Note The newly generated database generated from DBCC CLONEDATABASE isn't supported to be used as a production database and is primarily intended for troubleshooting and diagnostic purposes. We recommend detaching the cloned database after the database is created.
The cloning of the source database is performed by the following operations:
- Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
- Creates an internal snapshot of the source database.
- Copies the system metadata from the source to the destination database.
- Copies all schema for all objects from the source to the destination database.
- Copies statistics for all indexes from the source to the destination database.
DBCC CLONEDATABASE (source_database_name, target_database_name)[WITH [NO_STATISTICS][,NO_QUERYSTORE]]
This argument is the name of the database whose schema and statistics need to be copied.
This argument is the name of the database which the schema and statistics from the source database will be copied to. This database will be created by DBCC CLONEDATABASE and should not already exist.
This argument specifies if table/index statistics needs to be excluded in the clone. If this option is not specified, table/index statistics are automatically included. This option is available starting with SQL Server 2014 SP2 CU3 and SQL Server 2016 Service Pack 1.
This argument specifies if query store needs to be excluded in the clone. If this option is not specified, query store data is copied to the clone if it is enabled in the source database. This option is available starting with SQL Server 2016 Service Pack 1.
When to use DBCC CLONEDATABASE?
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 run the following operations:
- Creating primary data file and log file
- Adding secondary dataspaces
- Adding secondary files
Note All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.
- Internal Database snapshot
DBCC CLONEDATABASE uses an internal database 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.
Database level locks are held during following steps of the copy process:
- 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
As soon as the command has finished running, the internal snapshot is dropped. TRUSTWORTHY and DB_CHAINING options are turned off on a cloned database.
Only the following objects schema will be cloned in the destination database. Encrypted objects get cloned but arent supported in clone in this release of SQL Server. Any objects that are not listed in the following section aren't supported in the cloned:
- APPLICATION ROLE
- AVAILABILITY GROUP
- COLUMNSTORE INDEX
- Change Tracking (SQL Server 2016 SP2 CU10, SQL Server 2017 CU17, SQL Server 2019 CU1 and later versions)
- CLR (starting SQL Server 2014 SP2 CU3, SQL Server 2016 Service Pack 1 and later versions)
- DATABASE PROPERTIES
- FILES AND FILEGROUPS
- Full text (starting SQL Server 2016 SP1 CU2)
- PARTITION FUNCTION
- PARTITION SCHEME
Note T-SQL procedures is supported in all releases starting SQL Server 2014 SP2. CLR procedures is supported starting SQL Server 2014 SP2 CU3. Natively compiled procedures is supported starting SQL Server 2016 SP1.
- QUERY STORE (only in SQL Server 2016 Service Pack 1 and later versions)
Note Query Store is copied only if it is enabled on the source database. To copy the latest runtime statistics as part of Query Store, execute sp_query_store_flush_db to flush the runtime statistics to the query store before executing DBCC CLONEDATABASE.
- SPATIAL INDEX
- MEMORY OPTIMIZED TABLES (only in SQL Server 2016 SP1 and later versions).
- FILESTREAM AND FILETABLE OBJECTS (Starting SQL Server 2014 SP2 CU3, SQL Server 2016 SP1 and later versions).
- UPGRADED DB
- XML INDEX
- XML SCHEMA COLLECTION
Limitations and Considerations
DBCC CLONEDATABASE doesnt support creation of a clone if there are any user objects (tables, indexes, schemas, roles, and so on) that were created in the model database. If user objects are present in the model database, the database clone fails with following error message:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object <system table> with unique index 'index name'. The duplicate key value is <key value>
For information related to data security on cloned databases, see the following blog:
Understanding data security in cloned databases.
If you have column store indexes, see the following blog:
Considerations when you tune the queries with Columnstore indexes on clone databases to update columnstore index statistics before you run the DBCC CLONEDATABASE command.
Error log messages
<Timestamp> spid53 Starting up database 'sourcedb_clone'.
<Timestamp> spid53 Setting database option TRUSTWORTHY to OFF for database 'sourcedb_clone'.
<Timestamp> spid53 Setting database option DB_CHAINING to OFF for database 'sourcedb_clone'.
<Timestamp> spid53 Starting up database 'sourcedb_clone'.
<Timestamp> spid53 Database 'sourcedb_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.
<Timestamp> spid53 Database cloning for 'sourcedb' has finished. Cloned database is 'sourcedb_clone'.
- Creating a clone of the AdventureWorks database that includes schema, statistics and query store (SQL Server 2016 SP1 and later versions)
-- Generate the clone of AdventureWorks database.
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone);
- Creating a schema-only clone of the AdventureWorks database in SQL Server 2014 without statistics (SQL Server 2014 SP2 CU3 and later versions)
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS
- Creating a schema-only clone of the AdventureWorks database without statistics and query store (SQL Server 2016 SP1 and later versions)
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS,NO_QUERYSTORE