Skip to content

Latest commit

 

History

History
301 lines (208 loc) · 15.2 KB

dbcc-clonedatabase-transact-sql.md

File metadata and controls

301 lines (208 loc) · 15.2 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
DBCC CLONEDATABASE (Transact-SQL)
DBCC CLONEDATABASE generates a schema-only clone of a database by using DBCC CLONEDATABASE in order to investigate performance issues related to the query optimizer.
bluefooted
pamela
randolphwest
12/05/2022
sql
t-sql
language-reference
CLONEDATABASE
CLONE DATABASE
DBCC_CLONEDATABASE_TSQL
DBCC CLONEDATABASE
DBCC CLONE DATABASE
CLONEDATABASE_TSQL
database cloning [SQL Server]
cloning databases
clone databases
cloning database
clone database
copying databases
copy databases
copying database
copy database
NO_STATISTICS option
NO_QUERYSTORE option
VERIFY_CLONEDB option
BACKUP_CLONEDB option
database copying [SQL Server]
DBCC CLONEDATABASE statement
TSQL

DBCC CLONEDATABASE (Transact-SQL)

[!INCLUDE 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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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: [!INCLUDEssSQL14] Service Pack 2 CU 3, [!INCLUDEsssql16-md] 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: [!INCLUDEsssql16-md] 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: [!INCLUDEssSQL14] Service Pack 3, [!INCLUDEsssql16-md] Service Pack 2, [!INCLUDEssSQL17] 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: [!INCLUDEssSQL14] Service Pack 3, [!INCLUDEsssql16-md] Service Pack 2, [!INCLUDEssSQL17] 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: [!INCLUDEssSQL14] Service Pack 3, [!INCLUDEsssql16-md] Service Pack 2, [!INCLUDEssSQL17] 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 [!INCLUDE sssql19-md], 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 [!INCLUDEsql-server-2019], 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 [!INCLUDEssNoVersion], 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 3
  • FUNCTION
  • INDEX
  • LOGIN
  • PARTITION FUNCTION
  • PARTITION SCHEME
  • PROCEDURE 4
  • QUERY STORE 2, 5
  • ROLE
  • RULE
  • SCHEMA
  • SEQUENCE
  • SPATIAL INDEX
  • STATISTICS
  • SYNONYM
  • TABLE 9
  • MEMORY OPTIMIZED TABLES 2
  • FILESTREAM AND FILETABLE OBJECTS 1, 2
  • TRIGGER
  • TYPE
  • UPGRADED DB
  • USER
  • VIEW
  • XML INDEX
  • XML SCHEMA COLLECTION

1 Starting in [!INCLUDEssSQL14] Service Pack 2 CU 3.

2 Starting in [!INCLUDEsssql16-md] Service Pack 1.

3 Starting in [!INCLUDEsssql16-md] Service Pack 1 CU 2.

4 [!INCLUDEtsql] procedures are supported in all releases starting with [!INCLUDEssSQL14] Service Pack 2. CLR procedures are supported starting with [!INCLUDEssSQL14] Service Pack 2 CU 3. Natively compiled procedures are supported starting with [!INCLUDEsssql16-md] Service Pack 1.

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

6 Starting in [!INCLUDEssSQL16-md] Service Pack 2 CU 10.

7 Starting in [!INCLUDEssSQL17-md] Service Pack 2 CU 17.

8 Starting in [!INCLUDEssSQL19-md] CU 1 and later versions.

9 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 [!INCLUDE sssampledbobject-md] database that includes schema, statistics and Query Store data ([!INCLUDEsssql16-md] 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 [!INCLUDE sssampledbobject-md] database that doesn't include statistics ([!INCLUDEssSQL14] 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 [!INCLUDE sssampledbobject-md] database that doesn't include statistics and Query Store data ([!INCLUDEsssql16-md] 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 [!INCLUDE sssampledbobject-md] database without statistics and Query Store data that is verified ([!INCLUDEsssql16-md] 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 [!INCLUDE sssampledbobject-md] database without statistics and Query Store data that is verified for use. A verified backup of the cloned database will also be created ([!INCLUDEsssql16-md] Service Pack 2 and later versions).

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

See also