Restore Backup and all TRANLOGS in a specific DIR

SET NOCOUNT ON
DECLARE @BACKUPDIR AS NVARCHAR(255) -- THE DIRECTORY WHERE THE ACKUPS ARE PLACED
DECLARE @RESTORESET AS INTEGER-- THE BACKUPSET YOU WANT TO RESTORE
DECLARE @NEWDBNAME AS VARCHAR(200)-- THE NEW DATABASE NAME
DECLARE @ORIGDBNAME AS VARCHAR(200) -- THE ORIGINAL DATABASE NAME
DECLARE @ORIGLGNAME AS VARCHAR(200) -- THE ORIGINAL LOG FILE NAME
DECLARE @NEWDEST AS VARCHAR(255)-- THE NEW DESTINATION DIRECTORY

SET @BACKUPDIR  = 'F:\MSSQL\MSSQL\BACKUP\NORTHWIND\'
SET @RESTORESET = 2-- RESTORE THE SECOND BACKUPSET FOUND IN THE DIRECTORY
SET @NEWDBNAME  = 'NORTHWIND'
SET @ORIGDBNAME = 'NORTHWIND'
SET @ORIGLGNAME = 'NORTHWIND_LOG'
SET @NEWDEST    = 'F:\MSSQL\MSSQL\DATA\'

DECLARE @DB_FILENAME_PRFX AS VARCHAR(200)
DECLARE @LG_FILENAME_PRFX AS VARCHAR(200)

CREATE TABLE #DIRECTORYTABLE 
( ID INT IDENTITY,
  SUBDIRECTORY SYSNAME,
  DEPTH INTEGER,
  [FILE] INTEGER,
  SORTNAME VARCHAR(200),
  SORTTIMESTR VARCHAR(30),
  SORTTIME DATETIME)

-- GET THE DIRECTORY INFORMATION
INSERT #DIRECTORYTABLE (SUBDIRECTORY,DEPTH,[FILE]) 
EXECUTE MASTER.DBO.XP_DIRTREE @BACKUPDIR, 1, 1

-- GET THE FILE NAMES
SELECT TOP 1 @DB_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM #DIRECTORYTABLE 
WHERESUBDIRECTORY LIKE '%.BAK'
SELECT TOP 1 @LG_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM #DIRECTORYTABLE 
WHERESUBDIRECTORY LIKE '%.TRN'


-- GET THE TIMESTRING COINTAINED IN THE FILENAME AND UPDATE THE TABLE
UPDATE #DIRECTORYTABLE 
SET SORTNAME = @DB_FILENAME_PRFX ,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@DB_FILENAME_PRFX,''),'.BAK','')
WHERE SUBDIRECTORY LIKE @DB_FILENAME_PRFX+'%' 

UPDATE #DIRECTORYTABLE 
SET SORTNAME = @LG_FILENAME_PRFX,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@LG_FILENAME_PRFX,''),'.TRN','')
WHERE SUBDIRECTORY LIKE @LG_FILENAME_PRFX+'%' 

-- NOW CREATE A MEMORYTABLE AND FORMAT THE DATA AS NEEDED
DECLARE @MEMDIR TABLE (ID INT IDENTITY,[FILENAME] VARCHAR(200),SORTNAME VARCHAR(200),SORTTIMESTR CHAR(12),BACKUPSET INTEGER)

-- INSERT THE NEEDED VALUES IN THE CORRECT ORDER
INSERT @MEMDIR ([FILENAME],SORTNAME,SORTTIMESTR) SELECT SUBDIRECTORY,SORTNAME,SORTTIMESTR FROM #DIRECTORYTABLE ORDER BY SORTTIMESTR

-- DROP THE TEMP TABLE
DROP TABLE #DIRECTORYTABLE

-- DECLARE SOME OTHER NEEDED VARIABLES
DECLARE @NAME VARCHAR(200)
DECLARE @CURRENT INTEGER
DECLARE @MAX INTEGER
DECLARE @BACKUPSET INTEGER
DECLARE @CURRENTSET INTEGER
DECLARE @BCKFNAME VARCHAR(200)
DECLARE @MAXID INTEGER


-- ENUMERATE THE BACKUP SET AND DETERMINE HOW MANY SETS THERE ARE AND NUMBER THEM
SET @CURRENT = 1
SET @BACKUPSET = 0
SELECT @MAX = MAX(ID) FROM @MEMDIR
WHILE @CURRENT <= @MAX
BEGIN
SELECT @NAME=SORTNAME FROM @MEMDIR WHERE ID = @CURRENT
IF @NAME = @DB_FILENAME_PRFX
   BEGIN
      SET @BACKUPSET = @BACKUPSET + 1
   END
UPDATE @MEMDIR SET BACKUPSET = @BACKUPSET WHERE ID = @CURRENT
SET @CURRENT = @CURRENT + 1
END


-- OK WE KNOW WHICH BACKUP TO RESTORE ENUMERATE THE BACKUPS AGAIN AND OUTPUT THE RESTORE STATEMENTS
SELECT @MAXID = MAX(ID) FROM @MEMDIR WHERE BACKUPSET = @RESTORESET
SET @CURRENT = 1
WHILE @CURRENT <= @MAX
BEGIN
SELECT @NAME=SORTNAME,@CURRENTSET=BACKUPSET,@BCKFNAME= [FILENAME] FROM @MEMDIR WHERE ID = @CURRENT
IF @CURRENTSET = @RESTORESET
BEGIN
IF @NAME = @DB_FILENAME_PRFX 
BEGIN
PRINT 'RESTORE DATABASE [' + @NEWDBNAME + ']'
PRINT 'FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
PRINT 'WITH MOVE ''' + @ORIGDBNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '.MDF'','
PRINT '     MOVE ''' + @ORIGLGNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '_LOG.LDF'','
IF @CURRENT < @MAXID PRINT '     NORECOVERY' 
IF @CURRENT = @MAXID PRINT '     RECOVERY' 
END
ELSE
BEGIN
PRINT 'RESTORE LOG [' + @NEWDBNAME + ']'
PRINT '     FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
IF @CURRENT < @MAXID PRINT '     WITH NORECOVERY' 
IF @CURRENT = @MAXID PRINT '     WITH RECOVERY -- STOPAT = ''YYYY-MM-DD UU:MM:SS.000'''
END
END
SET @CURRENT = @CURRENT + 1
END