exec NEW_DB 'Prod_DB', 'Test_DB','user_abc'
GO

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[NEW_DB]    Script Date: 18.03.2019 9:26:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NEW_DB] 

	@dbname nvarchar(30),
    @newdbname nvarchar(30),
	@user nvarchar(20) 
	
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @sql nvarchar(2000)
	DECLARE @full_bkp_path nvarchar(250)
	DECLARE @diff_bkp_path nvarchar(250)
	DECLARE @full_date datetime
	DECLARE @diff_date datetime
	DECLARE @diff_sql nvarchar(250)

	declare @LnameData nvarchar(100)
	declare @LnameLog nvarchar(100)
	
	set @LnameData = 
(select 
f.name as logical_name
from [prod-serv].[master].[sys].[master_files] f
 inner join [prod-serv].[master].[sys].[databases] d
 on f.[database_id] =
 d.[database_id] 
 where d.name  = @dbname 
 and f.type_desc = 'ROWS'
 )
  
 set @LnameLog = 
(select 
f.name as logical_name
from [prod-serv].[master].[sys].[master_files] f
 inner join [prod-serv].[master].[sys].[databases] d
 on f.[database_id] =
 d.[database_id] 
 where d.name  = @dbname 
 and f.type_desc = 'LOG'
 )

	SELECT @full_bkp_path = physical_device_name, @full_date = backup_start_date
	FROM backups.dbo.backups
	WHERE type = 'D' and database_name = @dbname

	SELECT @diff_bkp_path = physical_device_name, @diff_date = backup_start_date
	FROM backups.dbo.backups
	WHERE type = 'I' AND database_name = @dbname

	IF (@diff_date < @full_date) SET @diff_sql = ''
	ELSE SET @diff_sql = 'RESTORE DATABASE ' + @newdbname + ' FROM DISK = N''' + @diff_bkp_path + ''' WITH FILE = 1, NOUNLOAD, NORECOVERY, STATS = 5;
'

IF Exists (select name from sys.databases where name = @newdbname)
Begin
	SET @sql = 'USE [master];
ALTER DATABASE ' + @newdbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
exec sp_executesql @sql
END

BEGIN	
	SET @sql =
'USE [master];
RESTORE DATABASE ' + @newdbname + ' FROM DISK = N''' + @full_bkp_path + ''' WITH FILE = 1,
MOVE N''' + @LnameData + ''' TO N''C:\SQL\' + @newdbname + '.mdf'', MOVE N''' + @LnameLog + ''' TO N''C:\SQL\' + @newdbname + '_log.ldf'', 
NOUNLOAD, NORECOVERY,  REPLACE,  STATS = 5;
'
+ @diff_sql + 
'
RESTORE DATABASE ' + @newdbname + ' WITH RECOVERY;'
exec sp_executesql @sql

END

SET @sql =
'USE [master]; ALTER DATABASE ' + @newdbname + ' SET MULTI_USER;'
exec sp_executesql @sql

SET @sql =

'USE ' + @newdbname + ';

DROP USER [' +  @user + '];

CREATE USER [' + @user + '] FOR LOGIN [' + @user + '];

USE ' + @newdbname + ';

ALTER ROLE [db_owner] ADD MEMBER [' + @user + '];

USE [master];

ALTER DATABASE ' + @newdbname + ' SET RECOVERY SIMPLE WITH NO_WAIT;
'
exec sp_executesql @sql

END

USE [master];
RESTORE DATABASE test_res FROM DISK = N'\\serv-bk-01\Backup\MSSQL\Full\Prod_db\Prod_db_backup_2019_03_16_010001_6905791.bak' WITH FILE = 1,
MOVE N'MIS' TO N'C:\SQL\test_res.mdf', MOVE N'MIS_log' TO N'C:\SQL\test_res_log.ldf', 
NOUNLOAD, NORECOVERY,  REPLACE,  STATS = 5;
RESTORE DATABASE test_res FROM DISK = N'\\serv-bk-01\Backup\MSSQL\Diff\Prod_db\Prod_db_backup_2019_03_18_010001_4636191.bak' WITH FILE = 1, NOUNLOAD, NORECOVERY, STATS = 20;

RESTORE DATABASE test_res WITH RECOVERY;
USE [master]; ALTER DATABASE test_res SET MULTI_USER;
USE test_res;

DROP USER [userabc];

CREATE USER [userabc] FOR LOGIN [userabc];

USE test_res;

ALTER ROLE [db_owner] ADD MEMBER [userabc];

USE [master];

ALTER DATABASE test_res SET RECOVERY SIMPLE WITH NO_WAIT;