Cloning a SQL Server Database

         The following script will backup and restore a database to the same server with a different name. Tested on  SQL 2012/2008. You just need to set the 4 variables at the top of the script to specify source and destination along with directories to store the backup and restored database files.

USE master;  
GO

/*USER INPUT*******************/
DECLARE @SourceDb varchar(200) = 'SourceDatabaseName'  
DECLARE @DestinationDb VARCHAR(200) = 'DestinationdatabaseName'  
DECLARE @BackupDirectory VARCHAR(200) = 'c:\SQLBackups\'  
DECLARE @UserDbDirectory VARCHAR(200) = 'c:\UserDbs\'  
/******************************/

DECLARE @LogicalFileName VARCHAR(200) = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@SourceDb)  AND type <> 1)  
DECLARE @LogicalLogFileName VARCHAR(200) = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@SourceDb)  AND type = 1)  
DECLARE @BackupFile VARCHAR(200) = @BackupDirectory + @SourceDb + '.bak'            
DECLARE @Query NVARCHAR(1000)  
SET @query = 'BACKUP DATABASE ' + @SourceDb + ' TO DISK = ' + QUOTENAME(@BackupFile,'''')  
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DestinationDb + ' FROM DISK = ' + QUOTENAME(@BackupFile,'''')  
SET @query = @query + ' WITH MOVE ' + QUOTENAME(@LogicalFileName,'''') + ' TO ' + QUOTENAME(@UserDbDirectory + @DestinationDb + '.mdf' ,'''')  
SET @query = @query + ' , MOVE ' + QUOTENAME(@LogicalLogFileName,'''') + ' TO ' + QUOTENAME(@UserDbDirectory + @DestinationDb + '_log.ldf','''')  
EXEC (@query)