USE [Master]
GO

/****** Object:  StoredProcedure [dbo].[AlarmAuditTrail_Delete]    Script Date: 09/28/2018 18:16:03 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Restore]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Restore]
GO
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Restore]    Script Date: 21-04-2022 15:33:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Execute [dbo].[Restore] 'ICDAS_3_0_History','D:\E_Drive\ICDAS3_0Backup\HistoryBackup\ICDAS_3_0.bak',0,''
create PROCEDURE [dbo].[Restore]
	@DBName as nvarchar(50)='',
	@DBPath as nvarchar(500) = '',
	@ReturnId as int Output,
	@ReturnMsg as nvarchar(500) Output
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
BEGIN
	begin try
		Set @ReturnId = 1

	Declare @physical_name as nvarchar(1000),@strquery as nvarchar(max)
		begin try
			Set @strquery='IF EXISTS(SELECT name FROM sys.databases WHERE name = N''' + @DBName + ''') DROP DATABASE ' + @DBName
			exec(@strquery)
		end try
		begin catch
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':DATABASE DROP ERROR'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			Return 
		end catch
		
		begin try
			Set @strquery='Create Database ' + @DBName
			exec(@strquery)
		end try		
		begin catch 
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':DATABASE CREATE ERROR'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			return 
		end catch
		
		
		
		begin try
			SELECT @physical_name= physical_name FROM sys.database_files

			RESTORE FILELISTONLY
			FROM DISK = @DBPath
		end try
		begin catch
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':SELECT PHYSICAL NAME'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			Return
		end catch
		
		begin try
			set @strquery= 'ALTER DATABASE ' + @DBName +
			' SET SINGLE_USER WITH
			ROLLBACK IMMEDIATE'
		exec(@strquery)
		end try
		begin catch
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':SETTING SINGLE USER ERROR'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			Return
		end catch
		
		
		begin try
			set @strquery='RESTORE DATABASE ' + @DBName + ' 
			FROM DISK = ''' + @DBPath + '''
			With REPLACE,
			MOVE ''ICDAS_3_0'' TO ''' + REPLACE(@physical_name,'mastlog.ldf', @DBName + '.mdf') + ''',
			MOVE ''ICDAS_3_0_Log'' TO ''' + REPLACE(@physical_name,'mastlog.ldf', @DBName + '_log.ldf') + ''''
			exec(@strquery)
		end try
		begin catch
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':RESTORE DATABASE ERROR AND PHYSICAL FILE NAME CHANGES'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			Return
		end catch
		
		if @DBName <> 'ICDAS_3_0'
		Begin
			begin try
				Set @strquery = 'ALTER DATABASE ' + @DBName + ' MODIFY FILE (NAME=N''ICDAS_3_0'', NEWNAME=N''' + @DBName + ''')'
				exec(@strquery)
			end try
			begin catch
				Set @ReturnId = 0
				Set @ReturnMsg = @DBName + ':LOGICAL FILE NAME CHANGE FOR MDF FILE'
				Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
				Return
			end catch
		end
		
		if @DBName <> 'ICDAS_3_0'
		Begin
			begin try
				Set @strquery = 'ALTER DATABASE ' + @DBName + ' MODIFY FILE (NAME=N''ICDAS_3_0_log'', NEWNAME=N'''+ @DBName + '_log'')'
				exec(@strquery)
			end try
			begin catch
				Set @ReturnId = 0
				Set @ReturnMsg = @DBName + ':LOGICAL FILE NAME CHANGE FOR LDF FILE'
				Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
				Return
			end catch
		end
		
		begin try
			Set @strquery = ' ALTER DATABASE ' + @DBName + ' SET MULTI_USER'
			exec(@strquery)
		end try
		begin catch
			Set @ReturnId = 0
			Set @ReturnMsg = @DBName + ':SETTING MULTIUSER ERROR'
			Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
			Return
		end catch
		
		set @ReturnMsg = @DBName + ':RESTORATION SUCCESSFULL'
		Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
		Return
	end try
	begin catch
		Set @ReturnId = 0
		Set @ReturnMsg = @DBName + ':MAIN ERROR'
		Select @ReturnId As ReturnId ,@ReturnMsg as ReturnMsg
		Return
	end catch

END
SET NOCOUNT OFF

Go