Issues with SQL Server Permissions to Restore a Database

By: Sergey Gigoyan   |   Updated: 2021-08-02   |   Comments (1)   |   Related: 1 | 2 | > Restore

Problem

Have you ever faced a situation when you wanted to restore a database using SQL Server Management Studio (SSMS) and could not find the backup file when choosing the backup location in SSMS? However, you were sure that you placed the backup file exactly in that folder. Moreover, when you double-checked for the file using Windows File Explorer you can see the file exists in that location.  So, what was the problem? Why does the file that exists not show up when searching with SSMS? In this article, we will shed light on the cause of this issue and how to solve it.

Solution

We will illustrate this problem by an example and then will provide a solution.

Let’s assume that we need to restore the TestDB database from the backup "C:\BACKUP\TestDB.bak":

We open SSMS, right-click on "Databases" and choose "Restore Database…":

For the backup source, we choose "Device" and pick up our backup location. As we can see, we are looking for backup files in "C:\BACKUP", but we cannot see any file there:

This seems weird unless we are aware of backup/restore permissions. In fact, backup and restore operations run under the SQL Server service account and therefore this account should have the appropriate permissions on the folder.

So, let’s find our SQL Server service account. To do that, we open SQL Server Configuration Manager, and in SQL Server Services, look at the SQL Server account. We can see that in in our case, the service account is sqluser as shown below.

Now, let’s see whether or not this account has "Read" permission to the BACKUP folder we are trying to use. If we right-click on that folder, choose "Properties" and then look at "Security", we can see that all permissions are denied for the sqluser user:

So, let’s grant full permissions to that user and test again:

If we run the restore operation again, we can see that the TestDB.bak file appears in the "C:\Backup" location:

We choose that file and restore the database and we can see it is successfully restored.

Permissions Needed for Restore and Backup

Note, that in our example, we granted full permissions to the SQL Service account. However, for restoring, having just the "Read" permission to the backup file location is enough.

With regards to backup operations, the SQL Server service account should have "Write" permission to the backup folder.

Another Test

Now, let’s do one more test. We will just deny the "Write" permission for the sqluser to the "C:\TestDB_BACKUP" folder and try to back up the database to that destination:

We can see that we receive an error message - "Access is Denied" when we back up the database:

After granting "Write" permission again, the database will be backed up successfully:

Conclusion

In conclusion, the backup and restore operations run under the SQL Server Service account. Therefore, this account needs the appropriate permissions to the corresponding locations where it stores the backups of the existing databases and from where it gets the source backup files for restoring.

Next Steps

Please find more topic-related information by following the links below:

• SQL Server BACKUP (Transact-SQL)
• SQL Server RESTORE (Transact-SQL)
• Setting Up Windows Service Accounts

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips