An annoying Bug in the Restore Dialog

Today, thanks to a customer, I discovered one of those annoying little things that can really drive you nuts.

Basically, they were trying to restore a backup using the SSMS Restore Database window and they kept getting “No backupset selected to be restored” whenever a backup file was selected.

You just had to select a file for restore and click OK…

… to be met with an error message in the Restore Database window:

The weird thing about it is that the backup file restored perfectly fine from a T-SQL script:

So it had to be something wrong with SSMS, but what?

Looking closer at the restore script, one thing stands out. Look at the file name:

Yep, there’s a leading whitespace in the file name. Could that be the source of the problem?

Let’s try again with the GUI in a slightly different way. This time I will copy the folder path from the “Backup File Location” textbox…

… and paste it directly in the “File name” textbox, right before the file name:

This time everything works as expected.

Bottom line:

This is a bug in SSMS: go on and vote this Connect item to have it fixed in a future version.
Don't use the GUI to restore a database.
Don't use the GUI at all.

Archived WordPress comments (8)

Historical comments from the original site; this archive is read-only.

Jeff Moden February 23, 2016 at 18:35
I'll be damned. I didn't even think of checking for such a thing. Ended up using a script but this is good to know. Thanks, ol' friend.
1. spaghettidba February 23, 2016 at 18:40
  You're welcome, Jeff. Not my sauce anyway: I'm just shouting out what the customer found out.
David McKinney. March 1, 2016 at 09:48
If you used the GUI to begin with, it probably wouldn't have let you put a leading space on your filename. To be honest, I don't have a lot of sympathy for anyone who finds surprising behaviour around a file which they have chosen to name with a leading space (or have I missed the point?)
1. spaghettidba March 1, 2016 at 10:54
  Yep, I think you missed the point. For the record, when you back up a database with the GUI, you can enter file names with leading spaces. That would have been easy to check, especially before posting abrasive comments. To each his own, I suppose.
  1. David McKinney. March 1, 2016 at 11:04
    I can't check it as I don't have SQL Server installed or available to me in my current role. I didn't intend my comments to be abrasive, but I still maintain that creating files of any sort which begin with a space is inviting problems.
    
    With the limited amount of testing I was able to do (albeit not with SQL Server) I note that Windows Explorer, Word and Excel for example will strip out a leading space, while I also saw that from a command prompt this is indeed possible. If someone accidentally creates a file with a space in front, then this is unfortunate, however to do it deliberately I suggest again is unwise, and I am sure that SQL Server is not the only tool to react badly in this circumstance.
    
    However I repeat that my intention was not to offend, and I apologise if you felt offended by it.
    1. spaghettidba March 1, 2016 at 11:21
      Don't worry, no offense taken. I concur that leading whitespaces in file names is an unwise choice and nobody in their right mind would do that. However, typos can happen. If SSMS wanted to fix the problem for you, I think it should at least try to be consistent: trim the file name from both backup and restore dialogs, which is not happening.
Markus March 2, 2016 at 13:39
What version, service pack level and CU is this bug showing itself? Is it the base version.. is it a specific service pack or CU?....
1. spaghettidba March 2, 2016 at 14:12
  Tried in latest and greatest. Can't say for older versions.

See also