8 Şubat 2023

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);