Gavin Draper's Blog

Father, Developer, Tech Geek, Extreme Sports Fanatic

@gavdraper | +gavdraper | github | StackOverflow Careers
 

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) = 'SourceDatabase'  
DECLARE @DestinationDb VARCHAR(200) = 'DestinationDatabase'  
DECLARE @BackupDirectory = 'C:\SQLBackups\'  
DECLARE @UserDbDirectory = '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 + '.dat'            

DECLARE @Query NVARCHAR(1000)  
SET @query = 'BACKUP DATABASE ' + @SourceDb + ' TO DISK = ' + QUOTENAME(@BackupFile,'''')  
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @UserDbDirectory + ' 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)  

 
comments powered by Disqus