470,613 Members | 2,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,613 developers. It's quick & easy.

Database Move

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.

Jul 23 '05 #1
2 1176
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: mi**@epprecht.net

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

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

"cfmx" <ke**@klhs.net> wrote in message
news:11**********************@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.

Jul 23 '05 #2
cfmx (ke**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Edwinah63 | last post: by
2 posts views Thread by bryja_klaudiusz[at]poczta[dot]fm | last post: by
3 posts views Thread by Jon Jacobs | last post: by
8 posts views Thread by Inspector | last post: by
5 posts views Thread by Neil | last post: by
5 posts views Thread by Seguros Catatumbo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.