Build restore scripts dynamically

/**************************************************************************************************//***                      Build Restore Scripts Dynamically                                     ***//***                                                                                            ***//***   Save this script on each server to be restored, and open in Management Studio            ***//***   Set the @BackupPath variable to the parent folder where the server's backup files exist  ***//***   Run the script, and copy the output results to a new query window                        ***//***   Run entire script at once, or restore each database individually                         ***//***                                                                                            ***//***   Script created by Brian Smith - April 2, 2015                                            ***//***                                                                                            ***//**************************************************************************************************/
SET NOCOUNT ON

DECLARE @BackupPath varchar(500); SET @BackupPath = '' -- e.g. '\\{SERVER}\{Share}\{ParentFolder}\'
DECLARE @DB varchar(500)
DECLARE @AllFilesTable TABLE (
subdirectory varchar(500),
depth int,
isfile bit
)
DECLARE @BackupsTable TABLE (
BackupFile varchar(500)
)
DECLARE @BakFile varchar(500)
DECLARE @TrnFile varchar(500)
DECLARE @RestoreScript varchar(8000)

-- Start building the restore script
SET @RestoreScript = 'USE [master];
'

-- Capture the first database
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE'

-- Begin the loop
WHILE @DB IS NOT NULL
BEGIN

-- Capture the list of backup files
INSERT INTO @AllFilesTable (subdirectory, depth, isfile)
EXEC ('xp_dirtree ''' + @BackupPath + '' + @DB + ''', 1, 1')
INSERT INTO @BackupsTable (BackupFile)
SELECT subdirectory FROM @AllFilesTable WHERE RIGHT(subdirectory,29) >= RIGHT((SELECT max(subdirectory) FROM @AllFilesTable WHERE RIGHT(subdirectory,4) = '.bak'),29)

-- Continue building the script lines
SET @RestoreScript = @RestoreScript + 'ALTER DATABASE [' + @DB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
'
-- Add the full backup restore script line
SELECT @BakFile = BackupFile FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.bak'
SET @RestoreScript = @RestoreScript + 'RESTORE DATABASE [' + @DB + '] FROM  DISK = N''' + @BackupPath + @DB + '\' + @BakFile + ''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
'
DELETE @BackupsTable WHERE BackupFile = @BakFile

-- Add the transaction log backup restore script lines (one for each .trn file)
SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'
WHILE @TrnFile IS NOT NULL
BEGIN

SET @RestoreScript = @RestoreScript + 'RESTORE LOG [' + @DB + '] FROM DISK = N''' + @BackupPath + @DB + '\' + @TrnFile + '''  WITH NORECOVERY;
'
DELETE @BackupsTable WHERE BackupFile = @TrnFile
SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'

END

-- Add the script line to bring the database back online
SET @RestoreScript = @RestoreScript + 'RESTORE DATABASE [' + @DB + '] WITH RECOVERY;
GO
'

PRINT @RestoreScript
SET @RestoreScript = ''

-- Clear the temp tables for the next capture
DELETE @AllFilesTable
-- Cycle through the databases one at a time
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' AND name > @DB

END