What is DBCC CLONEDATABASE ?

DBCC CLONEDATABASE is new feature comes up with SQL Server 2014 Service Pack 2 and above. DBCC CLONEDATABASE is used to create read only copy of an existing database. Cloned database includes the schema and statistics of the source database but no data.

· Source database should be online & readable while creating clone database.

· Clone database name must not be used by any other database.

· Clone database will be created as READ-ONLY, You can change clone database to read write mode as & when needed.

· Clone database created using copy of MODEL system database and later source database schema and statistics are copied to clone database.

· Creating a cloned copy of a system database is not allowed

· Clone database feature is available with SQL Server 2014 Service Pack 2 and above versions.

· File names for the Clone database will follow the source_file_name _underscore_random number convention. In case file name already exists, DBCC command will fail.

How DBCC CLONEDATABASE works?

· Validate the source database is online and readable

· Get S lock for the source database

· Create snapshot of the source database

· Creates a new destination clone database that uses the same file layout as the source but with default file sizes as the model database (this is an empty database which inherits from model)

· Get X lock for the clone database

· Copy the metadata to the clone database

o Copies the system metadata from the source to the destination database

o Copies all schema for all objects from the source to the destination database

o Copies statistics for all indexes from the source to the destination database

· Release all DB locks

· Drop snapshot of the source database

How to Create clone database?

You can easily create clone database by running below DBCC command. You just need to change source and destination clone database.

DBCC CLONEDATABASE (‘Source_Database’, ‘Destination_Clone_Database’)

Close Database created with Read Only. The database can now be used and you can change it to a Read-Write state easily by applying the ALTER DATABASE statement below:

USE [master]

GO

ALTER DATABASE [Destination_Clone_Database] SET READ_WRITE WITH NO_WAIT

GO

As mentioned above, the cloned database will have only schema and statistics as the source database, but no data.

How to check Database is cloned?

A new database property called IsClone can be used to check if the database is a cloned copy.

SELECT DATABASEPROPERTYEX(‘Database_Name’, ‘isClone’) AS IsClonedDB

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp