SQL Server 2017 relative path issue

Question

Encountered interesting problem after migrating to SQL Server 2017.

Absolute path test

backup database master to disk = 'C:\Temp\1\2\3\master.bak'

Backup is created in C:\Temp\1\2\3\ as expected.

Relative path test #1

backup database master to disk = 'C:\Temp\1\2\3\..\master.bak'

Backup is created in C:\Temp\

Expected: C:\Temp\1\2\

Relative path test #2

backup database master to disk = 'C:\Temp\11\22\33\..\master.bak'
backup database master to disk = 'C:\Temp\111\222\333\..\master.bak'
backup database master to disk = 'C:\Temp\bin\bin\bin\..\master.bak'

Backups are created in folders C:\Temp\11\, C:\Temp\111\, C:\Temp\bin\

Expected: C:\Temp\11\22\, C:\Temp\111\222\ and C:\Temp\bin\bin\ respectively.

Relative path test #3

backup database master to disk = 'C:\Temp\Folder1\Folder2\Folder3\..\master.bak'
backup database master to disk = 'C:\Temp\1111\2222\3333\..\master.bak'

Backups are created in folders C:\Temp\Folder1\Folder2 and C:\Temp\1111\2222 respectively as expected.

As you can see, all relative paths have the same depth, but depending on folder names lengths the backup file lands on different levels. I wasn't able to replicate this issue with SQL Server 2014. I was able to replicate this issue both in SQL Server 2017 RTM and RTM-CU2.

OS: Windows Server 2016. Paths are resolved correctly when pasted to Windows Explorer.

How do I make relative paths work when they contains short (under 4 characters) folder names?

Scrimpy

interesting, voted up – Aluminize

I'd post this to the microsoft page as it may be a bug – Kinsfolk

Answer 1

This seems to be fixed in SQL Server 2017 CU14 (or maybe earlier).

Stockjobber

Answer 2

Confirmed on "Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)"

This is a bug! However, after playing around with it I found a workaround by adding another dot after the directory names.

backup database master to disk = 'C:\Temp\1.\2.\3.\..\master.bak'

This successfully backed up to 'C:\Temp\1\2\'. I've found no reference why this works. Also, it would probably a good idea to stay away from low character directories if possible.

Avernus

Fighting bugs with more bugs? – Preclinical