sp_DatabaseRestore: @MoveFiles = 1 fails on Linux with Msg 537 (hardcoded backslash path separator)

[BrentOzar]

Describe the bug

On SQL Server on Linux, sp_DatabaseRestore fails with:

Msg 537, Level 16, State 3, Procedure dbo.sp_DatabaseRestore, Line 858
Invalid length parameter passed to the LEFT or SUBSTRING function.

@MoveFiles defaults to 1, so this hits the default code path — a plain restore on Linux fails.

Root cause: two hardcoded backslashes

1. Splitting the filename off PhysicalName. The @MoveFiles = 1 block does:

REVERSE(LEFT(REVERSE(PhysicalName), CHARINDEX('\', REVERSE(PhysicalName), 1) -1))

On Linux, PhysicalName is /var/opt/mssql/data/FRKSmokeTest.mdf — no backslash anywhere. CHARINDEX('\', ...) returns 0, so the expression becomes LEFT(REVERSE(PhysicalName), -1), and a negative length raises Msg 537. Both branches of the surrounding CASE have it, so @Database = @RestoreDatabaseName and the rename path fail alike.

2. Joining the backup directory to the file name. Separately, the generated restore command comes out as:

/var/opt/mssql/data/\FRKSmokeTest_Full2.bak

— a backslash spliced into a forward-slash path. So fixing only the CHARINDEX above is not sufficient; the path-joining also assumes Windows separators.

Why this looks like an oversight rather than a design choice

The same procedure already handles Linux separators when normalizing the drive parameters, at L444-L449:

IF (SELECT RIGHT(@MoveDataDrive, 1)) <> '/' AND CHARINDEX('/', @MoveDataDrive) > 0 --Has to end in a '/'
BEGIN
	SET @MoveDataDrive += N'/';
END
ELSE IF (SELECT RIGHT(@MoveDataDrive, 1)) <> '\' --Has to end in a '\'

So forward-slash paths were considered for the drive parameters and missed elsewhere.

Steps to reproduce

On SQL Server on Linux, with Ola Hallengren's CommandLog and CommandExecute installed:

BACKUP DATABASE YourDb TO DISK = '/var/opt/mssql/data/YourDb_Full.bak' WITH INIT, FORMAT;

EXEC dbo.sp_DatabaseRestore
     @Database            = 'YourDb',
     @RestoreDatabaseName = 'YourDbRestored',
     @BackupPathFull      = '/var/opt/mssql/data/',
     @RunRecovery         = 1;

Observed on

SQL Server 2017 Developer (14.00.3540) and SQL Server 2025, both Linux containers, in the CI matrix from #4047. Reproduces identically on both, and on dev as well as on that PR's branch — not a regression, long-standing.

It only became visible once CI installed the dependencies. Without CommandExecute the procedure exits at the missing-dependency check; with CommandExecute but no CommandLog it exits at The table CommandLog is missing, because sp_DatabaseRestore always calls it with @LogToTable = 'Y'. Both have to be present before this line is reached at all.

Expected behavior

@MoveFiles = 1 builds correct paths regardless of separator.

Suggested fix

Derive the separator from the path rather than assuming one — compute it once (e.g. CASE WHEN CHARINDEX('\', PhysicalName) > 0 THEN '\' ELSE '/' END) and use it in both the CHARINDEX split and the directory join. Worth grepping the whole procedure for hardcoded '\' in path handling; two sites turned up from one repro, so there may be more.

Do you want to build this fix yourself?

Happy to, as its own PR. Not folding it into #4047 — that PR is CI plumbing. It now keeps sp_DatabaseRestore at @Help with the full invocation commented out and pointing here, rather than shipping a step that is permanently red; uncommenting it is the natural first test of this fix.