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
Leave a Reply