There is a database on a server of which I would like a copy on my local machine for testing purposes, so I created a backup in the default disk location (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup). The backup dialog says it completes successfully, but when I go to restore the .bak, it's not visible to the file selection dialog (significant file names blurred, obviously).

enter image description here

There are other viable backups in the same location, but the one I supposedly created does not appear. When attempting to recreate the backup, the one I created appears as a selectable option, while the others in the folder do not.

enter image description here

What is the issue here? I don't think it's a permissions problem because I'm able to access the other backups in the same folder.

Edit: The backup was on the server which has a similar file structure to my machine. I remoted in to the server and copied the file. Thanks for the help!

  • What happens if you try changing the file type selection to "All Files (*)" – James Jenkins Dec 4 at 18:37
  • 1
    Just to confirm: the DB you're backing up is on your local machine, and when you restore it (under a different name), the restored DB will also be on your local machine - correct? – RDFozz Dec 4 at 18:40
  • Have you tried backing up to a non-default folder (after making sure the DB Engine user account has rights to that folder)? – CaM Dec 4 at 18:48
  • Program Files vs. Program Files (x86) folder? – Jacob H Dec 5 at 16:22

You did not save the files where you think you did.

In the first image there are 9 folders below 'Microsoft SQL Server' and above 'MSSQL11.MSSQLSERVER' in the second screen shot there are only 5.

This is possibly an issues with your local machine VS a seperate server. Assuming the address you are following are identical (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup) the only solution remaining is that the C drives are on different machines.

I've made the mistake before of missing out or mistyping the .bak file extension during backup and then it fails to display file when trying to locate for restore.

SQL Server keeps track of the actual location it wrote the backup file to. Review this T-SQL to get the information you need.

SELECT  TOP 10  
            [a].[server_name],
            [a].[database_name],
            [a].[backup_finish_date],
            [a].[backup_size],
            CASE [a].[type]
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential'
                WHEN 'L' THEN 'Transaction Log'
                ELSE [a].[type]
            END AS [BackupType],
            [b].[physical_device_name]
FROM        [msdb].[dbo].[backupset] AS [a]
JOIN        [msdb].[dbo].[backupmediafamily] AS [b] ON [a].[media_set_id] = [b].[media_set_id]
WHERE       [a].[database_name] = ''
ORDER BY    [a].[backup_finish_date] DESC

If you are unable to find the file in the location SQL Server specifies, that indicates one of the following scenarios:

  1. You do not have permission on the disk to view the file
  2. The file has been moved / deleted by a user or process

Source of T-SQL: https://www.mssqltips.com/sqlservertip/2960/sql-server-backup-paths-and-file-management/

It is not permission related situation. I have seen this situation in so many forums where user do not find the .bak file for restoring.

In that case, you can take a backup of the database at any location on the local machine and, copy the .bak file to the backup folder of the SQL database.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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