Restore All Backups

CREATE  PROC [dbo].[RestoreDatabaseScript] @PATH CHAR(100), @BACKUPPARH CHAR(150)
AS
BEGIN 
		SET NOCOUNT ON
		CREATE TABLE #TEMP
		(
			ID INT IDENTITY,
			LOGICAL_NAME CHAR(50),
			[FILE_PATH]CHAR(150),
			[FILE] CHAR(50),
			DATABASENAME CHAR(50),
			FILE_ID INT
		)

		INSERT INTO #TEMP
		(
			LOGICAL_NAME,
			FILE_PATH,
			[FILE],
			DATABASENAME,
			FILE_ID
		)
		SELECT 
			SYS.MASTER_FILES.NAME AS [LOGICAL_NAME],
			PHYSICAL_NAME AS [FILE_PATH],
			SUBSTRING ( PHYSICAL_NAME ,LEN(REVERSE(RIGHT(REVERSE(PHYSICAL_NAME),(LEN(PHYSICAL_NAME)-CHARINDEX('\', REVERSE(PHYSICAL_NAME),1))+1)))+1 , LEN(PHYSICAL_NAME) ),
			SYS.DATABASES.NAME,
			FILE_ID
		FROM 
			SYS.MASTER_FILES   
		INNER JOIN 
			SYS.DATABASES ON SYS.MASTER_FILES.DATABASE_ID =  SYS.DATABASES.DATABASE_ID
		WHERE 
			SYS.MASTER_FILES.DATABASE_ID > 6 AND FILE_ID IN (1,2) 
		ORDER BY 
			[LOGICAL_NAME]

		SET NOCOUNT OFF

		DECLARE @ID INT = 1
		DECLARE @COUNT INT
		SELECT  @COUNT = COUNT(*) FROM #TEMP
		WHILE(@ID < = @COUNT)
		BEGIN

		DECLARE @MDF VARCHAR(100)
		DECLARE @MDFPATH VARCHAR(100)
		DECLARE @MDFFILE VARCHAR(100)
		DECLARE @DATANASE VARCHAR(100)

		DECLARE @LDF VARCHAR(100)
		DECLARE @LDFPATH VARCHAR(100)
		DECLARE @LDFFILE VARCHAR(100)
		
		SELECT @MDF  = LTRIM(RTRIM(LOGICAL_NAME)),@MDFPATH = LTRIM(RTRIM(FILE_PATH)),@MDFFILE = [FILE],@DATANASE =  DATABASENAME FROM #TEMP  WHERE ID = @ID AND FILE_ID = 1
		SELECT @LDF  = LTRIM(RTRIM(LOGICAL_NAME)),@LDFPATH = LTRIM(RTRIM(FILE_PATH)),@LDFFILE = [FILE] FROM #TEMP WHERE ID = @ID+1  AND FILE_ID = 2
		PRINT '--Database Name = ['+RTRIM(LTRIM(@DATANASE))+']'
		PRINT '--------------------------------------------------------------------------------------------'
		PRINT 'RESTORE DATABASE ['+RTRIM(LTRIM(@DATANASE))+']'+CHAR(13)+
		'FROM DISK = '+CHAR(39)+RTRIM(LTRIM(@BACKUPPARH))+RTRIM(LTRIM(@DATANASE))+'.bak'+CHAR(39)+CHAR(13)+
		'WITH 
		MOVE '+CHAR(39)+@MDF+CHAR(39)+ ' TO '+CHAR(39)+RTRIM(LTRIM(@PATH))+RTRIM(LTRIM(@MDFFILE))+CHAR(39)+',
		MOVE'+CHAR(39)+@LDF+CHAR(39)+' TO '+CHAR(39)+RTRIM(LTRIM(@PATH))+RTRIM(LTRIM(@LDFFILE))+CHAR(39)+'
		, REPLACE'
		PRINT '--------------------------------------------------------------------------------------------'
		SET @ID=@ID+2
		END
		DROP TABLE #TEMP
END
GO