sp_DatabaseRestore - StopAt Ignored on Non-Striped Backups

[sebmr2]

Version 8.14 - 20230420

The deletes from @FileList starting at line 665 do not delete files that are unstriped, at least on accessing unmapped NFS shares. It appears this issue was introduced 8.11 when fixing #3190

This command grabs the most recent full backups, and subsequent log backups instead of the appropriately timed full for the StopAt value

EXEC dbadmin.dbo.sp_DatabaseRestore @Database = 'AdventureWorks',
                            @RestoreDatabaseName = 'AdventureWorks_Again',
                            @BackupPathFull = '\\NFS\AdventureWorks\Full\',
                            @StopAt = '202304071400000', --Date is before last full backup
                            @BackupPathLog = '\\NFS\AdventureWorks\\Log\', @Execute = 'N';

I took the previous command from before issue #3190 and added another DELETE FROM and locally it seems to work on SQL Server 2019

IF @StopAt IS NOT NULL
		BEGIN
			DELETE
			FROM @FileList
			WHERE 
			BackupFile LIKE N'%[_][0-9].bak'			AND	
			BackupFile LIKE N'%' + @Database + N'%'
			AND	(REPLACE( RIGHT( REPLACE( BackupFile, RIGHT( BackupFile, PATINDEX( '%_[0-9][0-9]%', REVERSE( BackupFile ) ) ), '' ), 16 ), '_', '' ) > @StopAt);

			DELETE 
			FROM @FileList
			WHERE BackupFile LIKE N'%[_][0-9][0-9].bak'			AND	
			BackupFile LIKE N'%' + @Database + N'%'
			AND	(REPLACE( RIGHT( REPLACE( BackupFile, RIGHT( BackupFile, PATINDEX( '%_[0-9][0-9]%', REVERSE( BackupFile ) ) ), '' ), 18 ), '_', '' ) > @StopAt);

			DELETE
			FROM @FileList
			WHERE BackupFile LIKE N'%.bak' and BackupFile NOT LIKE N'%[_][0-9].bak' AND BackupFile NOT LIKE N'%[_][0-9][0-9].bak'
			AND
			BackupFile LIKE N'%' + @Database + N'%'
			AND
			(REPLACE( RIGHT( REPLACE( BackupFile, RIGHT( BackupFile, PATINDEX( '%_[0-9][0-9]%', REVERSE( BackupFile ) ) ), '' ), 16 ), '_', '' ) > @StopAt);

		END;

Able to reproduce in SQL Server 2014-2019

[BrentOzar]

Thanks for the pull request! I don't have a testbed set up at the moment for sp_DatabaseRestore, but the change makes sense to me conceptually, so I'm going to go ahead and merge it into the dev branch. It'll be in the June release with credit to you in the release notes.