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

Sometimes when we try to restore database in Sql server we get below error:

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

In SQL Server 2008 R2 If you are using SSMS then you will get error window as below:

In SQL Server 2008 R2Using TSQL:

In SQL Server 2014 If you are using SSMS then you will get error window as below: In this case the backup was distributed into 3 files: We have provided 2 files but missed to add 3rd backup file.

In SQL Server 2014 TSQL:

This error message appears when the original backup was done as a striped backup where the backup stream was split into multiple destination files. And When you want to restore, you need to specify all of the same backup files which were used to take the backup. If not then you will get the above error.

So in order to solve above problem we have to provide the missing backup file and then restore it. In case we have lost that missing backup file then we'll not be able to restore that database until unless we take a fresh backup of that particular database.

To Restore the above database provide all 3 backup files using TSQL Or SSMS  it will be restored successfully:

E.g. Using TSql:

So this is how we can solve this problem.

How To Take Striped backup Of our Database In SQL Server:

Let see how can we take striped backup of our databases. Suppose, We have a sample database [StripedBackup]. Now we are  going to distribute the backup into 3 different files. We can use SSMS or TSQL code for same.

Usign TSQL you can take Striped backup as:

BACKUP DATABASE STRIPEDBACKUP
TO DISK = 'C:\BACKUP\STRIPEDBACKUP1.BAK',
DISK =  'C:\BACKUP\STRIPEDBACKUP2.BAK',
DISK =  'C:\BACKUP\STRIPEDBACKUP3.BAK'

If you want to take backup using SSMS then you can follow:

Right-click on the name of the database and then select 'Tasks'>'Back Up' Then add files as below screen shot and click ok.

So, this is how we can distribute our backup file into different files.

Thanks For Reading this Blog