Solved

Restoring db on SQL2008 R2 - media set has 2 families but only 1 are provided

Posted on 2013-10-25
3
8,356 Views
Last Modified: 2013-10-31
we have a 2008R2 SQL server, the machine has 3 partitions

C - OS
E -Data
D -Logs

when i initially installed SQL an set up all the databases they inadvertentaly all got setup on C

so, i backed up all the databases verified them. removed the database on C.

recreated the database on E, then did a restore & replace with the backups

this has worked great for all but one db

when i try to restore this certain db, i receive the error

Restore failed for Server 'SQL2'. (microsoft.SqlServer.SmoExtended)

Additional infomation:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
0
Comment
Question by:mudcow007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:mudcow007
ID: 39600005
one thing to add

where i have put all my backed up db's there are two with the same time/ date stamp

i have since renamed the files though (god knows why)

i have just tried to restore the db again using both of these files an got this error

System.Data.SqlClient.SqlError: the backup media on C:\db_backups\3. baseline.bak is part of media family 2 which has already been processed on C:\db_backups\4. baseline.bak. Ensure that the correct volumes are loaded
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39600363
I have tried to replicate your problem, and the error message I'm getting is very close so I'm wondering if it's not something related.

The two files with the same date and time stamp sounds like when you did the backup you split it across 2 backup files:

Something like this:

BACKUP DATABASE [demo] 
TO  DISK = N'C:\Users\sjwales\Desktop\demo2.bak',  
DISK = N'C:\Users\sjwales\Desktop\demo1.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'demo-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


When you restored it, did you list the same data file in the restore twice?

If I do this it works:

RESTORE DATABASE [demo2] 
FROM  DISK = N'C:\Users\sjwales\Desktop\demo1.bak'
, DISK = N'C:\Users\sjwales\Desktop\demo2.bak' 
WITH  FILE = 1,  
MOVE N'demo' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\demo2.mdf',  
MOVE N'demo_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\demo2_1.ldf',  
NOUNLOAD,  STATS = 10
GO

Open in new window


But if I do this, it fails with an error pretty darn close to what you're getting:

RESTORE DATABASE [demo2] 
FROM  DISK = N'C:\Users\sjwales\Desktop\demo1.bak'
, DISK = N'C:\Users\sjwales\Desktop\demo1.bak' 
WITH  FILE = 1,  
MOVE N'demo' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\demo2.mdf',  
MOVE N'demo_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\demo2_1.ldf',  
NOUNLOAD,  STATS = 10
GO

Open in new window


The error is:

Msg 3227, Level 16, State 1, Line 1
The backup media on "C:\Users\sjwales\Desktop\demo1.bak" is part of media family 2 which has already been processed on "C:\Users\sjwales\Desktop\demo1.bak". Ensure that backup devices are correctly specified. For tape devices, ensure that the correct volumes are loaded.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

That's close however the error message mentions the name of the file twice.

If I copied demo1.bak to demo3.bak and try again I get the same error but mentioning the two different files.

Is it possible that when you copied files around etc, you copied the same file twice ?

What is the EXACT script you are restoring with ?

Check that - does it ring any bells that you might be trying to restore the same file twice ?

You can tell the exact files in your backupsets with this query (change the date as needed to meet when your backup was done):

use msdb
go
select family_sequence_number, physical_device_name, database_name
from backupmediafamily a join backupset b
on a.media_set_id = b.media_set_id
where convert(varchar,b.backup_start_date,101) = '10/25/2013'

Open in new window


Hope that helps you work out what you did.
0
 

Author Closing Comment

by:mudcow007
ID: 39613541
Sorry i havent updated this, but yes you were correct, SQL had split the backup into two parts

one part was in the location i had set, the other was within the "backup" folder with the SQL folders

many thanks
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question