Wednesday, July 27, 2016

SQL Server 2014 SP2 - Clone Database

Looks like there is a new management feature available for SQL Server 2014 SP2 called "clone database".  According to Microsoft the goal is to create a database that contains the schema of all the objects and the current statistics from a specified source.

The main use of this command is to provide customer support a clone of your database so they can investigate performance issues related to the query optimizer.  I am assuming this could be used internally as well to get a copy of a production and restore this database to a support server for troubleshooting as well.  This might eliminate the need to take a native database backup of a large production database thus reducing the overall time, space, and complexity needed to troubleshoot certain issues in your production environment.  Keep in mind there are certain restrictions and you need to be a member of the sysadmin fixed server role.
  • DBCC CLONEDATABASE uses an internal database snapshot of the source database for the transactional consistency that is needed to perform the copy. This prevents blocking and concurrency problems when these commands are executed. If a snapshot cannot be created, DBCC CLONEDATABASE will fail.
See the online KB article https://support.microsoft.com/en-us/kb/3177838

I will be testing with some local databases to see how I can effectively leverage this snapshot but it looks promising and unless  you happened to look at the SQL Server 2014 SP2 release notes you might have missed it.

No comments: