How to Use DBCC CLONEDATABASE in SQL Server Older than 2014 SP2

DBCC CloneDatabase command is available only from SQL Server 2014 SP2; it is not available in previous versions. However, if you want to use this on versions older than 2014, then you can use this script.

With 2014 SP2

Whenever there is a performance issue, you need a copy of the production database. However, it is not necessary that the copy you create should be with data to investigate the performance issue. So Microsoft introduced DBCC CLONEDATABASE in the service pack 2 for SQL SERVER 2014. This DBCC command will help users to create copies of production database with just schema and statistics. Please do note that this command will work only with user databases and cannot be used with system databases.

Before 2014 SP2

Run the below script on your SQL server that is older than SQL 2014 SP2. This script will create a procedure dbcc_clonedatabase. You can then execute this procedure by passing the source database name and the new database name as parameters.

CREATE PROCEDURE dbcc_clonedatabase @olddbname VARCHAR(200)
    ,@newdbname VARCHAR(200)
AS
DECLARE @dd NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultData'
    ,@dd OUTPUT

DECLARE @dl NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultLog'
    ,@dl OUTPUT

DECLARE @db NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'BackupDirectory'
    ,@db OUTPUT

DECLARE @md NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg0'
    ,@md OUTPUT

SELECT @md = substring(@md, 3, 255)

SELECT @md = substring(@md, 1, len(@md) - charindex('\', reverse(@md)))

DECLARE @ml NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg2'
    ,@ml OUTPUT

SELECT @ml = substring(@ml, 3, 255)

SELECT @ml = substring(@ml, 1, len(@ml) - charindex('\', reverse(@ml)))

DECLARE @a VARCHAR(1000)
    ,@b VARCHAR(1000)
    ,@c VARCHAR(1000)

SET @a = isnull(@dd, @md)
SET @b = isnull(@dl, @ml)
SET @c = isnull(@db, @ml)

DECLARE @name VARCHAR(50)

SET @name = @olddbname

DECLARE @fileName VARCHAR(1000)

SET @fileName = @c + '\' + @name + '.BAK'

--select   @fileName
BACKUP DATABASE @name TO DISK = @fileName

DECLARE @tfilelist TABLE (
    cLogicalName NVARCHAR(128)
    ,cPhysicalName NVARCHAR(260)
    ,cType CHAR(1)
    ,cFilegroupName VARCHAR(10)
    ,csize INT
    ,cMaxSize BIGINT
    ,cfield INT
    ,ccreatelsn BIT
    ,cdroplsn BIT
    ,cuniqueid UNIQUEIDENTIFIER
    ,creadonlylsn BIT
    ,creadwritelsn BIT
    ,cbackupsizeinbytes BIGINT
    ,csourceblocksize INT
    ,cfilegroupid INT
    ,cloggroupguid UNIQUEIDENTIFIER
    ,cdifferentialbaselsn BIT
    ,cdifferentialbaseguid UNIQUEIDENTIFIER
    ,cisreadonly BIT
    ,cispresent BIT
    ,ctdethumbprint VARCHAR(5)
    );
DECLARE @bs NVARCHAR(1000)

SET @bs = 'restore filelistonly from disk=''' + @fileName + ''''

SELECT @bs

INSERT INTO @tfilelist
EXEC sp_executesql @bs

DECLARE @sql NVARCHAR(1000)

SELECT *
FROM @tfilelist

DECLARE @name2 VARCHAR(1000)
    ,@m VARCHAR(300)
    ,@mp VARCHAR(1000)
    ,@l VARCHAR(1000)
    ,@lp VARCHAR(1000)

SET @m = (
        SELECT clogicalname
        FROM @tfilelist
        WHERE ctype = 'D'
        )
SET @mp = (
        SELECT cPhysicalName
        FROM @tfilelist
        WHERE ctype = 'D'
        )
SET @l = (
        SELECT clogicalname
        FROM @tfilelist
        WHERE ctype = 'L'
        )
SET @lp = (
        SELECT cPhysicalName
        FROM @tfilelist
        WHERE ctype = 'L'
        )
SET @name2 = @newdbname
SET @sql = 'RESTORE database [' + @name2 + '] from disk =''' + @fileName + ''' with ';

SELECT @sql = @sql + ' move ''' + @m + ''' to ''' + replace(@mp, @name, @name2) + ''','

SELECT @sql = @sql + ' move ''' + @l + ''' to ''' + replace(@lp, @name, @name2) + ''', stats = 1'

SELECT @sql

EXEC sp_executesql @sql;

SET @sql = 'Use [' + @name2 + ']'

SELECT @sql

EXEC sp_executesql @sql;

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

EXEC dbcc_clonedatabase @olddbname = '<SOURCE DATABASE NAME>'
    ,@newdbname = '<NEW DATABASE NAME>'

Please note if you find the source database is corrupted when y ou are about to use dbcc clonedatabase,   then you can use SQL recovery tools such as DataNumen SQL Recovery to fix it.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook email error and excel recovery software products. For more information visit www.datanumen.com