sp_databaserestore don't see backups which consists of more than 10 backupfiles when using @stopAt

[wfvdijk]

Version of the script
8.11
What is the current behavior?
a) If there are more than 9 backupfiles and @StoPAt is defined the cleanup part (line 638-647) will delete all rows, resulting in "no backup found"
b) As a result, if you use @maxfilesize (in the procedure databaserestore from Ola) and the number of files changes to 10 or more, the sp_databaserestore procedure will only see the backups with < 10 backupfiles

If the current behavior is a bug, please provide the steps to reproduce.

• create a database with more than 9 backupfiles
• do a PIT restore
  (Finding A can only be tested with a new database)

What is the expected behavior?
correct determination of the backupfile to use

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
tested with SQL2019, bug also appeared on previous version

[BrentOzar]

Sorry to hear about that! Just to set expectations - because there isn't an exact script I can run to reproduce this quickly, this isn't something I can knock out fast.

There are a few options to move forward:

• You could work on the fix yourself, and then report back here with the fix (if this is an urgent production issue for you, I can see why you'd do that)
• You can contribute a script so someone else can just hit execute and see the problem, and then they can work on the fix (I know it sounds like the steps to reproduce are easy, but for starters, you need to create a backup with more than 9 backup files), or
• You can leave it here as-is, and if there's no activity in the next ~30 days, we'll delete the issue

[wfvdijk]

I'll try to produce a fix for this

[wfvdijk]

This will fix the problem:

	/*End folder sanity check*/

	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);

	END

[BrentOzar]

Hmm - are you sure that works if there's only, say, 5 files?

It looks suspicious that these two lines don't match:

WHERE BackupFile LIKE N'%[_][0-9].bak'

and

WHERE BackupFile LIKE N'%[_][0-9][0-9].bak'

[wfvdijk]

I've succesfully tested this with a backup of 15 backupfiles and one with 3 backupfiles. I tried to combine these 2 situations in one line of code, but I gave up. The 2nd delete only works for 10+ backupfiles (2 numbers before the ".bak" )

[wfvdijk]

Haven't bumped into this one, but the same problem is probably also in this line:

    --No file = no backup to restore
	SET @LastDiffBackupDateTime = REPLACE( RIGHT( REPLACE( @LastDiffBackup, RIGHT( @LastDiffBackup, PATINDEX( '%_[0-9][0-9]%', REVERSE( @LastDiffBackup ) ) ), '' ), 16 ), '_', '' );

[BrentOzar]

Thanks for the pull request! Looks good, merged into the dev branch, will be in the December release with credit to you in the release notes. Yeah, I won't fix the 10+ diff files one - I'll leave that for someone else in case they're bananas enough to use 10+ diff files, heh.