DBCC Clone Database in SQLServer 2016 sp1

Imagine you have to troubleshoot slow running queries on a particular client database remotely.The data is sensitive and client can’t share his database..To help in this cases,

SQLServer 2016 SP1 introduced new feature called

DBCC clone('source db name','target db name') [WITH [NO_STATISTICS][,NO_QUERYSTORE]]

Lets create a clone database to see ,how this looks like..

--this creates database with schema,statistics,query store
dbcc clonedatabase('tsql2012','tsqlclone')

When the above command finishes,we will be having a READ ONLY clone database with the name tsqlclone ,The above command also writes to error log as well..

Below is the snip from my local instance..

dbcc clone.PNG

Please refer to below KB for indepth details on how this works..

https://support.microsoft.com/en-in/kb/3177838

For earlier versions,we can use generate scripts option to generate schema ,statistics and run them on empty database to get similar functionality

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s