Solved

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

Posted on 2013-10-25
3
8,067 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 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