SQL: Why can't I see my files in SSMS when I want to restore?

I had a curious discussion on a mailing list the other day where the questioner was complaining that SQL Server Management Studio (SSMS) wasn’t working properly. He wanted to restore a backup, and his folders did not appear in the folder list to choose from. He could see them in Windows File Explorer but couldn’t see them when trying to restore a backup.

What he wasn’t understanding is that when you use SSMS to perform a restore, what you are doing is sending a command to SQL Server to tell it to do a restore. SQL Server is going to open the file and get the contents out of it, not via your local copy of SSMS.

So, the issue is which files SQL Server can see, not the files that you can see.

This perhaps becomes clearer if you think about SSMS as just a client application that sends commands to a server, and that server often won’t be on the same machine ie: you’ll have a client system running SSMS and a server somewhere that’s running SQL Server.

When you run Windows File Explorer, it’s your identity that’s doing the looking, either on your local machine, or on network shares on other machines.

When you restore a backup, it’s SQL Server that’s looking for the files, either on the server that’s running SQL Server or again on some network share.

The issue then is the identity of SQL Server and the permissions granted to that identity. If you run SQL Server Configuration Manager (one of the tools that ships with SQL Server) on the server, and select the Services option in the left-hand pane as shown in the main image above, you can see who SQL Server is running as. That’s the identity you need to be concerned about.

2018-07-09