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 BearHi,
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.­mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.l­df'
(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
>sql

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!