sp_DatabaseRestore: Differential Backups and Log are skipped when the Full Backup are using multiple files.

[MikeEOR]

Version of the script
sp_DatabaseRestore version 8.25+

What is the current behavior?
Differential Backups and Log are skipped when the Full Backup are using multiple files.

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

Using this command.

EXEC drm.dbo.DRMDatabaseRestore
@Database = @DatabaseName,
@RestoreDatabaseName = @DatabaseRestoreName,
@BackupPathFull = @BackupPathFull,
@BackupPathDiff = @BackupPathDiff,
@BackupPathLog  = @BackupPathLog,
@MoveFiles = 1,
@MoveDataDrive = @MoveDataDrive,
@MoveLogDrive = @MoveLogDrive,
@RestoreDiff = 1,
@ContinueLogs = 0,

-- @debug=1,
@RunRecovery = 1;

What is the expected behavior?

Differential Backups and Log Should be loaded just like when the full backup file is just a single file.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Only tested in 2022 and 2025

The bug can be found around line 950-960 with the statement
"SET @BackupDateTime = REPLACE( RIGHT( REPLACE( @LastFullBackup, RIGHT( @LastFullBackup, PATINDEX( '%[0-9][0-9]%', REVERSE( @LastFullBackup ) ) ), '' ), 16 ), '', '' );"

This code only assumes the backup's file name does not contain extra numeric characters for prefix for use in sequence when using multiple files.
If there are sequence numbers added, the code will return with the wrong numeric expression of the date. It will miss the first few characters of the supposed to be date field depending on the length of the extra numeric sequence
ex. MYDB_FULL_20260125_200000_10.bak will result in returning 26012520000010 as the backup date time instead of 20260125200000

Because the @BackupDateTime contains the wrong date, the next code at around line 1100+ will fail

' IF @RestoreDiff = 1 AND @BackupDateTime < @LastDiffBackupDateTime'

For now I have changed the code to the proc sp_DatabaseRestore on my server to get the correct date minus the prefix when working with split fiiles.

	_IF (SELECT COUNT(*) FROM #SplitFullBackups) > 0
	BEGIN 
		SET @BackupDateTime = LEFT(REPLACE( RIGHT( REPLACE( @LastFullBackup, RIGHT( @LastFullBackup, PATINDEX( '%_[0-9][0-9]%', REVERSE( @LastFullBackup ) ) ), '' ), 18 ), '_', '' ),14);
	END
	ELSE
		SET @BackupDateTime = REPLACE( RIGHT( REPLACE( @LastFullBackup, RIGHT( @LastFullBackup, PATINDEX( '%_[0-9][0-9]%', REVERSE( @LastFullBackup ) ) ), '' ), 16 ), '_', '' );_

[BrentOzar]

Hi! Yes, that's been a known issue for quite some time. If you'd like to submit a pull request that changes the code, we'd be glad to accept it. Would you like to do that?