SQL Cloning Database

dbbackup.bat

sqlcmd -S SERVERNAME\SQLEXPRESS -E -i backupdb.sql

-------------------------------------------------------------------------------------------

backupdb.sql

 

declare     @backupDB nvarchar(128),

    @restoreDB nvarchar(128),

    @backupFolder nvarchar(512),

    @dataFolder nvarchar(512);

set @backupDB = 'SampleDB';

set @restoreDB = 'SampleDBX';

set @backupFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\';

set @dataFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\';

declare @diskName nvarchar(512);

set @diskName = @backupDB + '-' +REPLACE(REPLACE(CONVERT(VARCHAR(128),GETDATE(),20),' ','-'),':','-') + '.bak';

set @diskName = @backupFolder + @diskName;

backup database @backupDB to disk = @diskName

declare @sql nvarchar(max)

set @sql = 'alter database ' + @restoreDB + ' set single_user with rollback immediate';

exec(@sql);

set @sql = 'restore database ' + @restoreDB + ' from disk = N'''+ @diskName +''''

+ ' with file = 1, '

+ ' move N'''+@backupDB+''' to N''' + @dataFolder + @restoreDB + '.mdf'', '

+ ' move N'''+@backupDB+'_log'' to N''' + @dataFolder + @restoreDB + '_log.ldf'', '

+ ' KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 5'

exec(@sql);

set @sql = 'alter database ' + @restoreDB + ' set MULTI_USER';

exec(@sql);