"No backupset selected to be restored" SQL Server 2012

P

21

169

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.

Pecan answered 25/8, 2012 at 6:54 Comment(8)

Hasn't solved it yet. Seems that SQL Server configuration has problems. I think things should be more easier than this. – Pecan 25/8, 2012 at 7:50

Can you provide the T-SQL that you're using to attempt the restore? – Isabellaisabelle 25/8, 2012 at 13:14

Well, I'm using the GUI of SSMS. – Pecan 26/8, 2012 at 3:50

Even doing it that way, there should be a "script" button somewhere near the top of the window. After you set everything up the way you think it would be set for the restore, hit that instead of "ok" and it will bring up the T-SQL that would be run against the server for the restore. – Isabellaisabelle 26/8, 2012 at 15:8

Was permissions issue for me as well with SQL 2014 Standard (standalone) on WS 2012 R2 (workgroups) but had to reboot twice for the permissions 'take.' For what that's worth.. Hope you all get solved as well. – Postoperative 27/7, 2014 at 18:40

@BenThul - In my case, selecting the .bak doesn't give me any Backup sets to restore. So, it doesn't give me the option to select "Script." – Circadian 28/10, 2016 at 20:2

Can you try restore headeronly from disk = '«your file name here»';? If that doesn't work, what error do you get? – Isabellaisabelle 28/10, 2016 at 20:42

For permissions, you will need them for at least the service acct of the instance, and if you're using a Windows login, that acct too. (And I think there's something else in there too, but it eludes me right now.) – Exhilarant 15/6, 2017 at 22:8

S

198

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.

Sensitize answered 28/8, 2012 at 11:40 Comment(6)

What error message do you get when you try the restore using the SQL above? – Sensitize 29/8, 2012 at 9:20

Seems that restoring DB using T-SQL script would do the work. I think because we haven't backed-up the filestream data, UI can't handle it. – Pecan 5/11, 2012 at 12:7

Nice, I like the raw SQL approach. I get a message like ` The media set has 2 media families but only 1 are provided. All members must be provided.` -- does this mean that the backup source was different SQL Server version than backup target? When I try with the UI approach ("Restore"), I get message @ top No backupset selected to be restored. – Pizzeria 27/4, 2017 at 16:50

OK, I guess "The media set has 2 media families" is discussed here; – Pizzeria 27/4, 2017 at 17:43

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 – Lizliza 23/8, 2018 at 11:36

If anyone has same issue as me, and want to completely delete and recreate his DB, see this: #10300311 – Whitesmith 26/5, 2020 at 18:24

F

66

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.

Figueroa answered 9/11, 2012 at 3:11 Comment(3)

This ended up working for me too. It's SO DUMB that my username can have permissions to that folder, but the only way to get this to work was to add the group "Everyone" to the permissions on the folder. So frustrating, but thanks for saving me time! – Epicycle 23/9, 2013 at 13:6

Booya! Gotta love those generic errors that don't tell you anything. Of course it was permissions. Happened to me when restoring from sql servers on different domains. Changed the .bak file permission to everyone. Worked fine after that. – Sabbath 14/2, 2018 at 15:13

Had experienced same issues when trying to restore database from .bak file, which was created by file upload web service, which is running under IIS ApplicationPoolIdentity – Sundaysundberg 12/6, 2020 at 15:16

A

32

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.

Aeneous answered 5/1, 2016 at 15:56 Comment(6)

so if I take a db backup from machine with sql server 2014 enterprise - can it be restored on sql server 2014 express? – Wheat 12/9, 2017 at 9:47

@Wheat I think it depends of what kind of functionality you have in the DB. And the kind of backup. The express version does not recognize compress backups. Also, if the enterprise version is using something unique to enterprise the free version (express) will not recognize it. But, if you have something standard, theoretically it should be recognized. – Filamentous 13/9, 2017 at 6:14

@JuanAcosta yep, I put that together. In my case, there's no enterprise-specific functionality in use, so I restored the backup without problems – Wheat 13/9, 2017 at 7:10

@JuanAcosta can you point me to a documentation that says express edition can't restore compressed backup? – Wheat 13/9, 2017 at 7:16

@Chester please check page 234 point 3. The default option is the only one avaiable in express version. books.google.com.au/… – Filamentous 14/9, 2017 at 8:24

However, if you need a compress check this page. sqljunkieshare.com/2016/09/23/… – Filamentous 14/9, 2017 at 8:25

M

16

Run SQL Server Management Studio as an administrator (right-click the shortcut/exe, then select "Run as Administrator"), then try to restore.

Mauchi answered 25/9, 2014 at 20:28 Comment(0)

J

11

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.

Joselow answered 11/9, 2012 at 8:21 Comment(3)

This pointed me in the right direction for my problem. I received the same error as the original poster but it was because I was attempting to restore a backup from SQL Express 2012 to a SQL Server 2008 instance. Understandably this is not advisable, unfortunately the error wasn't descriptive enough to get at the root problem. – Tammeratammi 4/2, 2014 at 19:34

Wow! I've installed too many hot-fixes from the site but that error didn't went away. I saw this answer and logged with Sql Server 2012 instance and tried to restore, IT WORKED! Huh thank you :) – Launder 28/9, 2015 at 10:39

So you saying you cant restore from full version to express version? – Sextan 7/12, 2015 at 17:2

S

8

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.

Sputter answered 12/5, 2014 at 7:9 Comment(1)

I used the following article to create a new sysadmin user (see Section 2 using single-user mode): wikihow.com/Reset-SA-Password-in-Sql-Server – Whitesell 31/3, 2020 at 15:15

B

7

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.

Blamed answered 22/5, 2014 at 19:5 Comment(0)

S

7

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.

Slothful answered 12/1, 2016 at 14:41 Comment(0)

P

7

For me it's a user privilege issue. I logged-in with sa user and its working fine.

Pediform answered 4/7, 2017 at 9:43 Comment(0)

L

6

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".

Leiva answered 11/9, 2013 at 22:3 Comment(0)

G

5

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.

Grabowski answered 23/11, 2015 at 10:14 Comment(0)

U

4

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.

Uneducated answered 16/1, 2015 at 13:20 Comment(0)

F

3

For me, it was because the backup file was still open by another process. Here's the event log:

BackupDiskFile::OpenMedia: Backup device 'X:\Backups\MyDatabase\MyDatabase_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..)

Fuddle answered 22/8, 2014 at 20:5 Comment(0)

E

3

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 sa to the SQL server
Expand Security in Object Explorer
Expand Logins
Right click on the Windows user in question
Click on Properties
Select Server Roles from Select a page options
Check dbcreator role for the user
Click OK

Electrometer answered 18/9, 2018 at 0:46 Comment(0)

D

1

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.

Domash answered 15/8, 2014 at 19:13 Comment(0)

L

1

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

Lizliza answered 23/8, 2018 at 11:54 Comment(0)

I

0

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 Service\MSSQL$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.

Immoderate answered 25/8, 2012 at 6:55 Comment(0)

T

0

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.

Thumbstall answered 29/1, 2017 at 7:49 Comment(0)

H

0

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.

Hooper answered 31/8, 2017 at 19:20 Comment(0)

M

0

I have run into the same issue. Run SSMS as administrator then right click and do database restore. Should work.

Monet answered 3/10, 2018 at 18:48 Comment(0)

H

0

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.

Helotism answered 22/1, 2019 at 15:34 Comment(0)

Recommended topics

Hot tags