Remove From My Forums

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

Soru
3

Oylamak İçin Oturum Aç

I made a backup of my database from ServerA (SQL 2000) and trying to restore it to ServerB (SQL 2005) and get this error below, even after configuring the log and data paths for the restore:

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

Restore failed for Server 'serverbname'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.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.Smo)

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

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

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

20 Şubat 2006 Pazartesi 23:21

Yanıtlar

15

Oylamak İçin Oturum Aç

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

14 Temmuz 2007 Cumartesi 00:48

Oylamak İçin Oturum Aç

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
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Yanıt Olarak İşaretleyen Kalman TothEditor 9 Aralık 2011 Cuma 12:50
Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 12:48

10 Mayıs 2011 Salı 13:57

Yanıtlayıcı

0

Oylamak İçin Oturum Aç

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.

18 Ekim 2008 Cumartesi 16:09
0

Oylamak İçin Oturum Aç
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
- Yanıt Olarak İşaretleyen Kalman TothEditor 26 Temmuz 2010 Pazartesi 05:14
8 Haziran 2010 Salı 09:38
0

Oylamak İçin Oturum Aç
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
- Yanıt Olarak İşaretleyen Kalman TothEditor 12 Ekim 2010 Salı 18:20
12 Ekim 2010 Salı 17:54
0

Oylamak İçin Oturum Aç
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
- Yanıt Olarak İşaretleyen Kalman TothEditor 13 Ekim 2010 Çarşamba 10:51
13 Ekim 2010 Çarşamba 06:54

Tüm Yanıtlar

0

Oylamak İçin Oturum Aç

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.

25 Şubat 2006 Cumartesi 21:30
0

Oylamak İçin Oturum Aç

Try to list the number of media from the backup using RESTORE FILELISTONLY. Search under books online for other RESTORE statement utilities.

27 Şubat 2006 Pazartesi 10:05
0

Oylamak İçin Oturum Aç

this is resolved, I backed up the database again and restored and it worked fine the second time...

27 Şubat 2006 Pazartesi 13:06
0

Oylamak İçin Oturum Aç

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.

11 Mayıs 2006 Perşembe 00:33
0

Oylamak İçin Oturum Aç

I'm still having a problem with this. Can someone enlighten me?

23 Mayıs 2006 Salı 17:16
0

Oylamak İçin Oturum Aç

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
------------------------------

22 Haziran 2006 Perşembe 03:38
0

Oylamak İçin Oturum Aç

Hello! Have the same problem. Are there anybody who can provide an answer?

6 Kasım 2006 Pazartesi 10:57
10

Oylamak İçin Oturum Aç
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.
- Yanıt Olarak Öneren DominicL 26 Temmuz 2013 Cuma 20:08
14 Kasım 2006 Salı 21:38
0

Oylamak İçin Oturum Aç

Thank you! Actually, restoring from new backup went normally. Just when i made a first backup i thought destination listed to be optional.... :)

17 Kasım 2006 Cuma 14:52
1

Oylamak İçin Oturum Aç

anyone who can provide a resolution for others who only have the backup file?

11 Aralık 2006 Pazartesi 10:22
5

Oylamak İçin Oturum Aç
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
- Yanıt Olarak Öneren angelktpi 22 Ocak 2013 Salı 16:29
12 Aralık 2006 Salı 22:58
0

Oylamak İçin Oturum Aç
Hi Glate:

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

Right click on the database and select Tasks/Restore/Database option from the shortcut menu

Click on the "From Device" radio button, then click on the ellipse button

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

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.

Click on the "From database" radio button and select the database name you are trying to restore.

In the "Options" section, select the checkbox "Override the existing database"

Finally, click on the "OK" button to start the restore

Finally, click the "OK" button to start the
5 Mart 2007 Pazartesi 23:35
0

Oylamak İçin Oturum Aç

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

16 Mart 2007 Cuma 21:23
0

Oylamak İçin Oturum Aç

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?

3 Nisan 2007 Salı 11:23
2

Oylamak İçin Oturum Aç

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

3 Nisan 2007 Salı 12:48
15

Oylamak İçin Oturum Aç

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

14 Temmuz 2007 Cumartesi 00:48
0

Oylamak İçin Oturum Aç

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?

31 Temmuz 2007 Salı 14:59
1

Oylamak İçin Oturum Aç

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.

26 Ekim 2007 Cuma 15:13
0

Oylamak İçin Oturum Aç

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.

20 Kasım 2007 Salı 15:18
0

Oylamak İçin Oturum Aç

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

1 Şubat 2008 Cuma 06:00
0

Oylamak İçin Oturum Aç

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

22 Şubat 2008 Cuma 17:25
0

Oylamak İçin Oturum Aç

I had the same problem, patrick_henry_1776 's solution worked for me. THANKS!

3 Mart 2008 Pazartesi 15:17
0

Oylamak İçin Oturum Aç

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.

27 Nisan 2008 Pazar 18:48
0

Oylamak İçin Oturum Aç

Thanks Dude ! Yours was the perfect Answer....

3 Mayıs 2008 Cumartesi 09:14

Oylamak İçin Oturum Aç

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

27 Haziran 2008 Cuma 02:21

Oylamak İçin Oturum Aç

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

8 Temmuz 2008 Salı 16:08

Oylamak İçin Oturum Aç

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!

9 Ağustos 2008 Cumartesi 05:35

Oylamak İçin Oturum Aç

>>> 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.

9 Ağustos 2008 Cumartesi 05:41

Oylamak İçin Oturum Aç

My solution:

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

GO

16 Ağustos 2008 Cumartesi 16:07

Oylamak İçin Oturum Aç

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???

7 Ekim 2008 Salı 19:35

Oylamak İçin Oturum Aç

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

18 Ekim 2008 Cumartesi 15:07

Oylamak İçin Oturum Aç

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.

18 Ekim 2008 Cumartesi 16:09

Oylamak İçin Oturum Aç

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...

13 Kasım 2008 Perşembe 15:29

Oylamak İçin Oturum Aç

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'

14 Ocak 2009 Çarşamba 07:48

Oylamak İçin Oturum Aç

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.

Yanıt Olarak Öneren Virtual.Offis 2 Nisan 2009 Perşembe 01:54

14 Ocak 2009 Çarşamba 08:45

Oylamak İçin Oturum Aç

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

Yanıt Olarak Öneren Virtual.Offis 2 Nisan 2009 Perşembe 02:29

2 Nisan 2009 Perşembe 02:29

Oylamak İçin Oturum Aç

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

6 Nisan 2009 Pazartesi 01:36

Oylamak İçin Oturum Aç

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

Yanıt Olarak Öneren Shimmy Weitzhandler 13 Mayıs 2009 Çarşamba 15:42

13 Mayıs 2009 Çarşamba 09:49

Oylamak İçin Oturum Aç

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

Yanıt Olarak Öneren Shimmy Weitzhandler 13 Mayıs 2009 Çarşamba 19:59

13 Mayıs 2009 Çarşamba 18:07

Oylamak İçin Oturum Aç

Very nice. Simple and clear.

2 Temmuz 2009 Perşembe 19:09

Oylamak İçin Oturum Aç

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.

7 Ağustos 2009 Cuma 05:00

Oylamak İçin Oturum Aç

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.

8 Haziran 2010 Salı 09:03

Oylamak İçin Oturum Aç

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

8 Haziran 2010 Salı 09:36

Oylamak İçin Oturum Aç

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

Yanıt Olarak İşaretleyen Kalman TothEditor 26 Temmuz 2010 Pazartesi 05:14

8 Haziran 2010 Salı 09:38

Oylamak İçin Oturum Aç

great

thanks a lot

Gabriel

8 Haziran 2010 Salı 09:50

Oylamak İçin Oturum Aç

Thank you so much

Nice and Helpful

Alex

25 Temmuz 2010 Pazar 02:17

Oylamak İçin Oturum Aç

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
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 12:37

26 Temmuz 2010 Pazartesi 05:30

Yanıtlayıcı

Oylamak İçin Oturum Aç

Thank you for this great explanation! I wish all forum responses were as clear ;-)

Michaela

27 Temmuz 2010 Salı 08:43

Oylamak İçin Oturum Aç

Thanks Kevin, it really help :)

22 Eylül 2010 Çarşamba 10:42

Oylamak İçin Oturum Aç

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

5 Ekim 2010 Salı 14:07

Oylamak İçin Oturum Aç

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

10 Ekim 2010 Pazar 16:28

Oylamak İçin Oturum Aç

How about using T-SQL script for database restore?

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

Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 12:47

10 Ekim 2010 Pazar 20:58

Yanıtlayıcı

Oylamak İçin Oturum Aç

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.

11 Ekim 2010 Pazartesi 09:40

Oylamak İçin Oturum Aç

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

11 Ekim 2010 Pazartesi 15:15

Oylamak İçin Oturum Aç

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

12 Ekim 2010 Salı 17:01

Oylamak İçin Oturum Aç

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

Yanıt Olarak İşaretleyen Kalman TothEditor 12 Ekim 2010 Salı 18:20

12 Ekim 2010 Salı 17:54

Oylamak İçin Oturum Aç

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
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 12:47

12 Ekim 2010 Salı 18:23

Yanıtlayıcı

Oylamak İçin Oturum Aç

yes i understood the problem but as said by SQLUSA this was done unintentionally.

So how do i find the 2nd file now

13 Ekim 2010 Çarşamba 06:41

Oylamak İçin Oturum Aç

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

Yanıt Olarak İşaretleyen Kalman TothEditor 13 Ekim 2010 Çarşamba 10:51

13 Ekim 2010 Çarşamba 06:54

Oylamak İçin Oturum Aç

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

13 Ekim 2010 Çarşamba 13:24

Oylamak İçin Oturum Aç

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??

14 Ekim 2010 Perşembe 14:58

Oylamak İçin Oturum Aç

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

15 Ekim 2010 Cuma 04:42

Oylamak İçin Oturum Aç

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.

16 Ekim 2010 Cumartesi 07:21

Oylamak İçin Oturum Aç

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

16 Ekim 2010 Cumartesi 09:54

Oylamak İçin Oturum Aç

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.

26 Ekim 2010 Salı 06:13

Oylamak İçin Oturum Aç

this really helps me a lot. Thank all of you here.

27 Ekim 2010 Çarşamba 03:10

Oylamak İçin Oturum Aç

Thanks for your help

3 Kasım 2010 Çarşamba 12:11

Oylamak İçin Oturum Aç

tell me how to fix that problem...

1 Media file is missed among 2 media files

10 Mayıs 2011 Salı 09:44

Oylamak İçin Oturum Aç

You need to find both backup files in order to be able to restore.

Tibor Karaszi, SQL Server MVP | web | blog

10 Mayıs 2011 Salı 10:54

Oylamak İçin Oturum Aç

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
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Yanıt Olarak İşaretleyen Kalman TothEditor 9 Aralık 2011 Cuma 12:50
Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 12:48

10 Mayıs 2011 Salı 13:57

Yanıtlayıcı

Oylamak İçin Oturum Aç

Hi, Thanks. It helped me

Düzenleyen Shivendoo Kumar Dubey 9 Aralık 2011 Cuma 13:29

9 Aralık 2011 Cuma 12:42

Oylamak İçin Oturum Aç

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'

--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

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

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

9 Aralık 2011 Cuma 13:24

Oylamak İçin Oturum Aç

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

10 Nisan 2012 Salı 04:11

Oylamak İçin Oturum Aç

Yes. This resolved my problem.

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

Villpates

1 Ağustos 2012 Çarşamba 09:10

Oylamak İçin Oturum Aç

it worked for me.

30 Ocak 2013 Çarşamba 07:16

Oylamak İçin Oturum Aç

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.

26 Mart 2013 Salı 21:51

Oylamak İçin Oturum Aç

This worked for me. Note the comma after 'Path'
That tripped me up when I didn't notice it at first.

18 Haziran 2016 Cumartesi 00:20

Oylamak İçin Oturum Aç

Thank you Kevin that was really heplfull

13 Eylül 2016 Salı 17:53

Oylamak İçin Oturum Aç

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

Thank you for this syntax! This was exactly what I needed.

23 Ekim 2018 Salı 14:38

Oylamak İçin Oturum Aç

Hi, i have a same problem but in my case the first backup is set to NUL in media_set_id table

Have you an idea to restore ?

I try this :

RESTORE DATABASE [mydatabase] 
FROM 
DISK = '',
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\BDD\BDD backup.bak'

Without success.

Düzenleyen bbreton 12 Haziran 2020 Cuma 14:27

12 Haziran 2020 Cuma 14:06

Oylamak İçin Oturum Aç

Resist the urge to reply to any old thread - especially one that is 14 years old. And it does little good to reply with a "i have same problem" without providing any details.

Start a new thread and provide as much useful information as you can - including the details that were requested / posted in this thread. It is HIGHLY doubtful you are using sql server 2000 - but that IS important information.

12 Haziran 2020 Cuma 14:28

En iyi yanıtlayıcılar

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

Soru

Yanıtlar

Tüm Yanıtlar