Clone SQL Server database

Option 1
Detach Original Database, copy its MDF and LDF files and create a new Database from copied files.


USE master;
GO 
EXEC sp_detach_db
    @dbname = N'OriginalDB';
GO

copy "c:\\OriginalDB.mdf" "c:\\NewDB.mdf"
copy "c:\\OriginalDB_log.ldf" "c:\\NewDB_log.ldf"

USE master;
GO
CREATE DATABASE OriginalDB
    ON (FILENAME = 'C:\\OriginalDB.mdf'),
       (FILENAME = 'C:\\OriginalDB_log.ldf')
    FOR ATTACH;
GO
CREATE DATABASE NewDB
    ON (FILENAME = 'C:\\NewDB.mdf'),
       (FILENAME = 'C:\\NewDB_log.ldf')
    FOR ATTACH;
GO

Option 2
Create backup of Original database and Restore New database from backup files. Use below script to clone database.


DECLARE @backupPath nvarchar(400);
DECLARE @sourceDb nvarchar(50);
DECLARE @sourceDb_log nvarchar(50);
DECLARE @destDb nvarchar(50);
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);
DECLARE @sqlServerDbFolder nvarchar(100);

SET @sourceDb = 'OriginalDB'
SET @sourceDb_log = @sourceDb + '_log'
SET @backupPath = 'C:\\' + sourceDb + '.bak'
SET @sqlServerDbFolder = 'C:\\'
SET @destDb = 'NewDB'
SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf'

BACKUP DATABASE @sourceDb TO DISK = @backupPath

RESTORE DATABASE @destDb FROM DISK = @backupPath
WITH REPLACE,
   MOVE @sourceDb     TO @destMdf,
   MOVE @sourceDb_log TO @destLdf
Comments
  1. Posted by Rizwan Ansari

Leave a Reply

Your email address will not be published.