#0425 – SQL Server – Backup exists but doesn’t display on the restore window in SSMS. Why? How to fix?


Recently, I ran into a forum post where the ask was to figure out why a perfectly valid backup was not visible when attempting to restore it via the wizard in SSMS. Today, I will reproduce the issue, explain the root cause and provide the solution for the same.

Building the scenario

In one of the my test SQL Servers, I have a copy of the [AdventureWorks2019] sample database, which I have backed up using the following simple script.

USE [master];
GO
BACKUP DATABASE [AdventureWorks2019]
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2K17\MSSQL\Backup\AdventureWorks2019.bak'
    WITH COMPRESSION;
GO

Now, to simulate the movement of the backup to a different machine, I created a new folder under my default “Documents” folder and placed the backup there.

Screenshot showing the placement of the target folder where the backup is placed
Screenshot showing the placement of the target folder where the backup is placed

Reproducing the symptom

Restoring via SSMS

  1. Connect to the target SQL Server using SSMS
  2. Right-click on the “Databases” folder in the Object Explorer
  3. Choose to Restore a database
  4. Under “Source”, select the radio-option for restoring from a “Device”
  5. Use the ellipsis to open the “Select Backup Devices” window and open the File explorer by choosing “Add”
  6. Navigate to the folder where the backup has been placed
    1. Expected Result: We should be able to see the folder and the backup file
    2. Actual Result: The backup file is not seen (the folder may or may not be seen)
Screenshot showing that the backup exists, but it is not seen in the "Locate Backup File" window
Screenshot showing that the backup exists, but it is not seen in the “Locate Backup File” window

Restoring via T-SQL

While the UI keeps things a bit mysterious, attempting to restore via T-SQL does point us to the right direction.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2019_Copy]
    FROM DISK = 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak';
GO

Here’s the error that we run into:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Notice that the error clearly says – “Access is denied.

Root Cause

As highlighted by the results of the T-SQL script, SQL Server is actually running into a security problem. The operation is done under the context of the SQL Server instance service user (i.e. the user under which the SQL Server service runs).

Because the user doesn’t have access to the folder we just created, the service cannot see the files underneath.

Solution

The solution is to use the SQL Server Configuration Manager to figure out the user under which the SQL Server service runs.

Once the user is identified, provide access to the target folder to the user and the files should now be visible – both to SSMS and to T-SQL.

Screenshot showing the backup file is now visible once the SQL Server instance service has been granted to the folder
Screenshot showing the backup file is now visible once the SQL Server instance service has been granted to the folder

Further Reading/References:

Until we meet next time,

Be courteous. Drive responsibly.

2 thoughts on “#0425 – SQL Server – Backup exists but doesn’t display on the restore window in SSMS. Why? How to fix?

  1. Jeff Moden

    I’ve not yet experienced this problem but thank you for bringing it up and writing about how to determine the actual problem and the fix, Nakul. I’m going to try it out in a couple of places because I can see it happening someday!

    I really appreciate the time you spend on this article, Nakul.

    Liked by 1 person

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.