none
The media set has 2 media families but only 1 are provided.

Answers

  • Greetings.

    I'm the PM that owns SQL backup.  I'll see if I can explain this clearly and help everyone out here.

     

    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.

     

    Because this works very much like RAID1, we do not have one set of data on one file and another set of data on the other one, but rather the data is inerspersed between the two files, which is the behavior which gives optimum performace.  One of the unfortunate results of this is the fact that you cannot get any data out of a subset of the backup files used to create the backup.

     

    The reason that you cannot do a backup adding on to one of these files is that the files themselves are initialized to understand that they are part x of a y-part backup.  If you specify only 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 need to stripe the backups across multiple files for performance reasons, then I'd suggest going with a single destination for management simplicity.

     

    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.  This isn't like RAID 5 where we can synthesize missing data, nor is it a continuation volume, where we could restore up to the missing bit.

     

    Kevin

    Saturday, July 14, 2007 12:48 AM
  • You can use 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	NULL	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\MediaFamily1.bak	2	512	0
    218	2	94DC7E1C-0000-0000-0000-000000000000	1	NULL	F:\temp\MediaFamily2.bak	2	512	0
    */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, May 10, 2011 1:57 PM
    Answerer
  •  

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

    1) List a series of files as the destination of the backup.  This creates a striped backup, exactly like a RAID  0 disk array:  Data is evenly spread among the members of the set with no redundancy.  If you pull one disk out of a RAID 0 set, you've got nothing.  This is done purely for performance.  Large databases which take a long time to back up can cut the backup time to a fraction by spreading it across many different disks in this way.

     

    2) use the "Mirror to" option to create a duplicate set of backup media.  Each of the mirrors can be either a single or striped backup set.  In the case of a pure mirror backup (no striping), you get no performance benefits, but you get complete redundancy.  Much like a RAID 1 disk set. There is no performance gain, but if you lose one disk, the other one is a complete copy and continues on its way.

     

    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 go to http://connect.microsoft.com/ and file a bug on that.  Those bugs pop directly into our internal bug tracking DB.

     

    However:  Reality is that if you have created a striped backup set, and you only have one part of it, you cannot recover from it.

    Saturday, October 18, 2008 4:09 PM
  • And below is an example for that. FORMAT is only needed first time you address the backup files:

    BACKUP DATABASE pubs
    TO DISK = 'C:\x\pM1.bak'
    MIRROR TO DISK = 'C:\x\pM2.bak'
    WITH FORMAT


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 08, 2010 9:38 AM
    Moderator
  • Basically, this message is telling you that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.  E.g.

    RESTORE DATABASE xyzzy
      FROM DISK='path\file1', DISK='path\file2'

    If you have only 1 backup file from a 2 'media families' backup, then you only have half of the database backup.  This would mean that you cannot restore the database.  But if you have both files, then you can restore them.

    An explanation of backup media can be found at:

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

    RLF

    Tuesday, October 12, 2010 5:54 PM
  • The name of the other files are not registered inside the file. You can verify that the backup was striped using a command like below:

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

    The FamilyCount column will tell you over how many files the backup was striped. But as I said, the filename for the other file(s) is not available inside this backup files. For this you need to go back to the source server and check the backup history tables (in msdb).


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, October 13, 2010 6:54 AM
    Moderator

All replies

  • I'm having the same issue-

    I successfully completed creating the actual backup file the first time I attempted this but when I tried to restore the file on the second server, I experienced the same error.  Afterwards, I tried to recreate the backup and I was not able to create the backup- instead getting virtually identical message on the source server (failed to create backup- then everything else is the same).  SQL provided me with the following link :

    http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

     

    Anyone out there with any insight?  I can't make a simple database backup with 2005?  There must be something I'm missing.

    Saturday, February 25, 2006 9:30 PM
  • Try to list the number of media from the backup using RESTORE FILELISTONLY. Search under books online for other RESTORE statement utilities.
    Monday, February 27, 2006 10:05 AM
    Moderator
  • this is resolved, I backed up the database again and restored and it worked fine the second time...
    Monday, February 27, 2006 1:06 PM
  • I am also having the same problem. I've tried 2 separate backups with the same results. And I know for a fact that there should be only 1 media set. The backups can be restored to the original server.
    Thursday, May 11, 2006 12:33 AM
  • I'm still having a problem with this. Can someone enlighten me?
    Tuesday, May 23, 2006 5:16 PM
  •  I receive the same error message from a SQL Backup file that I am working with.  Do I need to create another backup file?  Can I use the exsisting file?

    Thank you!

    Error reads as follows:

     TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------

    Restore failed for Server 'BIGDY\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Thursday, June 22, 2006 3:38 AM
  • Hello! Have the same problem. Are there anybody who can provide an answer?
    Monday, November 06, 2006 10:57 AM
  • I had the same problem. Here was my solution. 

    1. When you make the backup, change the Backup Set Name.

    2. Remove all of the destinations listed, and add 1 new destination.

    3. Do a full backup.

    It should restore normally.

    If you check the contents of the backup when you go to restore it, if it says it contains 2 media families, you will probably have this problem.  I'm no expert, but this worked for me.

    Good luck.
    • Proposed as answer by DominicL Friday, July 26, 2013 8:08 PM
    Tuesday, November 14, 2006 9:38 PM
  • Thank you! Actually, restoring from new backup went normally. Just when i made a first backup i thought destination listed to be optional.... :)
    Friday, November 17, 2006 2:52 PM
  • anyone who can provide a resolution for others who only have the backup file?
    Monday, December 11, 2006 10:22 AM
  • You actually need two backup files to do a restore if you have two destinations configured. Highlighting one of the backup destination does not mean that it will backup to that single destination. It creates a Media Family, with the first item in the list being "Media 1, Family 1" and the second item being "Media 1, Family 2". To do a restore you need both of the files listed in your destinations list as your backup is spanned across those two files.  

    Your Destinations shows:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mybackup.bak

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\otherbak.bak

    So to do a restore you need:

    mybackup.bak and otherbak.bak

     

    • Proposed as answer by angelktpi Tuesday, January 22, 2013 4:29 PM
    Tuesday, December 12, 2006 10:58 PM
  • Hi Glate:

    I had the exact problem, and after reading your post, I was able to restore my database.  Here are the steps:

    1. Right click on the database and select Tasks/Restore/Database option from the shortcut menu
    2. Click on the "From Device" radio button, then click on the ellipse button
    3. In the "specify Backup" window, click on the "Add" button to browse and find the backup file you created.  If you did not save the your backup file as xxx.BAK, then you have to select "All Files" from the "Files of type" drop down options in order to be able to see the backup file
    4. Click OK twice, then click on the Restore checkbox to select the file in the "Restore Database" windows in the "Select the backup sets to restore" section.
    5. Click on the "From database" radio button and select the database name you are trying to restore.
    6. In the "Options" section, select the checkbox "Override the existing database"
    7. Finally, click on the "OK" button to start the restore
    8.  Finally, click the "OK" button to start the
    Monday, March 05, 2007 11:35 PM
  • Hello,

    I am getting the same error in this scenario... when in the 5th step I selected a new database from "to database" dropdown.

    I see (from RESTORE LABELONLY) that the file I have, has the following attributes:

    Family count: 2, Family sequence: 2.

    So I suppose that the file with sequence 1 is missing. However, what can I restore from this only file? Can I somehow restore at least table definitions? It was a huge database (and I think I have equaly large file )

    Thank you very much.

     

    Paul

    Friday, March 16, 2007 9:23 PM
  • I got this message when trying to restore a backup that I had done manually, I made the mistake of having two entries in the destination and so it striped the data.

     

    To make things more complex I am restoring to another database name and in a different folder.  I have already sucessfully restored the a BAK file and created both the MDF and LDF files, I then successfully restored and the first TRN file.  It is just this next transaction log which is split across the two files that is a problem.

     

    Can you please let me know the syntax for restoring these multiple files.

     

    I would have thought it would be something like

     

    RESTORE LOG MyDatabase FROM DISK
      = 'J:\SQLBackups\MyDB200704030907a.TRN'   ,       'J:\SQLBackups\MyDB200704030907b.TRN' 
    with move 'Mydatabase_Log' to 'J:\MyDBRestore\MyDBRestore.LDF',
     NOUNLOAD , STATS = 10, RECOVERY , STOPAT = '4/02/2007 3:20:00 AM'

    but it does not like the comma between the two files, I have tried nothing as well as the word AND as well as just listing the two files.

     

    So what is the syntax for such a restore?

    Tuesday, April 03, 2007 11:23 AM
  • The syntax for a multiple file restore is

     

    RESTORE LOG [DATABASE NAME]

    FROM

    DISK = 'Path',

    DISK = 'Other Path'

    WITH

    All your other options.

     

    You shouldn't need to specify with move on the restore as this should have been specified when you processed the restore of the actual database files

    Tuesday, April 03, 2007 12:48 PM
  • Greetings.

    I'm the PM that owns SQL backup.  I'll see if I can explain this clearly and help everyone out here.

     

    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.

     

    Because this works very much like RAID1, we do not have one set of data on one file and another set of data on the other one, but rather the data is inerspersed between the two files, which is the behavior which gives optimum performace.  One of the unfortunate results of this is the fact that you cannot get any data out of a subset of the backup files used to create the backup.

     

    The reason that you cannot do a backup adding on to one of these files is that the files themselves are initialized to understand that they are part x of a y-part backup.  If you specify only 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 need to stripe the backups across multiple files for performance reasons, then I'd suggest going with a single destination for management simplicity.

     

    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.  This isn't like RAID 5 where we can synthesize missing data, nor is it a continuation volume, where we could restore up to the missing bit.

     

    Kevin

    Saturday, July 14, 2007 12:48 AM
  • Hi all, I´m with the same problem when I try to perform backup database, like this:

    [code]
    BACKUP DATABASE [SpyPhone] TO  DISK = N'C:\SQLServer-Backup-Test\BEN10-ALL-Database-Differencial',  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Differencial\BEN10-ALL-Database-Differencial' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'SpyPhone_backup_20070731115540', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    [/code]

    I got the following error:

    [code]
    Msg 3132, Level 16, State 2, 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
    BACKUP DATABASE is terminating abnormally.
    [/code]

    What I do to solve this problem?
    Tuesday, July 31, 2007 2:59 PM
  • No one has given the right answer in solving this error. This is the right answer:

    I'm using SQL Server 2005. There is an option to split a backup into as many files as you want. This is defined in the Destination space at the bottom of General screen of the Backup Database window. You can choose multiple locations. If there is more than one defined, and then you perform a restore, SQL Server will ask for the location of all the destination backups, hence the 2 media families but only 1 provided. So delete the existing backup, create a new one, and ensure there is only one destination path for the backup. Once finished, you can restore without a problem.
    Friday, October 26, 2007 3:13 PM
  • What confused me here is that Destination is a list.  I was thinking I was backing up to the destination I had highlighted when I'm actually backing up to ALL of them.

     

    Then, when I went to restore, I was only specifying the file I had highlighted during the backup, and, of course, I got the message.

     

    To avoid the "family" effect, remove all lines from the Destination list except one when you do the backup.

     

    Hope this helps.

    Tuesday, November 20, 2007 3:18 PM
  • hi

    I found same problem, this problem happen when you use more than one destination in backup process. If you need to use this problem backup file (like me) you must use all backup which you use to backup by add these backup files in to backup device (in restore dialog) and continue restore process

     

     

    hope this useful.

    thank

    Friday, February 01, 2008 6:00 AM
  •  

    I had the same problem

    the reason was that my backup was splitst in two files, one in backup device and one in ":\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\[database].bak"

    first i added the file from the directory above  and then my backup device and then problem was solved 

    Friday, February 22, 2008 5:25 PM
  • I had the same problem, patrick_henry_1776 's solution worked for me. THANKS!

     

    Monday, March 03, 2008 3:17 PM
  • During backup you should delete any previous entries in the "destination" textbox, then add new destination of backup file. When restoring, select that destionation, and everything will be fine.

     

    Sunday, April 27, 2008 6:48 PM
  •  

    Thanks Dude ! Yours was the perfect Answer....
    Saturday, May 03, 2008 9:14 AM
  •  payamtr wrote:

     

    I had the same problem

    the reason was that my backup was splitst in two files, one in backup device and one in ":\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\[database].bak"

    first i added the file from the directory above  and then my backup device and then problem was solved 

     

    Thanks a lot. Your comments has solved my problem

    Friday, June 27, 2008 2:21 AM
  •  

    Thank you all
     I want your views and helps

    Suppose one day your harddisk on which SQL Server 2005 with an important database named ( MashSQL ) is damaged .

    Can you and the other comrades guide me what the steps to take a backup copy of this database
      and put it on a CD or a DVD to restore it on the new hardisk?

    It has happened with me unfortunately , my hardisk damaged and I had a copy of the backup ( MashSQL .bak) taken from
    folder ,....  \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\[MashSQL].bak" reserved in a flash memory.

    When i tried to restore the database on the new harddisk,
     the error Message (The media set has 2 media families but only 1 are provided.)

    What are the mistakes i v done , what are the right steps to solve such case ?

    Thank you
    ShamiDB

    Tuesday, July 08, 2008 4:08 PM
  • This entire error and issue with SQL Server is just ridiculous by Microsoft.  Lets say you don't care about logs.  Lets say this is a fairly new database and so what, I don't have my log file or whatever.  I want SQL Server to still restore my damn .bak and it can't.  Why can't it create a default log for you with nothing in it to "make it happy" if you do not care about the log file in my particular case.  So what, I'm screwed?  That's hard to believe.  There has to be a work around for this if you are stuck with a .bak  you can't restore for this ridiculous limitation and 2 file format.  There has got to be a way you can tell it to ignore the 2nd file even if that means you loose all your logs.

    WTF!
    Saturday, August 09, 2008 5:35 AM
  • >>> This error message means that the original backup was done as a striped backup where the backup stream was split into two destination files.


    Uh, ok.  So what do I care as a person who's just right-clicking and creating a backup and I am relying on SQL's default way of doing so?  Lets say I am not a DBA and I do not assume I have to tweak anything and expect to be able to restore it later.  One would not expect to go through the hoops you are stating just by using the out-of-box right-click in context menu and backup  your DB.  That's ridiculous.


    And lets make one thing very clear.  I created this DB.  Never did I specify or set it to output to 2 files.  That's not something I would even want to do for a blog database...it's Subtext.

    Saturday, August 09, 2008 5:41 AM
  • My solution:


    1. create backupdevice <ss> which points to your bak file
    2.RESTORE FILELISTONLY
    FROM  ss with file=1

    GO

    Saturday, August 16, 2008 4:07 PM
  • Kevin,

     

    I was regularly doing backups to both the local default path and also to an external network drive.  When the C-drive with SQL Server (with its default-path backups) crashed, I reinstalled SQL Server (onto the replacement C-drive) and then attempted to restore the database using the BAK file saved to the external drive.  I'm now getting this "media set has 2 media families but only 1 are provided" message.  Isn't there some way to retrieve anything from these surviving network backup files???

     

    The contents of these TWO backups were supposedly identical.  I had no intention of "striping" the data across two files... or accross the network!  I can't even imagine most DBAs having a reason to do so, except to separate files like mutually dependent nuclear codes, so that one is useless without the other!  How many people really need to do that???  And this is the DEFAULT action???

    Tuesday, October 07, 2008 7:35 PM
  • hi i have same problem

    ....
       help me plz.....
                ............................
                         system.data.sqlclient.sqlerror:the media set has 2 media families but only 1 are provided .all members must be provided.(microsoft.sqlserver.express.smo)...........
                   i have no idea about that please send me the detail
    Saturday, October 18, 2008 3:07 PM
  •  

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

    1) List a series of files as the destination of the backup.  This creates a striped backup, exactly like a RAID  0 disk array:  Data is evenly spread among the members of the set with no redundancy.  If you pull one disk out of a RAID 0 set, you've got nothing.  This is done purely for performance.  Large databases which take a long time to back up can cut the backup time to a fraction by spreading it across many different disks in this way.

     

    2) use the "Mirror to" option to create a duplicate set of backup media.  Each of the mirrors can be either a single or striped backup set.  In the case of a pure mirror backup (no striping), you get no performance benefits, but you get complete redundancy.  Much like a RAID 1 disk set. There is no performance gain, but if you lose one disk, the other one is a complete copy and continues on its way.

     

    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 go to http://connect.microsoft.com/ and file a bug on that.  Those bugs pop directly into our internal bug tracking DB.

     

    However:  Reality is that if you have created a striped backup set, and you only have one part of it, you cannot recover from it.

    Saturday, October 18, 2008 4:09 PM
  • You will also get this error if you are trying to restore a differential backup on a different server when you are expecting a full backup file.  If you check the backup with Restore FileListOnly and check the results for the DifferentialBasexxx/Differential GUIDxxx fields, if these have entries the backup is a differential one, not a full one...

     

    Just been caught out today...

    Thursday, November 13, 2008 3:29 PM
  • This is gettig frustrating, my error messages are the same, but the problem is a bit different.
    I have the database in working order. I have taken backup of the database and I can import it, detach it, restore it you name it.
    But right now, when our  "salary" database system is trying to update it self it wont because of the "The media set has 2 media families but only 1 are provided" error message.
    Is there are way to see what media set and what its looking for with commands like

    restore database System4 from DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\system.bak'
    restore filelistonly from DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\system.bak'

    Wednesday, January 14, 2009 7:48 AM
  • And after 10 minutes off hot water... I found a solution for my problem.. any how.
    It seemed that the program didnt care what names I put on my backups.. It just wanted to use system.bak.
    Renamed that to oldsystem.bak used on of the other backups I had and the upgrade worked.
    • Proposed as answer by Virtual.Offis Thursday, April 02, 2009 1:54 AM
    Wednesday, January 14, 2009 8:45 AM
  • We had the same issue, and this is how we resolved our problem.
    Senario: We moved 3 database from SQL 2005 Express to SQL 2005 server.
    We originally configured SQL to perform a daily database dumb- Lets call this OLD BACKUP.bak. On the day we performed the moved we manually backed up the databse using SQL studio to a differnet location-Lets call this CURRENT BACKUP.bak. In the backup option we selected "Back up to a new media set, and erase all existing backup sets". When we created the new database on the new SQL server, and performed the restore, we received the aforementioned  error.

    Solution:
    In order to restore the database we need to add both the OLD BACKUP.bak and CURRENTUP.bak.
    1) Open Management Studio, select the database to restore, right click the database, select Task/Restore/Database
    2)
    In the General tab, click on the From device radio button and then click on Add, browse to your old backup.bak and then OK.
    3) click on the Add button again, and browse to your current backup.bak and then OK, then OK again. (the sequece of the files does not really at all). You can view the family media by highlighting the file extension in backup location and clciking on Contents.
    4) Click on the radio button Restore.
    5) In the options tab, on the far left, select the radio button Overwrite existing data, Then click on OK, hopefully your progress icon starts to go green.

    Phanh
    • Proposed as answer by Virtual.Offis Thursday, April 02, 2009 2:29 AM
    Thursday, April 02, 2009 2:29 AM
  • Greetings.

    I'm the PM that owns SQL backup.  I'll see if I can explain this clearly and help everyone out here.
    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.

    .............................................
    .............................................

    Kevin

    I have the same issue, but in my case, unfortunately I don't have the second file which I am not sure it was, I backed up the DB to my desktop and got 1 file, then I reinstalled my OS deleting everything except this file.
    You really wanna tell me that I am a dead man now????

    I have got only 1 *.bak file.
    Shimmy
    Monday, April 06, 2009 1:36 AM
  • Hi Shimmy,

    If you give 2 locations when you were doing backup, then it should be 2 file, one *.bak and other one without file extension.Then  you must add both location when restoring.

    wqwert
    Wednesday, May 13, 2009 9:49 AM
  • I like to compare it to disks and striping. Say you have a RAID 0 consisting of two disks. You lose one disk. You now don't expect to be able to get anything out of the remaining disks. That is striping. Same with striped backups.
    Tibor Karaszi
    Wednesday, May 13, 2009 6:07 PM
    Moderator
  • Very nice. Simple and clear.
    Thursday, July 02, 2009 7:09 PM
  • Hi Tibork, Kevin Farlee etc,

    You seem to be missing the point. None of the people in this thread intentionally striped their backups. SQL Server somehow did it without asking them. They have now lost their data because of this. 

    Now a simple fix would be to have a confirmation dialog asking people if they want their backups striped. Obviously most people don't. You are representing MSFT. It is in your interest to have a functional SQL Server product. There are alternatives.

    Friday, August 07, 2009 5:00 AM
  • Hi,

     

    is it possible to make multiple backups then? i thought that adding destinations would mirror the backup files. How can i activate the "mirror to" option? I want to have 2 full copies of the backups.

     

    Tuesday, June 08, 2010 9:03 AM
  • The GUI doesn't expose the mirror functionality. For that you need to type your backup command in a query window and use the MIRROR TO option for the backup command.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 08, 2010 9:36 AM
    Moderator
  • And below is an example for that. FORMAT is only needed first time you address the backup files:

    BACKUP DATABASE pubs
    TO DISK = 'C:\x\pM1.bak'
    MIRROR TO DISK = 'C:\x\pM2.bak'
    WITH FORMAT


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 08, 2010 9:38 AM
    Moderator
  • great

     

    thanks a lot

     

    Gabriel

    Tuesday, June 08, 2010 9:50 AM
  • Thank you so much

    Nice  and Helpful

    Alex

    Sunday, July 25, 2010 2:17 AM
  • Hi Tibork, Kevin Farlee etc,

    You seem to be missing the point. None of the people in this thread intentionally striped their backups. SQL Server somehow did it without asking them. They have now lost their data because of this. 

    Now a simple fix would be to have a confirmation dialog asking people if they want their backups striped. Obviously most people don't. You are representing MSFT. It is in your interest to have a functional SQL Server product. There are alternatives.


    If you don't remove the current file from the GUI backup General Page Destination Back Up To (File) window and you add a second (or more) file, the split/striped backup happens. Everything may go OK and the user may think that a new backup file created, instead a split backup took place.

    Workaround: REMOVE current file from window prior to entering new filename.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, July 26, 2010 5:30 AM
    Answerer
  • Thank you for this great explanation! I wish all forum responses were as clear ;-)

    Michaela

    Tuesday, July 27, 2010 8:43 AM
  • Thanks Kevin, it really help :)
    Wednesday, September 22, 2010 10:42 AM
  • Hi,

    Kinldy check your physical backupfile--If any one of the backup file is missing and still you are going to filre the query with your script you may encounter this type of problem.

    eg->I have used the queries (Note Just Iam giving an ex for understanding only)

    backup database <test> to

    disk='d:\n1\te1.bak',

    disk='e:\n2\te2.bak' with init,stats=10

    fire the query you will see the backup file in the specifid path, now come to the point go ahead and delete the backup file eg- I have deleted the te2.bak then fire the query

    backup database <test> to

    disk='d:\n1\te1.bak',

    disk='e:\n2\te2.bak' with init,stats=10

    you will get an error Bcz the aold entries were still exists in the Backupmediafamily(table) in MSDB database solutione go for the Format option --This will overwirte the backup media family of the entries.

    like use

     

    backup database <test> to

    disk='d:\n1\te1.bak',

    disk='e:\n2\te2.bak' with Format,stats=10

    This will works...

     

    The above which i told may be we need to look at physically--not only this option to look,some time if any recent modifcaion done on your mount point level or Disk etc..it causes severly..........

    Regards,

    RamaUdaya.K

    E-mail -rama38udaya@gmail.com

     

    Tuesday, October 05, 2010 2:07 PM
  • hey can u plz help me out

    i have followed the same steps as u specified till step 4

    bt after that when i click on the "from database" option, it doesnt take any database name as input.

    now what do i do???

    plzz help me plzzzzzzz

    Sunday, October 10, 2010 4:28 PM
  • Sunday, October 10, 2010 8:58 PM
    Answerer
  • cant do it

    actually the main problem is that i just have only the backup of the database n the system where i created the database is crashed now.. so i cnt recreate the backup also.

    plzz give a simple solution how to restore the databse from this problem.

    Monday, October 11, 2010 9:40 AM
  • Hi Ritu,

     

    What exact problem you are facing, did you mean you have the backup file of the Database

    and the Database crashed on the server or server Crashed? Let us know what error you have got Is the database is in Suspect mode??

    Regards,

    RamaUdaya.K

    Monday, October 11, 2010 3:15 PM
  • actually the computer where i made the database is not with me,so i dono have the database so that i can back up again the database.

    All i have is just the Backup of my database(created in sql server 2000) n everytime i try to restore it in my new system(which has sql server 2005) i get the following error

    TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------

    Restore failed for Server 'KHURANA\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    And now I am not able to understand this and practically it is not possible to create the databse again.

    So if You can help,I would be really obliged

    Regards

    Ritu

    Tuesday, October 12, 2010 5:01 PM
  • Basically, this message is telling you that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.  E.g.

    RESTORE DATABASE xyzzy
      FROM DISK='path\file1', DISK='path\file2'

    If you have only 1 backup file from a 2 'media families' backup, then you only have half of the database backup.  This would mean that you cannot restore the database.  But if you have both files, then you can restore them.

    An explanation of backup media can be found at:

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

    RLF

    Tuesday, October 12, 2010 5:54 PM
  • Mostly  the above situation happens unintentionally (the backup window not cleared prior to entering the new  backup file name). The challenge is to find the other file. It is probably a piggy-back situation: Alpha 1/2 backup is in file Omega.bak. The other 1/2 is in file Alpha.bak.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, October 12, 2010 6:23 PM
    Answerer
  • yes i understood the problem but as said by SQLUSA this was done unintentionally.

    So how do i find the 2nd file now

    Wednesday, October 13, 2010 6:41 AM
  • The name of the other files are not registered inside the file. You can verify that the backup was striped using a command like below:

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

    The FamilyCount column will tell you over how many files the backup was striped. But as I said, the filename for the other file(s) is not available inside this backup files. For this you need to go back to the source server and check the backup history tables (in msdb).


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, October 13, 2010 6:54 AM
    Moderator
  • Kalman,

    Yes, it is redundant. But considering the physilogical aspect here, the clearer the msaage is, the better. By the physiological aspect, I mean that anybody enountering this *don't want* this to be the case. So, one try to find loopholes (especially if the source database is no longer available). Since there are no loopholes, I believe it is an advantage to communicate the message as loud and clear as possible.

    It's like asking "Can I do this?". One person say no. The questioner will probably keep asking. If 100 persons all say no, then the questioner will probably go seek more productive means to achieve whatever the end result is meant to be.


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, October 13, 2010 1:24 PM
    Moderator
  • i again made a database in sql server 2005 and created a backup file of the database but cannot restore that back up file  with the following error message-

    TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------

    Restore failed for Server 'KHURANA\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'as' database. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Y is this error coming?? Can anyone help??

     

    Thursday, October 14, 2010 2:58 PM
  • Hi,

     

    If you have the Backup file of all the Files oncluded in that..

     

     try the below Option

    From-> Restore database form - select options "Overwrite the existing database"  while restoring...

     

    Note ==>When ever you are going to restore any database from one server to another you have to follow some guilde lines.

    >>Ensure that what ever thebackup file you have it is valid one. you have the right command to use for the Validation(go for the Books online for the restore command--Ieven after restoring the Database also you need to run the DBCC CHECKDB on that ensuring no inconsistency and check for the SQL error log ... hope you have got it................

     

    Regards,

    Rama

    Friday, October 15, 2010 4:42 AM
  • Hello

    As u mentioned to do the "Overwrite the existing databse",by doing this I could restore it in my system but still not able to do itn on any other system.

    Saturday, October 16, 2010 7:21 AM
  • It is much easier to discuss these things around TSDQL command instead of GUI. Remember that you can script the RESTORE command in the GUI. This option is btw the same as the REPLACE option for the RESTORE command. If you have difficulties on other systems, then we need to see the exact error message you get on that system. Consider opening a new thread for that, this is becoming crowded.
    Tibor Karaszi, SQL Server MVP | web | blog
    Saturday, October 16, 2010 9:54 AM
    Moderator
  • Hi,

    I am currently using SQL Server 2008, i have backed up one Database in Server1 and copied that backup file in Server2.

    Now Server1 is down & i am trying to Restore database in Server2. I am getting the following error message

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Express.Smo)

    Can anyone please let me know is this a valid scenario? if yes, can you please help me with a solution. I tried implementing few of the solutions provided above but none of them worked!!

    In my case, both the servers have SQS 08.

     

     

    Tuesday, October 26, 2010 6:13 AM
  • this really helps me a lot. Thank all of you here.
    Wednesday, October 27, 2010 3:10 AM
  • Thanks for your help
    Wednesday, November 03, 2010 12:11 PM
  • tell me how to fix that problem...

    1 Media file is missed among 2 media files

    Tuesday, May 10, 2011 9:44 AM
  • You need to find both backup files in order to be able to restore.
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, May 10, 2011 10:54 AM
    Moderator
  • You can use 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	NULL	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\MediaFamily1.bak	2	512	0
    218	2	94DC7E1C-0000-0000-0000-000000000000	1	NULL	F:\temp\MediaFamily2.bak	2	512	0
    */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, May 10, 2011 1:57 PM
    Answerer
  • Hi, Thanks. It helped me
    Friday, December 09, 2011 12:42 PM
  • USE ONLY MASTER Database to perform Below

    -------------------------------------------------

    --Back Up

     

    USE MASTER

     

    BACKUP DATABASE [AdventureWorks] TO 

          DISK = N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak'

          WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Full Database Backup',

          SKIP, NOREWIND, NOUNLOAD,  STATS = 10

     

     

    BACKUP DATABASE MyDatabase

    TO DISK='E:\MyDatabase.bak'

     

    BACKUP DATABASE MyDatabase

    TO DISK='E:\MyDatabase.bak'

    MIRROR

    TO DISK='F:\MyDatabase.bak'

     

    BACKUP DATABASE MyDatabase

    TO DISK='E:\Mydatabase.bak',

    DISK='F:\MyDatabase.bak'

     

    BACKUP DATABAES MyDatabase

    TO DISK='E:\MyDatabase.bak',

    DISK='F:\MyDatabase.bak'

    MIRROR

    TO DISK='G:\MyDatabase.bak',

    DISK='H:\MyDatabase.bak'

     

     

    ---------------------------------------------------

    USE MASTER

    --Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.

     

    --Step 1: Retrive the Logical file name of the database from backup.

    RESTORE FILELISTONLY

    FROM DISK = 'D:BackUpYourBaackUpFile.bak'

    GO

     

    --Step 2: Use the values in the LogicalName Column in following Step.

    ----Make Database to single user Mode

    ALTER DATABASE YourDB

    SET SINGLE_USER WITH

    ROLLBACK IMMEDIATE

     

     

    ----Restore Database

    RESTORE DATABASE YourDB

    FROM DISK = 'D:BackUpYourBaackUpFile.bak'

    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',

    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

     

    /*If there is no error in statement before database will be in multiuser

    mode.

    If error occurs please execute following command it will convert

    database in multi user.*/

    ALTER DATABASE YourDB SET MULTI_USER

    GO

     

    --------------------------------------------------------

     

    RESTORE DATABASE OD_TARGET

    FROM DISK = 'C:\ODMS_DB_BACKUP\OD_TARGET.bak'

    WITH REPLACE

     

     

     

    -----------------------------------

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

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

    http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

    http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-backup-database-command/

    http://www.devx.com/getHelpOn/10MinuteSolution/16503/1954

    http://www.techrepublic.com/article/step-by-step-learn-how-to-restore-databases-in-microsoft-sql-server/1041267


    Thanks Shiven:) If Answer is Helpful, Please Vote
    Friday, December 09, 2011 1:24 PM
  • Hello Guy,

    I also have the same problem when try to restore Database to SQL 2008.

    But it fixed.

    The problem is :

    When you use RIAD driver and one of Disk in your system is failure. You try to backup database in one of these this in RIAD. The backup file make incorrupt but OS doesn't show notification.

    How to slove it : Try to backup database to Disk withought RIAD. I work fine for me.

    Best regards,

    Dang Vinh Cuong


    Cuong

    Tuesday, April 10, 2012 4:11 AM
  • Yes. This resolved my problem.

    I took backup of SQL Server 2008 R2 database and restored it on SQL Server 2012 database.


    Villpates

    Wednesday, August 01, 2012 9:10 AM
  • it worked for me.
    Wednesday, January 30, 2013 7:16 AM
  • I had no idea the destination section of the Backup window was a list of files across which the backup will be stripped! This answer solved my problems.
    Tuesday, March 26, 2013 9:51 PM