Import from MSSQL2000 (8) to 7?

Import from MSSQL2000 (8) to 7?

am 19.07.2007 14:24:31 von Erwin Moller

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 Moller

Re: Import from MSSQL2000 (8) to 7?

am 19.07.2007 17:41:45 von Erland Sommarskog

Erwin Moller (since_humans_read_this_I_am_spammed_too_much@spamyourself.c om)
writes:
> 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.

> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Import from MSSQL2000 (8) to 7?

am 20.07.2007 14:30:58 von Erwin Moller

Erland Sommarskog schreef:
> Erwin Moller (since_humans_read_this_I_am_spammed_too_much@spamyourself.c om)
> writes:
>> 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.
>
>> 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

Re: Import from MSSQL2000 (8) to 7?

am 20.07.2007 23:23:53 von Erland Sommarskog

Erwin Moller (since_humans_read_this_I_am_spammed_too_much@spamyourself.c om)
writes:
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx