The media set has 2 media families but only 1 are provided.

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

VERIFY DATABASE is terminating abnormally.

This is very common error, which generally comes with Restore and Backup. To dig into the problem lets stimulate this error.

Here I have a bakup file stored in D:\ drive, I want to use RESOTE common to verify the bak file.

RESTORE VERIFYONLY FROM

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

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

VERIFY DATABASE is terminating abnormally.

As I hit F5, I got this error, the error clearly specify, that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.

To locate the missing part you can use the following query.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = '<Media_UUID>'

Now Media_UUID you can get from

RESTORE LABELONLY FROM

disk = 'D:\SQL Server\myBakup.bak'

MediaName MediaSetId                          

----------------------------------------------

NULL      A335C850-4788-4A6B-BAE0-984AF5F4E6CC

Now here we have MediaSetId, we can use this mediaSetId to the above query to find the missing bak file location.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = 'A335C850-4788-4A6B-BAE0-984AF5F4E6CC''

physical_device_name

--------------------------------------------------------------------------------------

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak

D:\SQL Server\myBakup.bak

So here we found that the bakup was splited into two different file.  Now we can use both the RESTORE Command to use both the

RESTORE VERIFYONLY FROM

disk = 'D:\SQL Server\myBakup.bak',

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

The backup set on file 1 is valid.

Conclusion: You can msdb..backupmediaset and msdb..backupmediafamily table to find the missing file location.

The only gears that loom in coders dream are their love to code. Love you dear SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share