Como usar DBCC CLONEDATABASE em SQL Server Mais antigo que 2014 SP2

O comando DBCC CloneDatabase está disponível apenas em SQL Server 2014 SP2; não está disponível em versões anteriores. No entanto, se você quiser usar isso em versões anteriores a 2014, poderá usar este script.

Com 2014 SP2

Sempre que houver um problema de desempenho, você precisará de uma cópia do banco de dados de produção. No entanto, não é necessário que a cópia que você criar esteja com dados para investigar o problema de desempenho. Portanto, a Microsoft introduziu o DBCC CLONEDATABASE no service pack 2 para SQL SERVER 2014. Este comando DBCC ajudará os usuários a criar cópias do banco de dados de produção apenas com esquema e estatísticas. Observe que este comando funcionará apenas com bancos de dados do usuário e não pode ser usado com bancos de dados do sistema.

Antes de 2014 SP2

Execute o script abaixo em seu SQL server que é mais antigo que o SQL 2014 SP2. Este script criará um procedimento dbcc_clonedatabase. Você pode então executar este procedimento passando o nome do banco de dados de origem e o novo nome do banco de dados como parâmetros.

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 TABELA ?  NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'EXCLUIR DE?'
 EXEC sp_MSForEachTable 'ALTER TABELA ?  CHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON?'

Observe que, se você achar que o banco de dados de origem está corrompido quando estiver prestes a usar o dbcc clonedatabase, poderá usar Ferramentas de recuperação SQL tais como DataNumen SQL Recovery para fixar isso.

Introdução do autor:

Neil Varley é um especialista em recuperação de dados em DataNumen, Inc., líder mundial em tecnologias de recuperação de dados, incluindo reparar erro de e-mail do Outlook e produtos de software de recuperação do Excel. Para mais informações visite www.datanumen.com