T-SQL script for copying a database

USE master;

DECLARE
        @SourceDatabaseName AS SYSNAME = '<SourceDatabaseName>',
        @TargetDatabaseName AS SYSNAME = '<NewDatabaseName>'


       
-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG      
       
DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
        msdb.dbo.backupset B
        JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')



-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)



-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
        @LogicalDataFileName as NVARCHAR(MAX),
        @LogicalLogFileName as NVARCHAR(MAX),
        @TargetDataFilePath as NVARCHAR(MAX),
        @TargetLogFilePath as NVARCHAR(MAX)
               
SELECT
        @LogicalDataFileName = name,
        @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 0            -- datafile file

SELECT
        @LogicalLogFileName = name,
        @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 1            -- log file    



-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly as a database with the same name already exists!', 20, 1) WITH LOG       
       
SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = '
'@SourceBackupFilePath''
WITH MOVE '
'@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE '
'@LogicalLogFileName'' TO ''@TargetLogFilePath'''
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

Is this useful?