Transact-SQL Syntax ConventionsGenerates a schema-only clone of a database by using DBCC CLONEDATABASE in order to investigate performance issues related to the query optimizer.
Transact-SQL Syntax Conventions
DBCC CLONEDATABASE
(
source_database_name
, target_database_name
[ WITH { [ NO_STATISTICS ] [ , NO_QUERYSTORE ] [ , VERIFY_CLONEDB | SERVICEBROKER ] [ , BACKUP_CLONEDB ] } ]
) source_database_name
The name of the database to be copied.
target_database_name
The name of the database the source database will be copied to. This database will be created by DBCC CLONEDATABASE and shouldn’t already exist.
NO_STATISTICS
Specifies if table/index statistics need to be excluded from the clone. If this option is not specified, table/index statistics are automatically included. This option is available starting with SQL Server 2014 (12.x) SP2 CU3 and SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP1.
NO_QUERYSTORE
Specifies if query store data needs to be excluded from the clone. If this option is not specified, query store data will be copied to the clone if the query store is enabled in the source database. This option is available starting with SQL Server 2016 (13.x) SP1.
VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required if the cloned database is intended for production use. Enabling VERIFY_CLONEDB also disables statistics and query store collection, thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS, NO_QUERYSTORE. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP2, and SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) CU8.
[!NOTE]
The following command can be used to confirm that the cloned database is production-ready:SELECT DATABASEPROPERTYEX('clone_database_name', 'IsVerifiedClone')
SERVICEBROKER
Specifies if service broker related system catalogs should be included in the clone. The SERVICEBROKER option cannot be used in combination with VERIFY_CLONEDB. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP2, and SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) CU8.
BACKUP_CLONEDB
Creates and verifies a backup of the clone database. If used in combination with VERIFY_CLONEDB, the clone database is verified before the backup is taken. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP2, and SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) CU8.
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, 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 from 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.
[!NOTE]
The new database generated from DBCC CLONEDATABASE is primarily intended for troubleshooting and diagnostic purposes. In order for the cloned database to be supported for use as a production database, the VERIFY_CLONEDB option must be used.
All files in the target database will inherit the size and growth settings from the model database. 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.
DBCC CLONEDATABASE doesn’t 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> [!IMPORTANT] If you have columnstore indexes, see Considerations when you tune the queries with Columnstore indexes on clone databases to update columnstore index statistics before you run the DBCC CLONEDATABASE command. Starting with SQL Server 2019, the manual steps outlined in the article above will no longer be required as the DBCC CLONEDATABASE command gathers this information automatically.
For information related to data security on cloned databases, see Understanding data security in cloned databases.
DBCC CLONEDATABASE uses an internal database snapshot of the source database for the transactional consistency that is needed to perform the copy. Using this snapshot prevents blocking and concurrency problems when these commands are executed. If a snapshot can’t 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 (an empty database inherited from the model database) - 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 can be cloned in the destination database. Encrypted objects get cloned but aren’t usable in the clone database. Any objects that are not listed in the following section aren’t supported in the clone: - APPLICATION ROLE - AVAILABILITY GROUP - COLUMNSTORE INDEX - CDB - CDC - CLR (starting in SQL Server 2014 (12.x) SP2 CU3, SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP1 and later versions) - DATABASE PROPERTIES - DEFAULT - FILES AND FILEGROUPS - Full text (starting in SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SP1 CU2) - FUNCTION - INDEX - LOGIN - PARTITION FUNCTION - PARTITION SCHEME - PROCEDURE
> [!NOTE]
> SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) Transact\-SQL procedures are supported in all releases starting with SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) Transact\-SQL SQL Server 2014 (12.x) SP2. CLR procedures are supported starting with SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) Transact\-SQL SQL Server 2014 (12.x) SQL Server 2014 (12.x) SP2 CU3. Natively compiled procedures are supported starting with SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) Transact\-SQL SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SP1.
QUERY STORE (starting with SQL Server 2016 (13.x) SP1)
> [!NOTE]
> Query store data is copied only if it is enabled on the source database. To copy the latest runtime statistics as part of the query store, execute sp_query_store_flush_db to flush the runtime statistics to the query store before executing DBCC CLONEDATABASE.
XML SCHEMA COLLECTION
Requires membership in the sysadmin fixed server role.
The following messages are an example of the messages logged in the error log during the cloning process:
2018-03-26 15:33:56.05 spid53 Database cloning for 'sourcedb' has started with target as 'sourcedb_clone'.
2018-03-26 15:33:56.46 spid53 Starting up database 'sourcedb_clone'.
2018-03-26 15:33:57.80 spid53 Setting database option TRUSTWORTHY to OFF for database 'sourcedb_clone'.
2018-03-26 15:33:57.80 spid53 Setting database option DB_CHAINING to OFF for database 'sourcedb_clone'.
2018-03-26 15:33:57.88 spid53 Starting up database 'sourcedb_clone'.
2018-03-26 15:33:57.91 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.
2018-03-26 15:33:57.92 spid53 Database cloning for 'sourcedb' has finished. Cloned database is 'sourcedb_clone'.DATABASEPROPERTYEX('dbname', 'IsClone') will return 1 if the database was generated by using DBCC CLONEDATABASE.
DATABASEPROPERTYEX('dbname', 'IsVerifiedClone') will return 1 if the database was successfully verified using WITH VERIFY_CLONEDB.
The following example creates a clone of the AdventureWorks database that includes schema, statistics and query store data SQL Server 2016 (13.x) SP1 and later versions)
The following example creates a clone of the AdventureWorks database that does not include statistics SQL Server 2014 (12.x) SP2 CU3 and later versions)
The following example creates a clone of the AdventureWorks database that does not include statistics and query store data SQL Server 2016 (13.x) SP1 and later versions)
The following example creates a schema-only clone of the AdventureWorks database without statistics and query store data that is verified for use as a production database SQL Server 2016 (13.x) SP2 and later versions).
The following example creates a schema-only clone of the AdventureWorks database without statistics and query store data that is verified for use as a production database. A verified backup of the cloned database will also be created SQL Server 2016 (13.x) SP2 and later versions).
DBCC (Transact-SQL)
How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server