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
Run below statement if Database stuck in recovery mode. RESTORE DATABASE NewDB WITH RECOVERY