SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_sys_database_duplicate]
	@new_db_name sysname,
	@exec_md_create bit
as

	set nocount on
	
	declare @backup_path		nvarchar(4000),
			@backup_name		nvarchar(4000),
			@db_name			sysname,

			@logical_data		sysname, 
			@logical_log		sysname,
			@physical_data		sysname,
			@physical_log		sysname,
			@new_physical_data	sysname,
			@new_physical_log	sysname


	-- déterminer le chemin pour les sauvegardes
	EXEC master.dbo.xp_instance_regread 
			N'HKEY_LOCAL_MACHINE', 
			N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', 
			@backup_path OUTPUT,  
			'no_output' 



	select	@db_name = DB_NAME(),
			@backup_name = @db_name + '_copy.BAK',
			@backup_path = @backup_path + '\' + @backup_name
			

	-- *_test => crystal_all_test
	if CHARINDEX('*', @new_db_name, 1) > 0
	begin
		select @new_db_name =  replace(@new_db_name, '*', @db_name)
	end


	SELECT	@logical_data = mf.name, 
			@physical_data = mf.physical_name
	FROM	sys.master_files mf
			INNER JOIN sys.databases db ON db.database_id = mf.database_id
	where	db.name = @db_name
	and		mf.type_desc = 'ROWS'

	SELECT	@logical_log = mf.name, 
			@physical_log = mf.physical_name
	FROM	sys.master_files mf
			INNER JOIN sys.databases db ON db.database_id = mf.database_id
	where	db.name = @db_name
	and		mf.type_desc = 'LOG'


	SELECT	@new_physical_data = REVERSE(SUBSTRING(REVERSE(@physical_data), CHARINDEX('\', REVERSE(@physical_data)) , LEN(@physical_data))) + @new_db_name + '.mdf',
			@new_physical_log = REVERSE(SUBSTRING(REVERSE(@physical_log), CHARINDEX('\', REVERSE(@physical_log)) , LEN(@physical_log))) + @new_db_name + '_log.ldf'
	

	-- vérifier que la nlle bdd n'existe pas 
	if exists (select 1 from master.dbo.sysdatabases where name = @new_db_name)
	begin
		RAISERROR('The database %s already exists', 16, 1, @new_db_name)
		return
	end


	BACKUP DATABASE @db_name
	TO DISK = @backup_path
	WITH FORMAT, NAME = @db_name

	print 'New Database: ' + @new_db_name
	print @physical_data + ' -> ' + @new_physical_data
	print @physical_log + ' -> ' + @new_physical_log


	RESTORE DATABASE @new_db_name
	FROM	DISK = @backup_path
	WITH
			MOVE @logical_data to @new_physical_data,
			MOVE @logical_log to @new_physical_log

	-- c'est fait par p_sys_md_create

	if @exec_md_create != 0
	begin 
		declare @sql varchar(4000)

		select @sql = 'use ' + @new_db_name + ' exec p_sys_md_create'

		EXECUTE( @sql)
	end
	else
	begin
		exec p_sys_database_objects_create @new_db_name
	end
	
GO
