The Media Set Has 3 Media Families but Only 1 Are Provided

• Remove From My Forums

The media gear up has 2 media families only merely ane are provided.

• Question

Answers

• Greetings.

  I'm the PM that owns SQL backup.  I'll see if I can explicate this conspicuously and help everyone out hither.

  This error message ways that the original backup was done as a striped backup where the backup stream was split into two destination files.

  When attempting to restore, yous need to specify all of the same files which were used to take the backup.

  Considering this works very much like RAID1, we do not have ane ready of data on one file and some other set of data on the other i, only rather the information is inerspersed between the 2 files, which is the beliefs which gives optimum performace.  One of the unfortunate results of this is the fact that you cannot become whatever data out of a subset of the backup files used to create the backup.

  The reason that yous cannot do a backup calculation on to one of these files is that the files themselves are initialized to sympathise that they are role x of a y-office backup.  If you specify but 1 file, it doesn't fit.

  You can always perform a backup by specifying a new backup file, and using WITH INIT.

  If you don't demand to stripe the backups across multiple files for performance reasons, then I'd suggest going with a unmarried destination for management simplicity.

  So, the simple version is:

  If you lot back up to 2 files, you must specify 2 files to restore from.  If you dorsum up to v files, y'all must specify five files to restore from, etc.

  You cannot restore any data from less than the full ready of files used for the fill-in.  This isn't like RAID five where we tin can synthesize missing data, nor is information technology a continuation volume, where nosotros could restore up to the missing scrap.

  Kevin

• You can employ the following query to locate the missing parts (demo 2 parts):

  SELECT [media_set_id]    ,[family_sequence_number]    ,[media_family_id]    ,[media_count]    ,[logical_device_name]    ,[physical_device_name]    ,[device_type]    ,[physical_block_size]    ,[mirror]  FROM [msdb].[dbo].[backupmediafamily]    /*  media_set_id	family_sequence_number	media_family_id	media_count	logical_device_name	physical_device_name	device_type	physical_block_size	mirror 218	1	A899C345-0000-0000-0000-000000000000	1	Nil	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Fill-in\MediaFamily1.bak	two	512	0 218	2	94DC7E1C-0000-0000-0000-000000000000	i	Nothing	F:\temp\MediaFamily2.bak	2	512	0 */

  ---

  Kalman Toth
  New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

  • Marked equally respond by Friday, December 9, 2011 12:fifty PM
  • Edited by Kalman Toth Editor Friday, November ten, 2017 12:48 PM

• There are two ways to create multi-file backups with SQL Server:

  1) List a serial of files as the destination of the backup.  This creates a striped backup, exactly similar a RAID  0 disk array:  Information is evenly spread among the members of the set with no back-up.  If you pull one deejay out of a RAID 0 set, you've got nothing.  This is done purely for operation.  Large databases which take a long fourth dimension to support can cut the backup time to a fraction by spreading it beyond many unlike disks in this way.

  ii) use the "Mirror to" pick to create a duplicate set of fill-in media.  Each of the mirrors can exist either a single or striped backup fix.  In the case of a pure mirror fill-in (no striping), you get no performance benefits, just you get complete redundancy.  Much like a RAID 1 disk set. There is no operation proceeds, but if yous lose i disk, the other one is a consummate copy and continues on its mode.

  And so:  I'll agree that perhaps the SSMS GUI could make it clearer when you are creating a stripe set and when you are creating a mirror set.  I'd encourage you to become to http://connect.microsoft.com/ and file a bug on that.  Those bugs pop directly into our internal problems tracking DB.

  However:  Reality is that if you lot have created a striped fill-in set, and you merely take 1 part of it, you lot cannot recover from it.

• And below is an example for that. FORMAT is only needed first fourth dimension you address the backup files:

  Fill-in DATABASE pubs
  TO Disk = 'C:\x\pM1.bak'
  MIRROR TO Deejay = 'C:\x\pM2.bak'
  WITH FORMAT

  ---

  Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

  • Marked as reply by Kalman Toth Editor Monday, July 26, 2010 v:14 AM

• Basically, this bulletin is telling you that the backup of the database wrote its information to two files, non i.   Therefore, you demand both files in order to exercise a successful restore.  Due east.g.

  RESTORE DATABASE xyzzy
  FROM Deejay='path\file1', Disk='path\file2'

  If y'all have only one fill-in file from a 2 'media families' backup, then you lot only have half of the database backup.  This would mean that you cannot restore the database.  Merely if y'all have both files, so you tin restore them.

  An explanation of backup media can be plant at:

  http://msdn.microsoft.com/en-us/library/ms178062.aspx

  RLF

  • Marked as answer past Kalman Toth Editor Tuesday, Oct 12, 2010 6:twenty PM

• The name of the other files are not registered inside the file. You tin can verify that the backup was striped using a control like below:

  RESTORE LABELONLY FROM Deejay = 'C:\x\p2.bak'

  The FamilyCount column volition tell you over how many files the backup was striped. But as I said, the filename for the other file(s) is not bachelor inside this backup files. For this you lot demand to become back to the source server and check the fill-in history tables (in msdb).

  ---

  Tibor Karaszi, SQL Server MVP | web | blog

  • Marked as answer by Kalman Toth Editor Midweek, October thirteen, 2010 10:51 AM

richmondtamet1951.blogspot.com

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/abf50e00-c9b0-4809-9e61-43ed8a53e968/the-media-set-has-2-media-families-but-only-1-are-provided?forum=sqltools

Share this post