Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

15 Experts available now in Live!

Get 1:1 Help Now