SQL Server: Copy Database Script

USE master
GO

-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'SOURCE_DB_NAME'

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:tempbackup.dat'

-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'NEW_DBNAME'

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:Program FilesMicrosoft SQL ServerMSSQLDataNEWDBFILENAME'

-- ****************************************************************
-- no change below this line
-- ****************************************************************

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'

IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END

-- RESTORE FILELISTONLY FROM DISK = 'C:tempbackup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:tempbackup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:tempbackup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:tempbackup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END

create table #restoreheader(
BackupName nvarchar(128)
, BackupDescription nvarchar(255)
, BackupType smallint
, ExpirationDate datetime
, Compressed tinyint
, Position smallint
, DeviceType tinyint
, UserName nvarchar(128)
, ServerName nvarchar(128)
, DatabaseName nvarchar(128)
, DatabaseVersion int
, DatabaseCreationDate datetime
, BackupSize numeric(20,0)
, FirstLSN numeric(25,0)
, LastLSN numeric(25,0)
, CheckpointLSN numeric(25,0)
, DatabaseBackupLSN numeric(25,0)
, BackupStartDate datetime
, BackupFinishDate datetime
, SortOrder smallint
, CodePage smallint
, UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
, SoftwareVersionMajor int
, SoftwareVersionMinor int
, SoftwareVersionBuild int
, MachineName nvarchar(128)
, Flags int
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
)

DECLARE @File int
SET @query = 'RESTORE HEADERONLY FROM DISK =' + QUOTENAME(@BackupFile , '''')
insert #restoreheader exec(@query)
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
TRUNCATE TABLE #restoreheader
DROP TABLE #restoretemp
DROP TABLE #restoreheader

select @File as [File]

IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
END
GO

Source: Create a copy of an existing SQL Server database