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.
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.
POSSIBLE REASONS
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)
28 Comments. Leave new
Yeah. To me was the 2nd reason. Was trying to restore to a 2014 from a 2012 backup.
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
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
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.
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.
RESTORE DATABASE
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.
Interesting. What is the version of SSMS? Is the backup readable?
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’,
NOUNLOAD, STATS = 10
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?
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)
this problem i faced lower version to higher version database restore
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.
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
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.