SQL SERVER – SSMS Error During Restore: No Backupset Selected to be Restored

There are various ways of learning in my current role. Along with client engagements, reading others blog, I also learn from comment/interaction of my own blog. One of my blog readers sent below email about Backupset.

Hi Pinal,
I am a Java developer and have a very less knowledge of SQL Server. To reproduce the scenario. I’m trying to restore my customer’s SQL Server 2008 database (foo.bak) on to our SQL Server 2012. I right clicked on the database in the Management studio 2012, restore database using source – device option, select foo.bak file for the restore. But it is not recognizing the backup file, I keep getting ‘No backupset selected to be restored‘. It doesn’t show up under the restore plan either. I am not sure what I’m missing.

Please help me!

I asked him more details and screenshot and below was given to me.

Here is the SSMS image after backup is selected.

SQL SERVER - SSMS Error During Restore: No Backupset Selected to be Restored backupset-01-800x358

If we click on the message, we see below the text of a pop-up message.

No backupset selected to be restored.

I did some research and shared possible caused by him.


1) Backup is corrupted or unreadable: To confirm this, we can run below command

RESTORE HEADERONLY FROM DISK='complete path to backup file>

If backup is corrupted, we would not be able to see the complete details in the output.

2) Restore to lower version: If a backup is taken on a higher version and restore is attempted to lower version then also we will get the same error.

He replied and told me that backup is not readable and its corrupted. They took a fresh backup from the source database and then they were able to restore.

Have you ever encountered a similar error? Please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Server, SQL Server Management Studio
Previous Post
SQL SERVER – Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error – (400) Bad Request
Next Post
SQL SERVER – Understanding Basic Memory Terms of Physical Memory and Virtual Memory

Related Posts

28 Comments. Leave new

  • Yeah. To me was the 2nd reason. Was trying to restore to a 2014 from a 2012 backup.

  • Vaibhav Shukla
    August 24, 2016 7:18 pm

    Yes I faced same error when upper version backup is restore on lower version of sql server. I got the possibilities from msdn library.

  • I am getting this exact scenario, I have to restart SQL server to get the backup sets to show up in the bottom of the gui, then the restore works fine. Same versions of sql server, bak file is not currupt, just have to bounce sql and it picks it up on the next try.

  • i had the same problem. when i tried the query is said

    The media family on device ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak’ is incorrectly formed. SQL Server cannot process this media family.
    RESTORE HEADERONLY is terminating abnormally

    im using the sql server 2014 and tried to restore from AdventureWorks2014 bak file. it didnt work either way for me.i had to settle for 2012 bak file.

  • Hi, I faced the same issue. The backup was taken on SQL server 2008R2 express and we are trying to restore this on SQL server 2012R2 standard edition. I checked if the backup file is corrupted or not using the above mentioned query. I got the output without any errors. What should be the recovery model and compatibility level ? I will not be able to restart the server since it is a production server.

    please advice

    • Kanupuru Vineeth Reddy
      December 14, 2016 2:20 pm

      Hey, it just didnt work for me. If you want some advice? just dump the file and download an older version of adventure works. theres 2012 version or something like that. since its just a dummy database, it really doesnt matter. But if your want the 2014 version only for some reason, then im sorry man. i couldn’t get it to work. i downloaded an older version and it was perfectly fine for my practice. Good luck. if you figured out a way to make it work then let me know about it. Take care

  • Ravi Shankar Mandal
    March 25, 2017 3:24 pm

    I have created a backup in SQL Server 2014 and Restoring in the same computer and same version of SQL Server but still getting that error.

  • Ravi Shankar Mandal
    March 25, 2017 3:29 pm

    My problem has solved now. The reason was I have created backup in 2 media devices simultaneously. So It requires both files.
    used the below code to find the actual error…hope it will help you also.
    FROM DISK=’\.bak’

    • Thanks for providing another solution.

    • Msg 3241, Level 16, State 7, Line 1
      The media family on device ‘F:\Jijo\SourceCode\ERP_FILES\ERP201521Dec2015.bak’ is incorrectly formed. SQL Server cannot process this media family.
      Msg 3013, Level 16, State 1, Line 1
      RESTORE DATABASE is terminating abnormally.

  • I had two backup sets too. Thank you!

  • These same symptoms will manifest if you do not have SQL rights to restore the backup.

  • I get this error when restoring from 2014 to 2017.

  • Cristian Avalos
    May 29, 2018 6:52 pm

    I get this error in the same versions of SQL. But my problem it’s about permissions (denied to create database).

  • Another reason that the backup set is empty is if the backup was done only of selected file groups. In this case you have to restore not the database but restore files and filegroups.

    Still you may get errors that a particular filegroup was not found on backup media. In that case script the backup query to a new window and remove the filegroups which weren’t backed up. For example:

    RESTORE DATABASE [my_foo_database]
    FILE = N’myfoo’
    –,FILE = N’myfoo_lob’
    –,FILE = N’myfoo_temp’
    FROM DISK = N’c:\backups\my_foo.bak’
    WITH FILE = 1,
    MOVE N’myfoo’ TO ‘c:\Data\my_foo.mdf’,
    –MOVE N’myfoo_lob’ TO ‘c:\Data\my_foo_0.ndf’,
    –MOVE N’myfoo_temp’ TO ‘c:\Data\my_foo_1.ndf’,
    MOVE N’myfoo_log’ TO ‘c:\Data\my_foo_3.ldf’,

    In the above example the backup media didn’t have myfoo_lob and myfoo_temp filegroups therefore they have been commented out. This will restore your database, and give you warnings that these filegroups are not restored.

  • This could be a permission issue as well. Ensure that the user who is doing the restore has the required permission to do so. This took me a while to figure out since i thought the user account i was using was a sys admin account

  • I get this error when I want to restore from different user account eventhough I set the account as administrator. I can only restore from the first user account created. How to solve this?

  • Naresh Koudagani
    February 22, 2019 11:29 pm

    I had the same error, when my source databases were TDE enabled.. on destination server I had to create the master Key, certificate and then try restore and it worked , so i would say 3 possible reasons (2 above pinal mentioned and 1 with trying to restore on a server where master key and certificates are not created and trying to restore TDE Enabled databases)

  • Basavaraj Jagadev Loni
    March 31, 2019 11:32 pm

    this problem i faced lower version to higher version database restore

  • asiva_pvl@yahoo.co.in
    April 14, 2019 2:55 pm

    Pleadse try to put your backup file in “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup”. or try to take a backup of ur own db and u can get the path to place ur backup file.its working if u place the file in sql server default backup path.

  • Harry Rodriguez
    May 18, 2019 8:18 pm

    I had the same problem, I tried to restore a database from a previous version to a recent version, for this you do need back up, go to “Task” and select “Back Up…” in this window select “Media Options” and select “Back Up to a new media set “, write the name of backup, save the file
    preferably in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup”. and try again restore the database in the Manager SQL Server

    Note this file isn´t .bak

  • Ramon Gutierrez
    November 1, 2019 3:44 am

    i HAVE THE SAME ISSUE, The reason was I have created backup in 2 media devices simultaneously. So It requires both files. I USE the Ravi Shankar code but as follow: RESTORE DATABASE MYDATABASE FROM DISK= ‘C:\Backup\MYDATABASE.bak’ . Then i receive a response “The media set has 2 media families but only 1 are provided. All members must be provided.” At final, I use the select backup device and add the two backups, and run perfectly. Thanks

  • Wonderful, I am getting this exact scenario, I have to restart SQL server to get the backup sets to show up in the bottom of the gui, then the restore works fine.


Leave a Reply