SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database cloning – CLONEDATABASE

Database cloning is a new feature supported in SQL Server. Only SQL Server 2014 SP2 and SQL Server 2016 SP1 support cloning.

The command syntax is very simple

DBCC CLONEDATABASE (source_database_name, target_database_name)

For example the next command

DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone)

will create database AdventureWorks2014_Clone with files created in the same folders used by AdventureWorks2014 but with sizes as the model database. The command creates an internal snapshot of AdventureWorks2014 and copies the system metadata, all schema, all objects and all stats and indexes to AdventureWorks2014_Clone.

The files of the cloned database will additionally have _underscore_random number to make them different from the original files.

The following command will make a clone of a database but without the statistics and query store information.

DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone)
WITH NO_STATISTICS,NO_QUERYSTORE

When you create a CLONE database the command gives an informational message

Database ‘AdventureWorks2014_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.

You can clone a cloned database. For example the following command will make a clone of AdventureWorks2014_Clone to AdventureWorks2014_Clone2. The database can be in READ-ONLY state while cloning.

This command works

DBCC CLONEDATABASE (AdventureWorks2014_Clone, AdventureWorks2014_Clone2)

There is a difference between a Database clone and a Database snapshot: The cloned database doesn’t depend on the changes in the source database after its creation, whilst the snapshot database does continue to be a read-only static view of the changes in the source database.

Some restrictions:

  • DBCC CLONEDATABASE cannot copy the encrypted objects of the database.
  • DBCC CLONEDATABASE doesn’t move the data of the source database to the target.
  • Cloning a system database isn’t allowed.
  • The source database must be online or readable.

Si vis pacem, para sql

Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.

Comments

Leave a comment on the original post [igormicev.com, opens in a new window]

Loading comments...