Search This Blog

Friday, November 20, 2009

Msg 3132, Level 16, State 1, Line 1

Today as soon as i reached office in the morning , an issue came up which was related to restoring of SQL Server database .The issue was that we were not able to restore one of the user database backups taken on some remote server .

The error we were getting was :

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

There was some .bak say test.bak file, which we were trying to restore and getting the error above.

This error says that there are 2 media families and only 1 has been provided .
Media families here means a Disk or a tape .If we take it as disk , then it means that "The backup was taken on two backup devices or say backup files (in case you have not created disk devices)" . This kind of backup is called as a srtipped backup where both the backup devices have some part of backup but not complete.

But the guy who took the backp says that he did nothing special that made the stripped backup i.e. he did not take the stripped backup at all .

Then how come the stripped backup took place on it own ....this was pretty strange.

I checked this issue through Bing and google and most of the people were saying the same that they never did intend to take the stripped backup .

Now, i had to prove the client that we are missing one backup device here or we are missing one .bak file , I did a small repro :

USE master
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue.bak'

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue_1',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue_1.bak'

backup database testdba to rest_issue,rest_issue1
restore database testdba from rest_issue <-- used only one mediaset

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

restore database testdba from rest_issue,rest_issue1 <-- used both mediaset

Restore was successful..
So the error was genuine and working fine .

Further ,If you think that you will restore only 1 file i.e. data file and leave the log file using option partial , even that will not work as the backup for data file is also stripped.

Restore filelistonly will tell you the files in the backup set

Restore headeronly will give us the MediasetID number and other information

Restore labelonly will give us the BindingID number and other information

Restore verifyonly will fail with the same error if you use it on one file as it needs both the files

Solution :There is no solution to this issue you need to find out another device else it will not work (use restore headeronly and labelonly ,backupmediafamily, backupset for assistance) and use both the files to restore .Once you have it , you can issue this

command :
Restore database ibmdba from anoop_issue,anoop_issue1 with replace
OR Restore database ibmdba from disk = , disk = with replace

Why Hhis happens :)

This will happen if :

1) You have created two(or more) backup devices and taken backup on 2
(or more) devices or you can say stripped backup and while restoring yo uare using only one .

2) You are using GUI to take the backup without seeing that the backup is going to more than 1 device or files .If you select both the devices/files it will take stripped backups .If you insert 2 devices / files but select NONE of them then also it will happen (That should not happen and should give us the error "Please select a device") .And thats where the bug is .

So the BUG is :

If a user do not select a device in the backup set when more than one devices are existing , by default SQL Server selects all the devices and not giving the error "please select the device"....

If this default behaviour is by design then its not the correct design and should be changed .

Repro of this issue through GUI :
-> right click adventure works database > Tasks > backup
-> Under Destination remove anything if its mentioned by default.
-> Click on Add > a new box will pop up > select backup device you have created
-> you will get the device name > click on OK.
-> Again click on Add >a new box will pop up > select another backup device .
-> You will see that both the backup devices are unselected now (",).
-> If now you click ok it will take the backup .Ideally it should give the error that "please select the device".

I have filed a bug with MS through connect...

Happy learning

1 comment:

Anonymous said...

I was so glad to trip across this post! From filelistonly, it looked like I had the right media, but when I used headeronly it revealed that the backup file was looking for two different media. Based on the BackupName, I believe the client did not select a specific backup device as described in your BUG above and so the .BAK file they sent needed both the backup device set up for my company and their in-house one. Thanks for the tip and explanation!