DBCC CLONEDATABASE in Microsoft SQL Server

March 25, 2024

DBCC CLONEDATABASE command generates a schema-only clone or copy of a database. Effective March 1, 2025, Microsoft will no longer support creating copy of a database using the DBCC CLONEDATABASE command and using it as a new database in a production environment. However, the command will persist for generating schema-only copies solely for diagnostic and troubleshooting purposes. This change impacts all editions of SQL Server 2016 and later versions. 

To generate a production-ready schema-only copy of a database, it’s highly recommended to utilize tools such as Microsoft SQL Server Data Tools (SSDT); the Generate and Publish scripts Wizard; or data-tier application extraction, which includes either the Extract Data-tier Application (DAC) Wizard or a PowerShell script. These tools provide a reliable way to create a copy of a database for use in production environments.

Learn more about

Frequently asked questions

What is SSDT?

SQL Server Data Tools (SSDT) is a modern development tool that integrates with Microsoft Visual Studio and provides design, debugging, and deployment capabilities for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, developers can perform necessary tasks without needing the admin-focused SQL Server Management tool on their developer computer. Essentially, Visual Studio removes unnecessary features like admin tools, and focuses on tools that are useful for developers, like database and schema comparison tools.

What is the Generate and Publish Scripts Wizard?

The Generate and Publish Scripts Wizard is a feature in SQL Server Management Studio (SSMS) that allows you to create scripts for transferring a database between instances of the SQL Server Database Engine or Azure SQL Database. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Database. The generated scripts can be run on another instance of the Database Engine or SQL Database. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database or limit it to specific objects.

What is a data-tier application (DAC)?

A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects—such as tables, views, and instance objects, including logins—associated with a user’s database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Tooling support for data-tier applications enables developers and database administrators to deploy dacpacs to new or existing databases. Deployments to an existing database update the database model from the existing state to match the contents of the dacpac. Developers build DACs from SQL database projects, a declarative development concept for building SQL objects that enables source control on the database schema.

A .bacpac is a related artifact that, by default, encapsulates the database schema and the data stored in the database. The primary use case for a BACPAC is to move a database from one server to another—or to migrate a database from a local server to the cloud—and archiving an existing database in an open format.

What is DBCC CLONEDATABASE command?

DBCC CLONEDATABASE creates a new database that contains the schema of all the objects and statistics from the specified source database. Cloned databases copy all schema and metadata of the source database without copying any data.

How do I use Schema Compare to compare different database definitions?

SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot, or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete, you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

You can save comparisons either as part of an SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for a new comparison.

Why do I need to generate a schema-only clone of a database with statistics in SQL Server?

You will need to generate a schema-only clone of a database with statistics to investigate performance issues.

The query optimizer in SQL Server uses the following types of information to determine an optimal query plan:

Database metadata
Hardware environment
Database session state

Typically, you must simulate all these same types of information to reproduce the behavior of the query optimizer on a test system.

Microsoft Customer Support Services might ask you to generate a schema script of the database along with statistics to investigate a query optimizer issue.