bak file not visible when trying to restore database

Question

I am trying to run a classic asp project on visual studio and therefore I am trying to restore a database. I cannot seem to find the .bak file when trying to restore it. It wasn't visible in the C-drive. I have looked it up and have seen that it has something to do with permissions. I have tried enabling permissions after properties>security of .bak file to 'Everyone', it did not work. I have also tried NT Service\MSSQLSERVER. Please see the following below.

Southerly

just to be sure ... have you tried selecting the "All files" option from the drop down above the OK Button ? Default is .bak,.trn. Pehaps the extension of the file is not .bak – Succotash

All the security permission changes you are doing must be done on the parent directory instead which is containing the *.bak file and not the bak file itself. In your case, the bak file is present in root of C:` drive so you will have to make permission changes on the C:` drive which is containing your bak file. Changing permissions on `C:` drive root is not considered a good practice as it is usually the boot drive. So, a much safe approach would be to create a folder in C:\ drive, move your bak file to the new folder and change the security permissions of the new folder instead. – Blackmun

Answer 1

I had the same issue. I get to know that it was a a permissions issue.

Here are the following steps to solve this problem

Go to Control panel > Administrative Tools (Windows Tools for Windows 11)> Services double click on the services. copy the log on account name of sql service as shown in the picture.

Go to your backup folder and right click and select properties. Follow the steps as shown in picture. After clicking on edit you will get new window.

Click on add

Paste here the Log on account name of sql service account which you had copied earlier. then Click on check name button.

Select MSSQLSERVER then click ok.

Now you will get your sql server account name in security tab.

Select sql server account name that and in permission box select Allow in all check boxes(I added Full Control) then click apply.

Now you will be able to select and restore your backup file.

Tinnitus

If you're using a named instance, here is an example: servername\MSSQL$myNewInstance – Gemperle

Answer 2

You also have to enable permissions to the folder where the file is located. SSMS first needs to browse the folder, then to read the file.

In your case, SQL server tries to access the folder using NT Service\MSSQLSERVER account, so permissions should be granted to that account, no need to give more permissions (to everyone).

Also try below if above doesn't help:

• close/open SSMS
• move the file from root to some folder and grant permissions to that folder
• use TSQL script to restore the database instead of SSMS

Elijah

I gave Read & execute, List folder contents, and Read permissions to <localhostComputer>\Users user group and then it worked for me. Giving permissions to NT Service\MSSQLServer was equally helpful. – Blackmun

Giving same access permissions to built-in Everyone user also helps the same way. – Blackmun

For named SQL server instance the service account is of format NT Service\MSSQL$<SqlServerInstancename> – Blackmun

Answer 3

you can copy the backup folder in to C:\Users\MSSQL$HCMIS\Desktop sql server users folder.Now it will be accessible

Esra

Answer 4

I'm facing simpler issue for sql server standalone 2014 enterprise edition ,installed in my home system . I tried every possibility provided in various article but strangely the issue is with my 2 named instance . I can restore database on my default instance with query and GUI . I tried changing log-on as account of sql server service for named instance to 'network service' or 'local system' and giving them full control permission to backup holding folder .

Cufic

Answer 5

In my (and probably a typical/common case): I looked through (and compare with other .bak files) in the windows permission and see nothing wrong.

However - This may be due to that one does not follow to restore from the [device] option first. Even you store the .bak in the right path, it is initially from the [Source]->[Database] check box, until you follow the steps to restore via the [device] option

(In my case the second AdventureWorksDW2020-DAX-Docs was not available until I go thru extra steps to restore it via the [Device] option first.)

The SQLserver need to do internal access right grants to users... etc..(even all the needed Windows properties are in place) –

Reference https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

Phew