NEW T-SQL FEATURES IN SQL SERVER 2017- I [ DBCC CLONEDATABASE ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

Microsoft introduced a new management command called DBCC CLONEDATABASE command in MS SQL SERVER 2017. This command will generate a clone of a database by using DBCC CLONEDATABASE. With DBCC CLONEDATABASE feature you can generate a clone of a database in order to investigate a performance issue related to a Query or Workload.

Cloning process:-

1. Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
2. Creates an internal snapshot of the source database.
3. Copies the system metadata from the source to the destination database.
4. Copies all schema for all objects from the source to the destination database.
5. Copies statistics for all indexes from the source to the destination database.

Notes–

1. This new database can use used to investigate a performance issue related to the query optimizer.
2. The new DB is primarily intended for troubleshooting and diagnostic purposes.
3. Microsoft recommends detaching of the cloned database after the database is created.
4. DBCC CLONEDATABASE is not supported to be used as a production database.
5. The new cloned database created is ReadOnly, with no data. The most important point is that it will have Statistics.
6. As best practice suggested by Microsoft product team this clone Database is not supposed to remain in PROD database
, but can be moved to a Dev or Test box for further troubleshooting and diagnostic purposes.

On other SQL Versions–

You can get the same command in other versions also by installing the service packs.

Sample example for [ CREATE OR ALTER ]

Generate Sample Data

--

--Create a new Database

CREATE DATABASE testPawan
GO

--Use the new database

USE testPawan
GO

--Create a new table in the new database

CREATE TABLE [dbo].[SplitStrings](
    [Id] [int] NULL,
    [Vals] [varchar](100) NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (1, N'a,b')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (2, N'a')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (3, N'b,3')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (4, N'c,u')
GO


--

TableData

CLONEDATABASE Command

--

--CLONEDATABASE Command

DBCC CLONEDATABASE ( 'testPawan' , 'NewPawan' )
GO
                                    
--

Output

--

Database cloning for 'testPawan' has started with target as 'NewPawan'.
Database cloning for 'testPawan' has finished. Cloned database is 'NewPawan'.
Database 'NewPawan' is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use 
in a production environment. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
                                    
--

The CLONEDATABASE command will create the new readonly database called NewPawan. Check out the details

Check Data in the new database table

--

USE NewPawan
GO

SELECT * FROM NewPawan.dbo.SplitStrings



Id          Vals
----------- ----------------------------------------------------------------------------------------------------

(0 rows affected)
                                    
--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com