Issues with SQL Server Permissions to Restore a Database


By:   |   Updated: 2021-08-02   |   Comments   |   Related: More > 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":

backup folder

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

ssms 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:

ssms restore database

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.

sql server configuration manager

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:

folder security properties

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

folder security properties

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

file explorer

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

restore database

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:

folder properties

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

backup failure

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

backup test

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:






get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips


Article Last Updated: 2021-08-02

Comments For This Article





download














get free sql tips
agree to terms