This update introduces a new management command DBCC CLONEDATABASE (Transact-SQL) in Microsoft SQL Server 2014 Service Pack 2. This command creates a new database that contains the schema of all the objects and statistics from the specified source database.
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 do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.
Microsoft Customer Support Services may ask you to generate a clone of a database by using DBCC CLONEDATABASE in order to investigate a performance issue related to the query optimizer.
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.
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.
When to use DBCC CLONEDATABASE?
DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues. Be aware of the following restrictions and supported objects:
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 schema of the following objects will be copied to the destination database. Any objects that are not listed in the following section aren't copied:
FILES AND FILEGROUPS
PROCEDURE Note T-SQL procedures only. Natively compiled stored procedures and CLR procedures won't be copied.
TABLE Note Only user and filestream tables are copied. Memory optimized tables and File Tables won't be copied.
XML SCHEMA COLLECTION
You have to have the membership in the sysadmin fixed server role.
Error log messages
The following messages are logged in the error log during the cloning process:
<Timestamp> spid53 Database cloning for 'sourcedb' has started with target as 'sourcedb_clone'.
<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'.
A new database property IsClone is added. DATABASEPROPERTYEX('dbname', 'IsClone') will return 1 if the database is generated by using DBCC CLONEDATABASE.
Example - Creating a clone of the AdventureWorks database
The following example executes DBCC CLONEDATABSE for the AdventureWorks database.
Transact-SQL -- Generate the clone of AdventureWorks database. DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone); GO
Learn about the terminology that Microsoft uses to describe software updates.
Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web