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