Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-10-25
3
Medium Priority
?
8,690 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 23

Accepted Solution

by:
Steve Wales earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
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…

636 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