I'm needing to move a database from SQL 7 to SQL 2000. What is the
best way to accomplish this and keep all tables, data, permissions, etc
in place throughout the move.

Should I use BCP, Replication or something else?

Thanks for any help.

Hi

http://msdn.microsoft.com/library/de...grade_6nqr.asp

http://msdn.microsoft.com/library/de...grade_5jw3.asp
(and it's sub-pages)

http://support.microsoft.com/default...b;en-us;261334

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"cfmx" <kent@klhs.net> wrote in message
news:1111854538.017241.304760@l41g2000cwc.googlegr oups.com...
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
>
> Should I use BCP, Replication or something else?
>
> Thanks for any help.
>

cfmx (kent@klhs.net) writes:
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
>
> Should I use BCP, Replication or something else?

It depends a little. If the server collation is the same on the two
servers, you can use backup/restore or sp_detach_db/sp_attach_db. The
database will automatically be upgraded to SQL 2000 format (and can
thus not be moved back.) To see if the collations are the same run
sp_helpsort.

If the collations are different, you can still use backup/restore or
detach/attach, since, in difference to SQL7 on SQL 2000 you can mix
collations on the same server. Howver, code that uses temp table and
joins them with permanent tables, will not work if the database collation
is different from the server collation.

For more details see these KB articles:
http://support.microsoft.com/?kbid=224071
http://support.microsoft.com/?kbid=325335

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp