I have a SQL Server 2012 database with filestream enabled. However, when I backup it and try to restore it on another SQL Server 2012 instance (on another machine), I simply get this message that:
No backupset selected to be restored.
Not even a single line of more explanation. What’s wrong here?
Every other database without filestream is OK and can be restored successfully. Is it something related to filestream? Should I install a hotfix or something like that.
asked Aug 25, 2012 at 6:54
Saeed NeamatiSaeed Neamati
35.2k41 gold badges135 silver badges188 bronze badges
8
I had this problem and it turned out I was trying to restore to the wrong version of SQL. If you want more information on what’s going on, try restoring the database using the following SQL:
RESTORE DATABASE <YourDatabase>
FROM DISK='<the path to your backup file><YourDatabase>.bak'
That should give you the error message that you need to debug this.
SteveC
15.6k23 gold badges101 silver badges173 bronze badges
answered Aug 28, 2012 at 11:40
6
My problem ended up being permissions. I’m on a dev machine and copied via Homegroup. Somehow, probably based on where I copied the file to, the permissions got messed up and Management Studio couldn’t read the file. Since this is dev I just gave Everyone permissions to the bak file and could then successfully restore via the GUI.
answered Nov 9, 2012 at 3:11
3
When running:
RESTORE DATABASE <YourDatabase>
FROM DISK='<the path to your backup file><YourDatabase>.bak'
It gave me the following error:
The media family on device ‘C:NorthwindDB.bak’ is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
Blockquote
Turns out You cannot take a DB from a Higher SQL version to a lower one, even if the compatibility level is the same on both source and destination DB.
To check the SQL version run:
Select @@Version
To see the difference, just create a DB on your source SQL server and try to do a restore from your backup file, when you do this whit SSMS, once you pick the backup file it will show some info about it as opossed to when you open it from a lower version server that will just say «no backupset selected to be restored»
So if You still need to move your data to a lower version SQL then check This.
answered Jan 5, 2016 at 15:56
Alon OrAlon Or
7887 silver badges7 bronze badges
6
Run SQL Server Management Studio as an administrator (right-click the shortcut/exe, then select «Run as Administrator»), then try to restore.
answered Sep 25, 2014 at 20:28
KeithKeith
20.5k11 gold badges83 silver badges125 bronze badges
0
I thought I was not stupid enough to mix up the versions — however, I didn’t realize that on my new server, a SQL Server 2005 instance was already installed from birth named SQLEXPRESS. When trying to restore my SQL Server 2008 R2 backed up database in SSMS 2012 to the SQLEXPRESS instance, the list of backup sets was empty.
Eventually I realized that the SQLEXPRESS instance on the server was not a 2012 instance, but a 2005. I disconnected and connected to the actual 2012 instance (in my case named SQLEXPRESS2012), and it (obviously) worked.
answered Sep 11, 2012 at 8:21
3
My problem was that my user was in the Builtin-Administrators group and no user with Sysadmin-role on SQL Server.
I just started the Management Studio as Administrator. This way it was possible to restore the database.
answered May 12, 2014 at 7:09
LizLiz
811 silver badge1 bronze badge
1
FYI: I found that when restoring, I needed to use the same (SQL User) credentials to login to SSMS. I had first tried the restore using a Windows Authentication account.
answered May 22, 2014 at 19:05
Ben RipleyBen Ripley
2,11521 silver badges33 bronze badges
In my case, it was permissions and the fact that I used «Restore Files and Filegroups…» rather than simply «Restore Database …».
That made the difference.
answered Jan 12, 2016 at 14:41
BrianBrian
3,6531 gold badge22 silver badges33 bronze badges
For me it’s a user privilege issue.
I logged-in with sa user and its working fine.
Harry
2,9976 gold badges37 silver badges64 bronze badges
answered Jul 4, 2017 at 9:43
SharfiSharfi
3553 silver badges12 bronze badges
For me the problem was having the .BAK file located in an encrypted folder on the server. Even with full Admin rights, I could never get SSMS to read the file. Moving the .BAK to an unencrypted folder solved my problem. Note that after moving the file you may have to also change the properties on the actual file to remove encryption (right click, properties, advanced, uncheck «encrypt contents to secure data».
answered Sep 11, 2013 at 22:03
GadgetNCGadgetNC
1593 silver badges4 bronze badges
In my case (new sql server install, newly created user) my user simply didn’t have the necessary permission. I logged to the Management Studio as sa, then went to Security/Logins, right-click my username, Properties, then in the Server Roles section I checked sysadmin.
answered Nov 23, 2015 at 10:14
Marc.2377Marc.2377
7,6777 gold badges51 silver badges95 bronze badges
I had the same issue with SQL Server 2014 (Management Studio could not see the folder in which the backup file resided, when attempting to locate it for a Restore operation). This thread held the answer that solved my problem. Quote:
The SQL Server service account can be found by Start->Control
Panel->Administrative Tools->Services. Double-click on the SQL Server
service->Log On tab. You’ll either be using the «Local System account»
or «This account» to define a specific account. If you are using the
Local System account, you won’t be able to reference backups that are
not local to the server. If, instead, you have defined the account to
use, this is the account that needs to have access to the backup file
location. Your ability to access the backups using your personal logon
is irrelevant; it is the SQL Server account that is used, even though
you are initiating the backup. Your IT people should be able to
determine what rights are granted to each account.
Hope that helps someone.
answered Jan 16, 2015 at 13:20
BCABCA
7,7043 gold badges37 silver badges52 bronze badges
0
For me, it was because the backup file was still open by another process. Here’s the event log:
BackupDiskFile::OpenMedia: Backup device ‘X:BackupsMyDatabaseMyDatabase_backup_2014_08_22_132234_8270986.bak’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).
Simply closing and reopening Sql Server Management Studio resolved it (so obviously it was ssms.exe that had the handle..)
answered Aug 22, 2014 at 20:05
JohnZajJohnZaj
3,0505 gold badges36 silver badges51 bronze badges
In my case, it was a permissions issue.
For the Windows user, I was using did not have dbcreator role.
So I followed the below steps
- Connect as
sato the SQL server - Expand
Securityin Object Explorer - Expand
Logins - Right click on the Windows user in question
- Click on Properties
- Select
Server RolesfromSelect a pageoptions - Check
dbcreatorrole for the user - Click OK
answered Sep 18, 2018 at 0:46
Devraj GadhaviDevraj Gadhavi
3,5013 gold badges38 silver badges67 bronze badges
Another potential reason for this glitch appears to be Google Drive. Google Drive is compressing bak files or something, so if you want to transfer a database backup via Google Drive, it appears you must zip it first.
answered Aug 15, 2014 at 19:13
James G.James G.
2,8223 gold badges27 silver badges52 bronze badges
If you want to replace the existing database completely use the WITH REPLACE option:
RESTORE DATABASE <YourDatabase>
FROM DISK='<the path to your backup file><YourDatabase>.bak'
WITH REPLACE
answered Aug 23, 2018 at 11:54
Iasmini GomesIasmini Gomes
6971 gold badge9 silver badges14 bronze badges
Using SQL Server 2012 Express.
My error (from the SQL Manager — Restore Database Dialog):
No backupset selected to be restored
Further, there were no backupsets shown in the list to select.
The issue was that I had moved 1 of the 5 backup files to a folder where the SQL Server service logon user did not have permissions — I tried to add that user, but could not get the NT ServiceMSSQL$SQLEXPRESS user into the security list.
I moved the file under the Documents folder for the service user, and that enabled it to read all the files I had selected — 4 at that point — and the error changed to «media set missing» — then I looked around for another backup file, and when I added that I was able to restore.
The answers in this question helped me look in the right places and work my way to a solution.
For me, It was a permission issue. I installed SQL server using a local user account and before joining my companies domain. Later on , I tried to restore a database using my domain account which doesn’t have the permissions needed to restore SQL server databases. You need to fix the permission for your domain account and give it system admin permission on the SQL server instance you have.
answered Jan 29, 2017 at 7:49
I got the same error message even though I backup and restore on the same single machine.
The issue was from here: when backup, i had 2 item in the destination box.
So the fix would be: make sure only 1 item in the ‘destination’ box. Remove all the others if there are.
answered Aug 31, 2017 at 19:20
I have run into the same issue. Run SSMS as administrator then right click and do database restore. Should work.
answered Oct 3, 2018 at 18:48
Jonny BoyJonny Boy
1921 gold badge1 silver badge10 bronze badges
I think I get the award for the most bone headed reason to get this error. In the Restore Database dialog, the database dropdown under Source is gray and I thought it was disabled. I skipped down to the database dropdown under Destination thinking it was the source and made a selection. Doing this will cause this error message to be displayed.
answered Jan 22, 2019 at 15:34
GabeGabe
8079 silver badges13 bronze badges
В этой статье мы расскажем об ошибке SQL Server No Backupset Selected to Be Restored. Лучшим решением для управления реляционными базами данных является Microsoft SQL Server. Это приложение невероятно эффективно и широко используется ИТ-отделами. Это помогает в хранении и поиске важных данных от различных компаний. Когда пользователь хочет получить доступ к данным, он или она должны выполнить инструкции SQL. Пользователи SQL могли столкнуться с ошибками в результате внезапного завершения, проблемы с подключением к базе данных или временной ошибки. Когда пользователи пытались восстановить данные из файла резервной копии, сервер SQL сообщал о том, что для восстановления не выбран набор резервных копий.
- При попытке восстановить резервную копию из различных версий SQL пользователь столкнется с этой проблемой. Например, предположим, что у нас есть SQL Server 2008, и мы хотим восстановить резервную копию SQL Server 2012 или 2014.
- Если у вас есть неисправный или нечитаемый файл резервной копии, это может быть причиной ошибки сервера SQL «не выбран набор резервных копий для восстановления». Следующая команда позволяет пользователю быстро проверить вашу базу данных.
RESTORE HEADERONLY FROM DISK = «Путь к файлу резервной копии» - Другим важным фактором являются права доступа к файлу «.bak». Если файл .bak имеет специальные разрешения или находится в папке с определенными разрешениями, Management Studio не сможет получить доступ к файлу, что приведет к этой ошибке. Итак, прежде чем мы начнем восстановление, мы дважды проверяем, что у файла .bak есть правильные разрешения.
Если у вас возникла эта проблема и вы хотите узнать больше об ошибке, попробуйте восстановить базу данных с помощью приведенной ниже команды.
RESTORE DATABASE FROM DISK =’путь к файлу резервной копии’>.bak’
Примечание: Если ваша база данных SQL повреждена, Программное обеспечение для восстановления базы данных SQL может использоваться для исправления и восстановления данных из базы данных SQL-сервера.
Профессиональное решение для SQL Server Не выбран набор резервных копий для восстановления Ошибка
SQL Server не включает никаких инструментов для исправления и восстановления файлов резервных копий, которые были повреждены. Вы должны использовать Программное обеспечение DataHelp для восстановления резервной копии SQL чтобы решить эту проблему. Это приложение было создано для восстановления поврежденных файлов резервных копий SQL Server и для восстановления файлов .bak в базах данных SQL Server. Для восстановления базы данных из резервной копии работает со всеми версиями MS SQL Server, включая 2017, 2016, 2014, 2012, 2008 и другие. Все проблемы с файлами bak, такие как ошибка 3241, ошибка SQL Server 3154 и отсутствие набора резервных копий для восстановления, легко устраняются с помощью этого программного обеспечения.
Шаги по исправлению того, что для восстановления не выбран набор резервных копий
1. Чтобы восстановить базу данных SQL Server, загрузите и установите Программное обеспечение для восстановления SQL.
2. К добавить файлиспользуйте инструмент восстановления SQL-сервера «Открыть» вариант.
3. Параметры сканирования доступны в SQL МДФ инструмент восстановления:
а. Быстрое сканирование б. Расширенное сканирование для восстановления поврежденных баз данных SQL Server, а также для автоматического обнаружения файлов MDF SQL Server.
4. Ты сможешь автоматическое определение файлы NDF, используя вторую вкладку, «Опция NDF».
5. Вы можете сохранить MDF-файл который был отсканирован.
6. Утилита предоставит вам предварительный просмотр восстановленных файлы базы данных (МДФ и НДФ).
7. Вы можете экспортировать база данных с помощью инструмента экспорт вариант следующим образом:
а: База данных SQL-сервера б: Скрипты, совместимые с SQL Server
8. Компоненты, которые необходимо экспортировать из восстановленной базы данных, теперь можно проверить.
Читайте также: Восстановление поврежденной базы данных SQL-сервера
Заключение
В этой статье мы узнали о проблеме и ее причинах при восстановлении файла резервной копии в SQL Server. Эта проблема возникает из-за поврежденного файла резервной копии или восстановления в более старой версии SQL Server. Невозможно восстановить поврежденные файлы резервных копий SQL вручную. В результате мы предлагаем вам использовать профессиональное решение для решения проблемы SQL Server No Backupset Selected to Be Restored. Он работает со всеми версиями Windows, включая Windows 10, 8.1 и 8.
- Remove From My Forums
«No backupset selected to be restored» — Cannot restore SQL Server 2014 Database backup with TDE enabled on to a new instance
-
Вопрос
-
Hello,
I am using SQL Server 2014, SP2, Enterprise edition, on both old and new instances. I am trying to restore a backup (about 360 GB) of a TDE enabled database on to the new instance. The certificate had expired on the source server, so, created
a new certificate on source and then restored on to the new instance. I also created the master key on the new instance. I made a copy-only backup of the database and copied on to the server where new instance is located. When I tried to
restore the database I keep getting «No backupset selected to be restored» error. Restore Filelistonly and Restore Verifyonly check on backup file come back with no issues on the new instance. However, Restore Headeronly comes back
with error below.Msg 3013, Level 16, State 1, Line 118
RESTORE HEADERONLY is terminating abnormally.After this attempt failed, I created a blank database manually and enabled encryption on after running the following in the database context.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <cert name>Now I am trying to restore on top of the new database using SSMS but it is stuck at «Reading backup device header» file for more than an hour.
Please shed some light on this.
Thanks in advance.
Prasad Koukuntla
Microsoft SQL Server is a well-known database management system that helps to store and retrieve data from the database. SQL Server recommends users to create a backup of SQL Server Database regularly. If users need to restore an SQL database, it can be done easily in minutes. But, what to do if the backup (.bak file) is corrupted or can not be accessed? A large number of users are confronted with the “SQL Server No Backupset Selected To be Restored” error when restoring a database from .bak file. In this blog, we will let you know about the solution of how to fix no backupset selected to be restored and restore .bak file in SQL Server Database.
User Query
Hello, we are working with SQL Server 2012 from last 6 months in our organization. I have backed up the SQL database and obtained the dab.bak file. Now, I’m trying to restore SQL database from .bak file using SQL Server Management Studio. But the backup file is not restored and the error “No backupset selected to be restored” is displayed. I don’t know what I am missing. Please guide me on how to restore bak file in SQL Server. Thanks in Advance!
Reasons For SQL
Server No Backupset Selected To Be Restored Error
There can be two possible reasons behind this error message while
restoring .bak file in SQL Server.
- You can try to restore .bak file from the new version to the old version. For example, you took a backup on SQL Server 2012 and restore it on SQL Server 2008. You only restore the database from .bak file in the same SQL Server version.
- The backup file is corrupted or inaccessible. You can run this command to check
RESTORE HEADERONLY FROM DISK='D:/abc.bak'
If the backup is corrupted, you are not able to see the all details
in the output.
How to Restore
BAK File in SQL Server Which is Corrupted?
SQL Server does not provide any utility to repair & restore damaged backup file. To overcome this problem, you must use SysTools SQL Backup Recovery Software. This application specially designed to repair SQL Server corrupt backup file and restore .bak file in SQL Server Database. It works with all MS SQL Server version like 2017, 2016, 2014, 2012, 2008, and others to recover the database from backup. The software easily fix all bak file errors such as Error 3241, SQL Server Error 3154, SQL Server No Backupset Selected to Be Restored error etc.
With this software, you can repair a large size of SQL backup files with complete accuracy. It recovers entire data saved in .bak file which include MDF & NDF database file. After recovering the damaged data, it will display a preview of all database items like tables, views, stored procedures, etc.
Steps to Fix SQL
Server No Backupset Selected to Be Restored Error
Follow all the mentioned instructions to repair & restore corrupted bak file in SQL Server Database.
Step 1. Download and Launch the SQL BAK File Recovery Tool
Step 2. Click Open, and then click Browse to load the SQL Server .bak file
Step 3. Select SQL .bak file version, if you don’t know the exact version, simply click on Auto Detect option
Step 4. Click Multiple Backup File Option to add and restore .multiple SQL .bak files at the same time
Step 5. After selecting the files, click on the Recover button to start the recovery of the corrupted .bak file
Step 6. Once the recovery process is complete, a preview of all repaired database items will be displayed. Select the required items and click on Export option
Step 7. Under Export Options, choose Export to SQL Server Database or SQL Server Compatible Scripts option and fill the required information
Step 8. From the Select Destination Database option, choose “Create a New Database” to restore SQL Server backup to new database and “Export to Existing Database” to restore in an existing database
Step 9. Next, Select one option, Export with schema or Export with schema & data
Step 10. Click Export button to fix SQL Server No Backupset Selected to Be Restored error.
You can easily save the report in .csv file to the desired folder
location. To review the restored data, open SQL Server database.
Final Verdict
In this blog, we know the error and its causes when restoring the backup file in SQL Server. This error appears due to a corrupted backup file or restores in an older SQL Server version. There is no manual solution to repair damaged SQL backup files. Therefore, we recommend the professional solution that helps you to fix the SQL Server No Backupset Selected to Be Restored error. It is compatible with all Windows version including Windows 10, 8.1, 8, etc.
Frequently Asked Questions
How Can I Fix “The media family on device is incorrectly formed” Error?
If you see this error when restoring a backup file, you may be trying to restore a newer version of SQL Server. Sometimes this error can also mean that your backup file is corrupt. In such a case, you can use SysTools BAK File repair tool to recover data from a corrupted backup file.
Why I am Unable to Restore SQL Database Backup file?
There are several reasons behind the inability to restore the backup, such as trying to restore a database to older versions, the file is corrupted, does not execute the right command or the right steps.
How Do I Restore Backup File to New Database Without Losing my data?
This task is quite complex and risky by manually, so you can try SQL BAK restore tool that helps you to restore the SQL BAK file to different database.
I recently began migrating some databases from a production to a new pre-prod environment. Upon attempting to restore a rather large database I encountered an error after selecting the backup file stating No backupset selected to be restored. I had seen this error before but in the past it was due to selecting an incorrect file or filetype (often a transaction log file when a full backup file was expected).
After doing a little research I was able to pin-point two scenarios that can lead to this error being displayed.
The first scenario can occur if you are attempting to restore the database to a lower version of SQL Server. For example, if I took a backup in SQL Server 2017 and and attempting to restore to SQL Server 2012 I may see this error. It may be possible to get around this by adjusting the compatibility level of the database prior to running the backup if the versions of SQL are close enough.
The second, and more concerning scenario, can occur if the backup file is corrupted or otherwise unreadable. Though there may not be much you can do if this is the case, there is an easy way to confirm if there are any issues with the backup.
The following script can be used to query the backup header to determine if any corruption exists. You’ll want to update the location and backup file name of your specific database backup. The script should run pretty quickly, even for large database backups as it is only retrieving the header information and not actually performing any type of restore.
RESTORE HEADERONLY FROM DISK='D:Backupsmydatabase.bak'
To illustrate, the first record displayed below shows a backup that is corrupted or otherwise unreadable. This backups was created during a patching window and the server was rebooted before the backup completed.
The second record shows a backup that is intact. Despite the NULL values displayed, this backupset will restore without any errors.
If your backup is corrupted you’re not necessarily out of luck. There are some options available for repairing a corrupted backup, though that’s a different story for a different day…