Tag: newfeatures

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