Solved

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

Posted on 2013-10-25
3
7,849 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now