SQL Server 2017 crashes when backing up because filepath is wrong

Question

I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs and found nothing more than SQL Server closed unexpectedly. I would then have to go and restart the service.

I narrowed the issue down to the script that the GUI was trying to run. The problem is when it goes to take a tail log backup, the path to the backup files is wrong. It should be D:\mapbenefits\...

BACKUP LOG [mapbenefits]
TO  DISK = N'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'
WITH NOFORMAT, NOINIT,  NAME = N'mapbenefits_LogBackup_2019-02-21_13-58-24',
    NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

I have two questions.

1. How do I fix this path? I tried going into the server settings and the backup path is D: with no slash. If I add the slash the gui removes it. This is SSMS v17.9.1. I can pick D:\mapbenefits\ and that works but I want D:\DATABASE\...

2. Is this a bug? Should SQL server crash just because a path is incorrectly typed? Once I fixed the file path it has no trouble. I can reproduce anytime just by mucking up the filepath.

If I run a query to check version I get CU13, but if I go into settings I see version 14.0.1000.169.

Looks like this is a bug and is reproducible so I posted it to here: https://feedback.azure.com/forums/908035-sql-server/suggestions/36920542-incorrect-filepath-with-backup-log-command-causes

Answer 1

I was able to reproduce this.

On 2016, if I put an invalid path like that, I get this message:

Cannot open backup device 'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'. Operating system error 3(The system cannot find the path specified.)

On 2017 CU 13 (14.0.3048.4), it results in the service crashing. You've already mentioned that in the latest hotfix (14.0.3049.1), the service doesn't crash, but the session is killed.

I've confirmed the same exact behavior applies to RESTORE DATABASE as well - passing a path like "D:Backups" (with a missing backslash) or "D::\Backups" (extra colon) crashes the SQL Server instance (thanks Michael K Campbell for bringing this up).

If I put a "valid" path that doesn't exist, I get the correct behavior ("cannot find the path specified") in 2017.

This is a bug - in both CU 13 and the hotfix build. Passing incorrect parameters to the BACKUP or RESTORE commands shouldn't crash the service or kill your session. You can report it on the feedback site.

Note: the service-crashing version of this bug can be reproduced in a public preview version of SQL Server 2019 (CTP2.2 - thanks to Denis Rubashkin for pointing this out)

---

Looking at this in a debugger, it seems that path validation code is simply broken. It ends up causing a stack overflow by recursively calling into sqlmin!CheckFileStreamReserved after the normal (and quite extensive) checks on the supplied path fail to make sense of it. The stack overflow brings down the service on build 3048. The same thing happens on 3049 except a sequence of access violations while attempting to handle the stack overflow instead breaks the connection rather than stopping the whole server.

---

A fix for this bug was released in SQL Server 2017 CU 15:

FIX: SQL Server 2017 crashes because of stack overflow when you try to back up database master to disk

The issue is also resolved in SQL Server 2019 CTP 3.0.