HELP! Restore SQL7 database to SQL2000 SP3a problem

I have a small database that urgently needs restoring under SQL 2000.
SP3a is installed.
Is there a procedure?Yes.
First obtain three chickens and a mule...
Seriously, a SQL 7.0 database can be restored (or attached after detach) to
a SQL 2000 instance using the normal restore database commands or Enterprise
Manager. There is no difference in restoring a 7.0 backup to a SQL 2000
server than in restoring a SQL 2000 backup to a SQL 2000 server.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||You can use a RESTORE DATABASE command. For example:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH REPLACE
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||Thanks Geoff,
The error I am getting is:
SQL-DMO (ODBC SQLstate: 42000)
The media set for database (name) has 2 family members but only 1 are
provided. All members must be provided. Restore database is
terminating... "
S.|||Evidently your backup spanned multiple backup sets (files or tapes). You
need to specify all backup sets in a single restore command:
RESTORE DATABASE MySQL7Database
FROM C:\Backups\MySQL7Backup1.bak,C:\Backups\MySQL7Backup2.bak
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132773276.165857.173410@.z14g2000cwz.googlegroups.com...
> Thanks Geoff,
> The error I am getting is:
> SQL-DMO (ODBC SQLstate: 42000)
> The media set for database (name) has 2 family members but only 1 are
> provided. All members must be provided. Restore database is
> terminating... "
> S.
>|||Geoff, thanks
No, this was not the case. The database backup file was created in SQL
Server 7 (SP4) and zips to 2MB after backup.
On selection of the database file it's backup set properties can be
seen. I selected to restore All, and there is no existing database over
which it will overwrite. So it is hard to see why it should be asking
for another file.
Are there any other settings that can be changed? I am having a look...
S.|||Well guys, thanks very much anyway. I have tried using Dan Guzman's
suggestion of running a command script, but still no good - same error
type. I suppose that somehow there must be a corruption, as whenever I
have previously backed up the system it has been plain sailing to get
it back in again. But that was in SQL 7.
I shall get another backup (500 Km away!), unless you can advise more.|||It might be that the actual backup spans multiple files, even though you
have only one of the members. You can see if this is your situation by
examining FamilyCount of the RESTORE LABELONLY results:
RESTORE LABELONLY
FROM DISK='C:\Backups\MyDatabase.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>|||The backup is striped. I never seen a case where SQL Server said it is, but it wasn't. Use RESTORE
LABELONLY as suggested by Dan, and you will see the number of members. I.e., you need to get the
other backup files, or a new non-striped backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>sql