Import from MSSQL2000 (8) to 7?

Hi Group,

I developed a intranet site using MSSQL7/win2000 some time ago.
The target environment used MSSQL2000/8.
We were (almost painlessly) able to import the db-scheme and data from 7
to 8. (Bravo MSSQL)

Now I need to do some upgrading on the application and I would like to
have a copy of the database from MSSQL2000/8 to MSSQL7.
Is that also possible?

Or should I download Microsoft SQL Server Express and use that instead
of my MSSQL7? Is it better?

I hope I can get the relationsheet too (that one with Foreign Keys
mapped in a nice graphical way).

Any advise highly appreciated.
I am good with Postgresql, but my MSSQL skills leave a lot to be
desired. :-/
For an outsider like me the many versions and OS's are quite confusing.

Do I need special commands on MSSQL2000/8 to create a MSSQL7 compatible
export?

Thanks in advance!

Regards,
Erwin MollerErwin Moller (since_humans_read_this_I_am_spammed_too_much@.spam yourself.com)
writes:

Quote:

Originally Posted by

Now I need to do some upgrading on the application and I would like to
have a copy of the database from MSSQL2000/8 to MSSQL7.
Is that also possible?

You would have to build the database on SQL 7 from scripts, and move the
data with bulk load. I don't know you did the import in the other direction,
but going to a higher version it's possible by backup/restore or simply
attaching the database to the higher version. Going to a lower version
this is not possibe.

Quote:

Originally Posted by

Or should I download Microsoft SQL Server Express and use that instead
of my MSSQL7? Is it better?

Well, SQL 7 came out in 1998, SQL Express in 2005, so there are some
more features. But there are also some things that don't really work
as in SQL 7. For instance the old-style join *= is available only
in compatibility level 80 and lower.

Nevertheless, I would recommend going to SQL 2005 if possible.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schreef:

Quote:

Originally Posted by

Erwin Moller (since_humans_read_this_I_am_spammed_too_much@.spam yourself.com)
writes:

Quote:

Originally Posted by

>Now I need to do some upgrading on the application and I would like to
>have a copy of the database from MSSQL2000/8 to MSSQL7.
>Is that also possible?

>
You would have to build the database on SQL 7 from scripts, and move the
data with bulk load. I don't know you did the import in the other direction,
but going to a higher version it's possible by backup/restore or simply
attaching the database to the higher version. Going to a lower version
this is not possibe.
>

Quote:

Originally Posted by

>Or should I download Microsoft SQL Server Express and use that instead
>of my MSSQL7? Is it better?

>
Well, SQL 7 came out in 1998, SQL Express in 2005, so there are some
more features. But there are also some things that don't really work
as in SQL 7. For instance the old-style join *= is available only
in compatibility level 80 and lower.
>
Nevertheless, I would recommend going to SQL 2005 if possible.

Hi Erland,

Thanks for your answers.
I think I'll switch to SQL Express 2005.
One last question: If I switch to SQL Express 2005, do you expect that I
can copy the database from MSSQL2000 to SQL Express 2005 without scripts
and bulk data load?

Regards,
Erwin Moller|||Erwin Moller (since_humans_read_this_I_am_spammed_too_much@.spam yourself.com)
writes:

Quote:

Originally Posted by

I think I'll switch to SQL Express 2005.
One last question: If I switch to SQL Express 2005, do you expect that I
can copy the database from MSSQL2000 to SQL Express 2005 without scripts
and bulk data load?

Yes. You can copy with attach/detach or BACKUP/RESTORE. Note that you
cannot copy back this way. This must be done with scripts and bulk load.

Also, be sure to run sp_updatestats when you have copied the database
to SQL 2005, as the migration invalidates all statistics.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql