This blog is a response to a comment on our blog. Have you ever encountered below 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.
Here are the steps to reproduce the error. First, I would take backup of 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
And now, try to restore it.
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 below, it fails with error.
I hope you understood the reason of error. If you look at backup command, I have taken a striped backup on 2 files, but during restore I have given only one.
This error message means that the original backup was done as a striped backup where the backup stream was split into two destination files. When attempting to restore, you need to specify all of the same files which were used to take the backup. So, the simple version is: If you back up to 2 files, you must specify 2 files to restore from. If you back up to 5 files, you must specify 5 files to restore from, etc. You cannot restore any data from less than the full set of files used for the backup.
We need to find out where is part of this backup. Below is the query which can be used to find the missing part of the backup.
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;
Media_set_id and family_sequence_number can tell if there is a split backup.
Do you use this feature?
Reference: Pinal Dave (http://blog.SQLAuthority.com)