Have you ever encountered the following error related to media set?

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

This error occurs when attempting to restore a database backup and not providing all the necessary media files. In this blog post, we will discuss the reasons behind this error and provide a solution to resolve it.

Understanding the Error

The error message indicates that the original backup was done as a striped backup, where the backup stream was split into multiple destination files. When restoring such a backup, you need to specify all the same files that were used during the backup process.

For example, if you backed up a database to 2 files, you must specify 2 files to restore from. Similarly, if you backed up to 5 files, you must specify 5 files to restore from. It is not possible to restore any data from less than the full set of files used for the backup.

Reproducing the Error

Let’s take a look at an example to understand how this error can occur. First, we will take a backup of a database:

USE master
GO

IF (db_ID('SQLAuthority') is NOT NULL)
    DROP DATABASE SQLAuthority
GO

CREATE DATABASE SQLAuthority
GO

BACKUP DATABASE SQLAuthority 
TO DISK = 'SQLAuth_Full_Part01.bak', 
   DISK = 'SQLAuth_Full_Part02.bak' 
WITH FORMAT

Now, let’s try to restore the database using only one of the backup files:

RESTORE DATABASE [SQLAuthority_1] FROM  
DISK = N'SQLAuth_Full_Part01.bak' 
WITH  
MOVE N'SQLAuthority' TO N'E:\DATA\SQLAuthority_1.mdf',  
MOVE N'SQLAuthority_log' TO N'E:\DATA\SQLAuthority_1_log.ldf'
GO

As shown above, the restore operation fails with the media set error.

Solution

To resolve this error, we need to identify the missing part of the backup. The following query can be used to find the missing part:

DECLARE @DatabaseName NVARCHAR(max)
SET @DatabaseName = N'SQLAuthority'

USE msdb;

SELECT DISTINCT t1.NAME
    ,t3.[media_set_id]
    ,t6.family_sequence_number
    ,t6.physical_device_name
    ,(datediff(ss, t3.backup_start_date, t3.backup_finish_date)) / 60.0 AS duration
    ,t3.backup_start_date
    ,t3.backup_finish_date
    ,t3.type AS [type]
    ,CASE 
        WHEN (t3.backup_size / 1024.0) < 1024
            THEN (t3.backup_size / 1024.0)
        WHEN (t3.backup_size / 1048576.0) < 1024
            THEN (t3.backup_size / 1048576.0)
        ELSE (t3.backup_size / 1048576.0 / 1024.0)
    END AS backup_size
    ,CASE 
        WHEN (t3.backup_size / 1024.0) < 1024
            THEN 'KB'
        WHEN (t3.backup_size / 1048576.0) < 1024
            THEN 'MB'
        ELSE 'GB'
    END AS backup_size_unit
    ,t3.first_lsn
    ,t3.last_lsn
    ,CASE 
        WHEN t3.differential_base_lsn IS NULL
            THEN 'Not Applicable'
        ELSE convert(VARCHAR(100), t3.differential_base_lsn)
    END AS [differential_base_lsn]
    ,t6.device_type AS [device_type]
    ,t3.recovery_model
FROM sys.databases t1
INNER JOIN backupset t3 ON (t3.database_name = t1.NAME)
LEFT JOIN backupmediaset t5 ON (t3.media_set_id = t5.media_set_id)
LEFT JOIN backupmediafamily t6 ON (t6.media_set_id = t5.media_set_id)
WHERE (t1.NAME = @DatabaseName)
ORDER BY t6.physical_device_name;

The media_set_id and family_sequence_number columns in the result can help identify if there is a split backup.

By understanding the cause of the error and using the provided solution, you can successfully restore a striped backup without encountering the media set error.

Do you use the striped backup feature in SQL Server? Let us know in the comments!