Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 3rd, 2004, 05:03 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Restore Problem

I am having a problem restoring a database (SQL2000)
from a .bak file device. When I attempt a restore I get
the following message.

"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally".

What does the error mean and is there a way of getting around it?

The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?

Regards

Neil Gibson
 
Old June 3rd, 2004, 05:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Before Backing up the DB, were the log files been distributed to different drives to optimized the I/O operations?

_________________________
-Vijay G
Strive for Perfection
 
Old June 3rd, 2004, 05:38 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for responding so quickly.

The database was on a test server and both the data and log files were on the same disk.

...Neil
 
Old June 3rd, 2004, 06:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I think you got to check the original location of DATA file and LOG file of the data base before backing it up. Also you got to consider if that has more than one log file and secondary data file, etc...

Any Idea on how the backup was taken? or What was the command used while backing up?

This may show some light on how to go about. Mean while let me explore to see what other reasons could cause this.

Cheers!

_________________________
-Vijay G
Strive for Perfection
 
Old June 3rd, 2004, 06:14 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It was all very simple. Both data and log files on the same drive.

The backup was taken with the SQL Backup utility from SQLEM.

...Neil
 
Old June 3rd, 2004, 06:20 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The original database was on out production server and the data and log files were on seperate drives. A backup was taken of this db (using SQLEM) and transferred to a test server as a .bak file. The backup was them restored onto the test server and some work carried out on the database. After the work was complete the database was backed up (again using SQLEM) and the resulting .bak file zipped with Winzip9 to save space.

Four weeks later I need to restore the database to do some fiurther work. I have unzipped the file and tried to restore it (using SQLEM) and encountered this problem.
 
Old June 3rd, 2004, 06:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

When you backup a db, that had data file and log file in different drives, and try restoring it to another box where any one of those drives does not exist or the path to where it is trying to restore doesnot exits, you would fail to get that restored.

What I feel is when restored to test server, and after some work is carried out again while backing up, there should not have been any thing extra done while backing up.

Also pls check if the box into which you are trying to restore this bak file, still has the path and drive existing so that it does not fail.

Or you can try to restore using the following t-sql statement.
RESTORE DATABASE TestDB
    FROM DISK = 'c:\TestDB.bak'
    WITH MOVE 'TestDB_Data' TO 'c:\test\TestDB.mdf',
    MOVE 'TestDB_log' TO 'c:\test\TestDB.ldf'

The path may vary with that in your system. Please change that accordingly.

Hope that Helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
 
Old June 3rd, 2004, 08:58 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have tried the restore using the RESTORE command in Query Analyser and got the same result.

I have also tried restoring the backup to the production server, again with the same result.

Is there any utility that will let me look at the format of the .bak file? I am assuming that there is a header of some sort that is incorrect in the .bak file.
 
Old June 3rd, 2004, 09:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I dont think there is an utility with which you can look into .bak format.

Were you able to restore that .bak again on you test server?

Cheers!

_________________________
-Vijay G
Strive for Perfection
 
Old June 3rd, 2004, 09:48 AM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have not been able to restore the backup to the test or production servers. I can only think that by zipping and unzipping the .bak file it has corrupted the file in some way.

Am giving up and will have to re-do the work that was done.

Would still appreciate ideas if you come across anything

Regards

...Neil





Similar Threads
Thread Thread Starter Forum Replies Last Post
BizTalk Server 2006 Standard Restore Problem stanelisn Biztalk 2 August 2nd, 2013 09:39 AM
Problem to restore database in C#2005 database acmuralee MySQL 0 March 25th, 2008 04:42 AM
Restore umeshtheone SQL Server 2000 1 July 12th, 2007 07:38 AM
Problem With Restore Log prabhakaran SQL Server 2000 1 April 23rd, 2007 10:06 AM
Restore problem Adam H-W SQL Server 2000 1 June 3rd, 2004 05:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.