60

I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it.

I can script the restore process, but I've seen this problem before and I'm not sure what could cause the .bak to not appear.

8
  • I'm going to write a script for the restore, I just want to know what is causing it. It's definitely a .bak, not a .bak.txt or anything.
    – Sean Long
    Jul 30, 2013 at 19:04
  • So are you able to browse the folder where you know the file is, and it's not appearing? Or are you unable to browse to the folder? Jul 30, 2013 at 19:05
  • I can browse to the folder, just not see any of the .bak files. I've checked the permissions of the user I'm running SSMS with, they're a local admin and the file isn't marked as "hidden" or anything.
    – Sean Long
    Jul 30, 2013 at 19:06
  • Can you post a screen shot somewhere, showing Windows Explorer's view of the folder, and that of SSMS? Also explain in the question the process you are using to get to this step. Jul 30, 2013 at 19:11
  • 1
    (Also note the SQL Server service account has to have permissions too, not just the SSMS user. Though that shouldn't prevent .bak files from showing in the list before you do anything with them.) Jul 30, 2013 at 19:13

7 Answers 7

56

We had the same issue today. It turned out to be a permissions issue, as illustrated in some of the other answers. The difference is that the account we needed to add was NT SERVICE\MSSQLSERVER.

Screenshot

I identified the account by comparing the permissions of the default SQL Backup folder permissions to the folder containing the backup file.

Adding the service account is not as simple as it sounds. Here is what worked for me. (Server 2012 R2)

  1. Log into the server. (The change must be made on the actual server, not through a network share.)
  2. Change the Locations to the local server name.
  3. Type in NT SERVICE\MSSQLSERVER in the name box. (Do not click Check Names)
  4. Click OK. You will then see a list of the matching service accounts. Select MSSQLSERVER, and click OK to accept the selection.

enter image description here

enter image description here

You should see the service account listed in the security settings, and can adjust the permissions accordingly.

After adding permissions (I added Full Control, to match the default SQL Backup folder.) and restarting the SQL Server Management Console, I was able to select and restore my backup file.

3
  • superuser.com/questions/248315/… tells us how to list user accounts on a computer. For any service named s, there is a virtual account named NT Service\s, even if the service does not run under that account. Mar 14, 2016 at 15:25
  • 3
    The account I had to add was NT Service\MSSQL$SQLSERVER2014, perhaps because I named my instance SQLSERVER2014 May 10, 2019 at 2:04
  • I solved it by adding permissions for Computername\Users
    – Ola Eldøy
    Sep 28, 2020 at 9:58
17

I got this today.

I turned off file sharing, indexing and cleared any open sessions (people logged in to copy backups), but problem did not go away.

Went to Windows Explorer, copied the backups to another folder and browsed to that one through Management Studio, and I could see it there.

Deleted the original folder and recreated it, copied contents back again and browsed to the .bak files and it was back to normal.

Hope this helps someone.

2
  • 1
    it did help! :) Mar 10, 2014 at 8:02
  • This is a typical answer in computing. Lots of other complicated solutions which will swallow lots of your time, but this simple answer solved the problem for me too. Thanks.
    – Andy Brown
    Feb 25, 2022 at 12:53
11

I encountered this problem a couple of times. The issue is not with SQL Server but with Windows Permission of the folder. You have to add appropriate permission to the folder where you copied .bak file (I think System Network role).

The easier solution is, to move the file to the default back up folder in Program Files. It has all the necessary permission. For SQL Server 2012 it is

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
3
  • This is a great solution because one is not opening up potential security holes in their computer just to restore one file. Oct 24, 2018 at 22:25
  • works great no need for policies and stuff
    – Marin
    Jul 12, 2019 at 15:04
  • This is an appropriate answer. Worked excellent especially on Windows10
    – Bluesight
    Mar 18, 2020 at 8:13
3

I also came across the same issue today. I was given a backup file to restore but it was not visible to me while browsing. However I was able to acess the in file system but in in restore wizard using SQL Server management Studio.

After spending some I discovered that it is permissions issue. SQL Server Service is running using Network Service but Network Service account did not have permissions to access the folder. After granting the permissions to Network Service. Backup file was visible to restore.

For details please check by Blog.

2

If you can find it in Windows Explorer, then you should write a RESTORE DATABASE command instead of relying on the clunky GUI to find it for you. Who knows what code is going on in there and why it might not be able to find the file - sorry I only have a workaround and not a solution.

Also ensure that the file is actually something.bak and not something.bak.txt (Windows may be "helpfully" hiding the extension from you. I hate that default behavior.)

2

This happened to me today on my dev box. In my case, the service account had permissions to the folder, but my user account didn't. Once I granted my account permissions to the folder, I was able to see the .BAK files.

-1

In my case, I accidentally connected to the WRONG server, obviously I couldn't find the backup file there....

The wrong server hostname differ only by a single letter, it's a mistake that was bound to happen. It's preferable to connect using "." or "localhost" instead of the full server hostname, if you're already logged in via RDP on the server.

enter image description here

In hindsight, try the command line instead of SSMS GUI. That will give you a clear error "file not found". This rules out a permissions issue.

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

Hope this helps!

Not the answer you're looking for? Browse other questions tagged or ask your own question.