Database Copy Paste

This  T-SQL script is like copy A database paste to another database B in SQL Server .

My SQL Server version is 2014, haven’t run another version.

/*declare*/
DECLARE @path varchar(100)='E:\SQL_DATA\BACKUP\';
DECLARE @backfile varchar(100)=@path+'SourceDB.baktmp';

/*backup*/
BACKUP DATABASE [SourceDB] TO  DISK = @backfile WITH NOFORMAT
, NOINIT
,  NAME = N'SourceDB-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
;

/*import*/
ALTER DATABASE [TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TargetDB] FROM  DISK = @backfile WITH  FILE = 1
,  MOVE N'TargetDB' TO N'E:\SQL_DATA\TargetDB.mdf'
,  MOVE N'TargetDB_log' TO N'E:\SQL_DATA\TargetDB_log.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [TargetDB] SET MULTI_USER
;

/*delete backup file*/
DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day, +1, GETDATE()), 111);
EXECUTE master.dbo.xp_delete_file 0,@path,N'baktmp',@deldate;

You need modify these :

1. Modify @path
2. Replace “SourceDB” to your source database name
3. Replace “TargetDB” to your target database name

Maybe change  to a stored procedure will be easier.

Create following script in master db.

 

USE [master]
GO
/*--------------------------------------------------
description: Copy database A to database B
author: Robin
date: 2016/02/20
testing Code:
--------------------------------------------------
EXEC Database_Copy_Paste 'DATABASE_A','DATABASE_B'
--------------------------------------------------*/
ALTER Procedure [dbo].[Database_Copy_Paste]
	@SourceDB nvarchar(32)
	, @TargetDB nvarchar(32)
AS
BEGIN
/*set login files name*/
DECLARE @DBRowsFile varchar(32), @DBLogFile varchar(32);
SET @DBRowsFile='DefaultDBRows';
SET @DBLogFile='DefaultDBLog';

/*check logic file are the same*/
DECLARE @SQLString nvarchar(1024), @ParmDefinition nvarchar(512);
SET @SQLString=
'IF exists(
	select top 1 name from (
		select ''[@SourceDB]'' as db, name from [@SourceDB].sys.database_files
		union all
		select ''[@TargetDB]'', name from [@TargetDB].sys.database_files
	) t
	where t.name not in (
		select @DBRowsFile as name
		union
		select @DBLogFile
	))
BEGIN
	select ''ALTER DATABASE ''+t.db+'' MODIFY FILE (NAME=''+t.name+'', NEWNAME=''+s.name+'');'' as [Have to excute]
	from (
		select ''[@SourceDB]'' as db, name, type_desc from [@SourceDB].sys.database_files
		union
		select ''[@TargetDB]'', name, type_desc from [@TargetDB].sys.database_files
	)t
	left outer join (
		select @DBRowsFile as name, ''ROWS'' as type_desc
		union
		select @DBLogFile, ''LOG''
	) s on t.type_desc=s.type_desc
	where t.name<>s.name
END';
SET @SQLString = REPLACE(@SQLString, '[@SourceDB]', @SourceDB);
SET @SQLString = REPLACE(@SQLString, '[@TargetDB]', @TargetDB);
SET @ParmDefinition='@DBRowsFile varchar(32), @DBLogFile varchar(32)';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @DBRowsFile=@DBRowsFile, @DBLogFile=@DBLogFile;

IF @@ROWCOUNT>0
BEGIN
	PRINT 'Have to change logic file name first...';
	RETURN;
END;


/*declare database parms */
DECLARE @BackupPath nvarchar(128), @BackupFile nvarchar(128);
SET @BackupPath = 'E:\SQL_DATA\BACKUP\';
SET @BackupFile = @BackupPath+@SourceDB+'.baktmp';

SET @SQLString=
'/*backup*/
BACKUP DATABASE [@SourceDB] TO DISK = @BackupFile WITH NOFORMAT
, NOINIT
,  NAME = N''[@SourceDB]-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10 ;

/*import*/
DECLARE @TargetDBFile nvarchar(128), @TargetDBLogFile nvarchar(128);
select @TargetDBFile = physical_name    from [@TargetDB].sys.database_files where type_desc=''ROWS'';
select @TargetDBLogFile = physical_name from [@TargetDB].sys.database_files where type_desc=''LOG'';

ALTER DATABASE [@TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [@TargetDB] FROM  DISK = @BackupFile WITH  FILE = 1
,  MOVE @DBRowsFile TO @TargetDBFile
,  MOVE @DBLogFile TO @TargetDBLogFile
,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [@TargetDB] SET MULTI_USER ;

/*delete backup file*/
DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day, +1, GETDATE()), 111);
EXECUTE master.dbo.xp_delete_file 0,@BackupPath,N''baktmp'',@deldate ;
';

SET @SQLString = REPLACE(@SQLString, '[@SourceDB]', @SourceDB);
SET @SQLString = REPLACE(@SQLString, '[@TargetDB]', @TargetDB);

SET @ParmDefinition=
'@SourceDB nvarchar(32), @TargetDB nvarchar(32)
, @BackupPath nvarchar(128), @BackupFile nvarchar(128)
, @DBRowsFile varchar(32), @DBLogFile varchar(32)';

--print @ParmDefinition
--print @SQLString

EXECUTE sp_executesql @SQLString, @ParmDefinition
	, @SourceDB=@SourceDB
	, @TargetDB=@TargetDB
	, @BackupPath=@BackupPath
	, @BackupFile=@BackupFile
	, @DBRowsFile=@DBRowsFile
	, @DBLogFile=@DBLogFile;

END
GO

Then call stored procedure:

USE [master]
EXEC Database_Copy_Paste 'DATABASE_A','DATABASE_B'

^_^