info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



DBCC CLONEDATABASE
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-01-03








Use DBCC CLONEDATABASE to create a Schema and Statistics only copy of a User database. This is possible in SQL Server 2014 SP2 and SQL Server 2016 SP1.

It is commonly used to investigate a performance issue related to Query Optimizer, testing query performance without requiring all the space needed for the data in the database, test database deployment scripts that modify schema and/or code, or to troubleshoot issues with schema or statistics.

You can detach a Cloned Database and attach it to another server if required. Can also set it to Read_Write, change Compatibility Mode & change
Recovery Model.

It is recommended practice not to keep a Cloned Database on a Production Server.

The Cloned Database name should not already exist.

The Source Database must be a user database. Cloning of system databases isn't allowed.

The Source Database must be online or readable.

The file names for the Cloned/Destination Database will follow the source_file_name _underscore_random number convention.

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.

Executing SET STATISTICS IO ON and SET STATISTICS TIME ON against a Source and Clone will show differences since the Clone has no data in it.

If analyze Execution Plans on Source and Clone, they are the same for both databases except for the actual data moving through the plan steps.


Create a Clone of AdventureWorks database:

DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone);
GO

Output:

Msg 2526, Level 16, State 3, Line 3
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

This occurs if you are not using the correct engine version. Need SQL Server 2014 SP2 and SQL Server 2016 SP1.


Create a Clone of AdventureWorks database:

DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone);
GO

Output:

Database cloning for 'AdventureWorks' has started with target as 'AdventureWorks_Clone'.

Database cloning for 'AdventureWorks' has finished. Cloned database is 'AdventureWorks_Clone'.

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

DBCC execution completed. If DBCC printed error messages, contact your system administrator.




That's it!!!



T-SQL: Compare Database settings of Source and Clone:

select [name], is_read_only, snapshot_isolation_state, snapshot_isolation_state_desc from sys.databases where name like 'AdventureWorks%'




T-SQL: Compare sysobjects of Source and Clone:

Select XType, count(*) as 'Total' from [AdventureWorks].dbo.sysobjects group by xtype order by xtype asc
Select XType, count(*) as 'Total' from [AdventureWorks_Clone].dbo.sysobjects group by xtype order by xtype asc




T-SQL: Compare Statistics of Source and Clone:

Select * from [AdventureWorks].sys.objects where name = 'Employee' and type = 'U'
Select * from [AdventureWorks_Clone].sys.objects where name = 'Employee' and type = 'U'
Select * from [AdventureWorks].sys.stats where object_id = 1237579447 order by name asc
Select * from [AdventureWorks_Clone].sys.stats where object_id = 1237579447 order by name asc