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