DBCC CLONEDATABASE (Transact-SQL)

Άρθρο
02/09/2024

Applies to: SQL Server

Generates a schema-only, read-only copy of a database by using DBCC CLONEDATABASE in order to investigate performance issues related to the query optimizer.

Transact-SQL syntax conventions

Syntax

DBCC CLONEDATABASE
(
    source_database_name
    ,  target_database_name
)
    [ WITH { [ NO_STATISTICS ] [ , NO_QUERYSTORE ] [ , VERIFY_CLONEDB | SERVICEBROKER ] [ , BACKUP_CLONEDB ] } ]

Arguments

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

Applies to: SQL Server 2014 (12.x) Service Pack 2 CU 3, SQL Server 2016 (13.x) Service Pack 1, and later versions.

Specifies if table/index statistics need to be excluded from the clone. If this option isn't specified, table/index statistics are automatically included.

NO_QUERYSTORE

Applies to: SQL Server 2016 (13.x) Service Pack 1 and later versions.

Specifies if Query Store data needs to be excluded from the clone. If this option isn't specified, Query Store data will be copied to the clone if the Query Store is enabled in the source database.

VERIFY_CLONEDB

Applies to: SQL Server 2014 (12.x) Service Pack 3, SQL Server 2016 (13.x) Service Pack 2, SQL Server 2017 (14.x) CU 8, and later versions.

Verifies the consistency of the new database. Enabling VERIFY_CLONEDB also disables statistics and Query Store collection, thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS, NO_QUERYSTORE.

The following command can be used to determine if the cloned database has been verified:

SELECT DATABASEPROPERTYEX('clone_database_name', 'IsVerifiedClone');

SERVICEBROKER

Applies to: SQL Server 2014 (12.x) Service Pack 3, SQL Server 2016 (13.x) Service Pack 2, SQL Server 2017 (14.x) CU 8, and later versions.

Specifies if service broker related system catalogs should be included in the clone. The SERVICEBROKER option can't be used in combination with VERIFY_CLONEDB.

BACKUP_CLONEDB

Applies to: SQL Server 2014 (12.x) Service Pack 3, SQL Server 2016 (13.x) Service Pack 2, SQL Server 2017 (14.x) CU 8, and later versions.

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.

Remarks

A clone of a database generated with DBCC CLONEDATABASE is only intended for troubleshooting and diagnostic purposes. The clone is a read-only, schema-only copy of the original database and has limitations on which objects are copied over. See the Supported objects section for more details. Any other use of a clone database isn't supported.

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, and so on) 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.

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 (15.x), the manual steps outlined in the article above will no longer be required as the DBCC CLONEDATABASE command gathers this information automatically.

Stats blob for columnstore indexes

Starting with SQL Server 2019 (15.x), DBCC CLONEDATABASE automatically captures the stats blobs for columnstore indexes, so no manual steps are required. DBCC CLONEDATABASE creates a schema-only copy of a database that includes all the elements necessary to troubleshoot query performance issues without copying the data. In previous versions of SQL Server, the command didn't copy the statistics necessary to accurately troubleshoot columnstore index queries and manual steps were required to capture this information.

For information related to data security on cloned databases, see Understanding data security in cloned databases.

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. 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 shared (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 exclusive (X) lock for the clone database
Copy the metadata to the clone database
Release all database 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.

Supported objects

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 aren't listed in the following section aren't supported in the clone:

APPLICATION ROLE
AVAILABILITY GROUP
COLUMNSTORE INDEX
CDB
CDC
Change Tracking ^{6, 7, 8}
CLR ^{1, 2}
DATABASE PROPERTIES
DEFAULT
FILES AND FILEGROUPS
Full text ³
FUNCTION
INDEX
LOGIN
PARTITION FUNCTION
PARTITION SCHEME
PROCEDURE ⁴
QUERY STORE ^{2, 5}
ROLE
RULE
SCHEMA
SEQUENCE
SPATIAL INDEX
STATISTICS
SYNONYM
TABLE ⁹
MEMORY OPTIMIZED TABLES ²
FILESTREAM AND FILETABLE OBJECTS ^{1, 2}
TRIGGER
TYPE
UPGRADED DB
USER
VIEW
XML INDEX
XML SCHEMA COLLECTION

¹ Starting in SQL Server 2014 (12.x) Service Pack 2 CU 3.

² Starting in SQL Server 2016 (13.x) Service Pack 1.

³ Starting in SQL Server 2016 (13.x) Service Pack 1 CU 2.

⁴ Transact-SQL procedures are supported in all releases starting with SQL Server 2014 (12.x) Service Pack 2. CLR procedures are supported starting with SQL Server 2014 (12.x) Service Pack 2 CU 3. Natively compiled procedures are supported starting with SQL Server 2016 (13.x) Service Pack 1.

⁵ 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.

⁶ Starting in SQL Server 2016 (13.x) Service Pack 2 CU 10.

⁷ Starting in SQL Server 2017 (14.x) Service Pack 2 CU 17.

⁸ Starting in SQL Server 2019 (15.x) CU 1 and later versions.

⁹ Most system tables flagged as is_ms_shipped aren't cloned.

Permissions

Requires membership in the sysadmin fixed server role.

Error log messages

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'.

About service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You don't have to install a previous service pack before you install the latest service pack. See Table 1 in Latest updates and version history for SQL Server for finding more information about the latest service pack and latest cumulative update.

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.

Database properties

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.

Examples

A. Create a clone of a database that includes schema, statistics and Query Store

The following example creates a clone of the AdventureWorks2022 database that includes schema, statistics and Query Store data (SQL Server 2016 (13.x) Service Pack 1 and later versions):

DBCC CLONEDATABASE (AdventureWorks2022, AdventureWorks_Clone);
GO

B. Create a schema-only clone of a database without statistics

The following example creates a clone of the AdventureWorks2022 database that doesn't include statistics (SQL Server 2014 (12.x) Service Pack 2 CU 3 and later versions):

DBCC CLONEDATABASE (AdventureWorks2022, AdventureWorks_Clone) WITH NO_STATISTICS;
GO

C. Create a schema-only clone of a database without statistics and Query Store

The following example creates a clone of the AdventureWorks2022 database that doesn't include statistics and Query Store data (SQL Server 2016 (13.x) Service Pack 1 and later versions):

DBCC CLONEDATABASE (AdventureWorks2022, AdventureWorks_Clone) WITH NO_STATISTICS, NO_QUERYSTORE;
GO

D. Create a clone of a database that is verified

The following example creates a schema-only clone of the AdventureWorks2022 database without statistics and Query Store data that is verified (SQL Server 2016 (13.x) Service Pack 2 and later versions):

DBCC CLONEDATABASE (AdventureWorks2022, AdventureWorks_Clone) WITH VERIFY_CLONEDB;
GO

E. Create a clone of a database that is verified for use that includes a backup of the cloned database

The following example creates a schema-only clone of the AdventureWorks2022 database without statistics and Query Store data that is verified for use. A verified backup of the cloned database will also be created (SQL Server 2016 (13.x) Service Pack 2 and later versions).

DBCC CLONEDATABASE (AdventureWorks2022, AdventureWorks_Clone) WITH VERIFY_CLONEDB, BACKUP_CLONEDB;
GO

Κοινή χρήση μέσω

DBCC CLONEDATABASE (Transact-SQL)

Syntax

Arguments

source_database_name

target_database_name

NO_STATISTICS

NO_QUERYSTORE

VERIFY_CLONEDB

SERVICEBROKER

BACKUP_CLONEDB

Remarks

Stats blob for columnstore indexes

Internal database snapshot

Supported objects

Permissions

Error log messages

About service packs for SQL Server

Database properties

Examples

A. Create a clone of a database that includes schema, statistics and Query Store

B. Create a schema-only clone of a database without statistics

C. Create a schema-only clone of a database without statistics and Query Store

D. Create a clone of a database that is verified

E. Create a clone of a database that is verified for use that includes a backup of the cloned database

See also

Σχόλια

Πρόσθετοι πόροι