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)
i have taken backup a month ago and server is also set to closed where i have taken backup.
now i hvae same issue . Is there any workaround.
What’s the RESTORE DATABASE syntax to include two *.bak file names?
RESTORE database [BikeStore] FROM DISK=’D:\DBbackup\BikeStore_1.BAK’ with file=1, replace ,recovery
Error : The media set has 3 media families but only 1 are provided. All members must be provided.
Hi Pinal..What column/meta data table the Restore operation is referring ? and came to know it requires all 3 split backup files to complete the restore operation. I have checked in Restore HEADERONLY and Restore FILELISTONLY but no clue about total number of split backup files.
NOTE: I am doing this restore operation in Remote target Server. So there is no chance to refer the source server msdb.dbo.backup meta tables. so it might checking the total file count from the backup file itself. i don’t know what it is referring exactly.