Stored Proc that generates RESTORE scripts from a set of backups created by Ola's Hallengren maintenance scripts.

sp_help_dbrecovery.sql

-- This stored proc was developed based on the backup naming and folder convention from
-- https://ola.hallengren.com/ Maintenance Solution (as of 28 September, 2016).
-- It solely relies on files in the folder, it does not query MSDB, since it is 
-- designed to work with Availability Groups (which may not have one single source of backup history).

-- Give it a database name and a backup root folder that you specified in Ola's Maintenance Solution script
-- and it will produce a sequence of RESTORE commands:
--   * Starting from MOST RECENT full backup,
--   * Then MOST RECENT diff backup (if exists)
--   * Then all T-Log backups after the last FULL/DIFF backup.

-- Optional parameter @stopAt datetime can be used to generate RESTORE scripts
-- up to a last backup before @stopAt time. PLEASE NOTE that in this version 
-- STOPAT option for t-log restore is not used. The procedure just generates 
-- the recovery scripts based on the existing backup files until time specified in @stopAt.

-- The MIT License (MIT)
-- Copyright (c) 2016 Vadim Kovalyov
-- https://opensource.org/licenses/MIT

USE [master] 
GO  

IF (OBJECT_ID('[dbo].[sp_help_dbrecovery]', 'P') IS NOT NULL)
	DROP PROCEDURE [dbo].[sp_help_dbrecovery]
GO

CREATE PROCEDURE [dbo].[sp_help_dbrecovery]
	@dbName sysname,
	@bakupBasePath NVARCHAR(MAX),
	@stopAt datetime = null
AS
BEGIN
	SET NOCOUNT ON

	--------------------------------------------------------------------------------------------------------------------------
	-- First part of the proc creates a table @BackupFiles with all backup files from @bakupBasePath directory (recursively).
	--------------------------------------------------------------------------------------------------------------------------

	DECLARE @dir SYSNAME = @bakupBasePath

	DECLARE @DirTree TABLE  (
		Id int identity(1,1),
		SubDirectory nvarchar(255),
		Depth smallint,
		FileFlag bit,
		ParentDirectoryID int
	)

	INSERT INTO @DirTree (SubDirectory, Depth, FileFlag)
	EXEC master..xp_dirtree @dir, 10, 1

	UPDATE @DirTree
	SET ParentDirectoryID = (
		SELECT MAX(Id) FROM @DirTree d2
		WHERE Depth = d.Depth - 1 AND d2.Id < d.Id
		)
	FROM @DirTree d

	DECLARE 
		@ID INT,
		@BackupFile NVARCHAR(MAX),
		@Depth TINYINT,
		@FileFlag BIT,
		@ParentDirectoryID INT,
		@wkSubParentDirectoryID INT,
		@wkSubDirectory NVARCHAR(MAX)

	DECLARE @BackupFiles TABLE(FileNamePath NVARCHAR(MAX))

	DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY 
	FOR
	SELECT * FROM @DirTree WHERE FileFlag = 1

	OPEN FileCursor
	FETCH NEXT FROM FileCursor INTO 
		@ID,
		@BackupFile,
		@Depth,
		@FileFlag,
		@ParentDirectoryID  

	SET @wkSubParentDirectoryID = @ParentDirectoryID

	WHILE @@FETCH_STATUS = 0
	BEGIN
	--loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop
	WHILE @wkSubParentDirectoryID IS NOT NULL
	BEGIN
		SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID 
		FROM @DirTree 
		WHERE ID = @wkSubParentDirectoryID

		SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile
	END

	--no more subfolders in loop so now prefix the root backup folder
	SELECT @BackupFile = @dir + @BackupFile

	INSERT INTO @BackupFiles (FileNamePath) VALUES(@BackupFile)

	FETCH NEXT FROM FileCursor INTO 
		@ID,
		@BackupFile,
		@Depth,
		@FileFlag,
		@ParentDirectoryID 

	SET @wkSubParentDirectoryID = @ParentDirectoryID      
	END

	CLOSE FileCursor
	DEALLOCATE FileCursor

	-------------------------------------------------------------
	-- Second part of the script generates the RESTORE commands.
	-------------------------------------------------------------

	DECLARE @cmd NVARCHAR(1024)
	DECLARE @fileList TABLE (backupFile NVARCHAR(MAX), backupTime NVARCHAR(255)) 
	DECLARE @lastFullBackup NVARCHAR(MAX) 
	DECLARE @lastDiffBackup NVARCHAR(MAX) 

	DECLARE @lastFullBackupTime NVARCHAR(255) 
	DECLARE @lastDiffBackupTime NVARCHAR(255) 


	INSERT INTO @fileList(backupFile) 
	SELECT FileNamePath FROM @BackupFiles
	
	-- According to Ola Hallengren naming convention, extract datetime from the name.
	UPDATE  @fileList SET backupTime = LEFT(RIGHT(backupFile, 19), 15)

	 --This is the same formula as used in Ola's Solution
	SET @stopAt = ISNULL(@stopAt, GETDATE())
	DECLARE @StopAtTime NVARCHAR(255) = REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@stopAt,120)),'-',''),' ','_'),':','')

	PRINT '----------------------START COPY------------------------'

	-- Find latest full backup 
	SELECT TOP 1 @lastFullBackup = backupFile, @lastFullBackupTime = backupTime
	FROM @fileList  
	WHERE backupFile LIKE '%' + @dbName + '_FULL_%.BAK'  
		AND backupTime < @StopAtTime
	ORDER BY backupTime DESC 

	SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
		   + @lastFullBackup + ''' WITH NORECOVERY --, REPLACE --uncomment this to override the database' 
	PRINT @cmd 

	-- Find latest diff backup 
	SELECT TOP 1 @lastDiffBackup = backupFile, @lastDiffBackupTime = backupTime
	FROM @fileList  
	WHERE backupFile LIKE '%' + @dbName + '_DIFF_%.BAK'  
	   AND backupTime > @lastFullBackupTime 
	   AND backupTime < @StopAtTime
	ORDER BY backupTime DESC 

	-- Check to make sure there is a diff backup 
	IF @lastDiffBackup IS NOT NULL 
	BEGIN 
	   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
		   + @lastDiffBackup + ''' WITH NORECOVERY' 
	   PRINT @cmd 
	   SET @lastFullBackup = @lastDiffBackup 
	   SET @lastFullBackupTime = @lastDiffBackupTime
	END 

	-- Check for log backups 
	DECLARE backupFiles CURSOR FOR  
	   SELECT backupFile  
	   FROM @fileList 
	   WHERE backupFile LIKE '%' + @dbName + '_LOG_%.TRN'  
		   AND backupTime > @lastFullBackupTime 
		   AND backupTime < @StopAtTime
		ORDER BY backupTime

	OPEN backupFiles  

	-- Loop through all the files for the database  
	FETCH NEXT FROM backupFiles INTO @backupFile  

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
	   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''  
		   + @backupFile + ''' WITH NORECOVERY' 
	   PRINT @cmd 
	   FETCH NEXT FROM backupFiles INTO @backupFile  
	END 

	CLOSE backupFiles  
	DEALLOCATE backupFiles  

	-- Put database in a useable state 
	SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
	PRINT @cmd 

	PRINT '----------------------END COPY--------------------------'

	--SELECT * FROM @fileList
	--ORDER BY backupTime
		
END