Database restore errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database restore errors

Author

Topic

tmack
Starting Member

6 Posts

Posted - 2007-06-29 : 18:44:03

I recently made a move to a new server, and I made a backup of all my databases which all work except one.

This is the error I get:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Restore failed for Server '64.85.163.93,1433'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Headeronly:

Backup Name: CMS-Full Database Backup
Backup Desc: NULL
Backup Type: 1
Expir Date: NULL
Compressed: 0
Position: 1
DeviceType: 2
USername: sa
DB Name: CMS
DB Ver: 611
BackupSize: 5521408
First LSN:75000000044600037
Last LSN:75000000046400001
Checkpoint LSN:75000000044600037
Backup DB LSN: 0
Sort Order: 52
CodePage: 0
Unicode Locale ID: 1033
Unicdoe Comparision Style: 196609
Compatibility Level: 90
Software Vendor ID: 4608
Software Ver: 9
Flags: 512
BindingID: 669064E1-617C-4C90-9CA0-4B7DDF772F99
Recovery Fork ID: 552CCBF1-AF5C-4A35-AF63-E4AE854B4CB2
Collation: SQL_Latin1_General_CP1_CI_AS
FamilyGUID: 552CCBF1-AF5C-4A35-AF63-E4AE854B4CB2
HasBulkLoggeddata: 0
IsSnapshot: 0
IsReadOnly: 0
IsSingleUser: 0
HasBackupChecksums: 0
IsDamaged: 0
BeginsLogChain: 0
HasIncompleteMetaData: 0
IsForceOffline: 0
IsCopyOnly 0
FirstRecoveryForkID: 552CCBF1-AF5C-4A35-AF63-E4AE854B4CB2
ForkPoint: NULL
RecoveryModel: FULL
DifferentialBaseLSN: NULL
|| BaseGUID: NULL
Backuptype Desc: DB
Backupsetguid: 97743B90-FABB-4296-9198-8FECB7B5DE07

LabelOnly:
MediaName: Null
MediaSetID:1D4A6421-D6FC-4940-878E-3DF0E4EBF179
FamilyCount: 2
FamilySequenceNumber: 2
MediaFamilyId: 9085DAC7-0000-0000-0000-000000000000
MediaSequenceNumber: 1
MediaLabelPresent: 0
Media Desc: NULL
SoftwareName: Microsoft SQL Server
Software Vendor ID: 4608
MediaDate: 2007-06-06 21:06:50.000
MirrorCount: 1

Based on what I read I need two files, however I can't seem to find a second file. I don't know why it would need one as I backed it up the same way as the other 30 databases! This is my most important database and is hurting me by the day!

Is there anyway I can remove the familyCount, or ignore the error? Thanks

Kristen
Test

22859 Posts

Posted - 2007-06-30 : 05:19:37

Sounds like the original was a striped backup, in which case you need to two (or maybe more!) files to make the restore.

Alternatively make a new, manual, backup of the source database using a single file.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-30 : 20:54:36

How did you backup dbs?

tmack
Starting Member

6 Posts

Posted - 2007-06-30 : 22:35:13

The old data is gone, I CANNOT make a new backup.

I backed up the DBs using Microsoft SQL Server Management Studio. I did not stripe the data, I don't know why it did that. Is there anyway to make it work by modifying the file? It's not the data I'm worried about, it's the tables and stored procedures..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-30 : 23:38:22

If you did that in gui, can go back to that backup page to find out where the db backed up to.

tmack
Starting Member

6 Posts

Posted - 2007-07-01 : 07:39:36

I don't have the old server anymore, no.

Kristen
Test

22859 Posts

Posted - 2007-07-01 : 16:38:03

Do you have a backup of the [raw] MDF/LDF? (Would probably need to be from when the SQL Service was stopped)

Kristen

tmack
Starting Member

6 Posts

Posted - 2007-07-01 : 17:10:25

Nope. I only made backup files, I wish I did. I think this is a bug in SQL, because FILELISTONLY is only showing 1 file... Are there any programs that can rewrite a backup file? Or anyway to rewrite the backup file with TSQL?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-01 : 20:26:14

It lists source db files in 'restore filelistonly'. If you need backup info, should use 'restore headeronly'.

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 05:19:52

Might be worth trying to make the restore manually, rather than through SSMS

For restore syntax see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

tmack
Starting Member

6 Posts

Posted - 2007-07-05 : 18:56:08

Nope, all restore commands give me the same error.