Help! Unable to restore DB from SQL7 to SQL2000

Hi Good Day everybody,
Currently, I'm planning to upgrade SQL7 to SQL2000, the configuration are
below:
Current Server (Server A)
a) Windows NT4 SP6a
b) SQL 7 + SP4
c) Default Collation: SQL_Latin1_General_CP1_CI_AS
d) Default Data Location: E:\MSSQL7
New Server (Server B)
a) Window 2000 Server SP4
b) SQL 2000 + SP3a
c) Default Collation: Latin1_General_CP1_CI_AS (required to set as default)
d) Default Data Location: D:\Program Files\Microsoft SQL Server
Authentication Mode:
a) Mixed Mode
b) SQL and Windows Authentication
* Both server are login with same userid and password.
Problem:
I have create and new user database "PA_CCCTemp" in the SQL2000 server.
Besides, I also backup user database "PA_CCC" from SQL7.
However, during I restore databases that I have backup into the SQL2000
server, I hit an error "Microsoft SQL-DMo [ODBC SQLState:42000] The backup
set holds a backup of a database other than the existing 'PA_CCCTemp"
database. Restore Databse is terminating abnormally.
I do not know what is the problem caused. Either the database is different
name, or the restoration location are different from the original place, or
the collation is different.
I also need help on how I can migrate the database from SQL7 to SQL 2000,
includes user id and logon password.
Regards,
Polar Bear
Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.Xmdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.lXdf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
SQL Server MVP
Thanks
Hari
SQL Server MVP
"Polar Bear" <Polar Bear@.discussions.microsoft.com> wrote in message
news:227A549B-7D5A-48DA-A4F5-1850CE31B84B@.microsoft.com...
> Hi Good Day everybody,
> Currently, I'm planning to upgrade SQL7 to SQL2000, the configuration are
> below:
> Current Server (Server A)
> a) Windows NT4 SP6a
> b) SQL 7 + SP4
> c) Default Collation: SQL_Latin1_General_CP1_CI_AS
> d) Default Data Location: E:\MSSQL7
> New Server (Server B)
> a) Window 2000 Server SP4
> b) SQL 2000 + SP3a
> c) Default Collation: Latin1_General_CP1_CI_AS (required to set as
> default)
> d) Default Data Location: D:\Program Files\Microsoft SQL Server
> Authentication Mode:
> a) Mixed Mode
> b) SQL and Windows Authentication
> * Both server are login with same userid and password.
> Problem:
> I have create and new user database "PA_CCCTemp" in the SQL2000 server.
> Besides, I also backup user database "PA_CCC" from SQL7.
> However, during I restore databases that I have backup into the SQL2000
> server, I hit an error "Microsoft SQL-DMo [ODBC SQLState:42000] The backup
> set holds a backup of a database other than the existing 'PA_CCCTemp"
> database. Restore Databse is terminating abnormally.
> I do not know what is the problem caused. Either the database is different
> name, or the restoration location are different from the original place,
> or
> the collation is different.
> I also need help on how I can migrate the database from SQL7 to SQL 2000,
> includes user id and logon password.
> Regards,
> Polar Bear
>