Hello,

I have three databases on an sql 2000 sp4 server that I like to move to a
newly built sql 2005 server.

Would the detach/retach or backup/restore method work? If so, what other
things do I have to consider?

TIA,
tnt

tnt (tnt (AT) discussions (DOT) microsoft.com) writes:
Yes. Personally, I think BACKUP/RESTORE is the simplest.

As always when you move a database from one server to another, you need
to review login mapping. First you need to make sure that there are logins
for the database users on the target server (at least for the persons
that need to access the databases!). Next is to review the mapping between
database users and logins. Windows users should not be a problem if
servers are in the same domain. SQL users on the other will most likely
not map to the correct SQL login on the new server, but you need to
change that with ALTER USER.

Next thing to consider is that you should run sp_updatestats on all
databases, as statistics are invalidated by the migration.

You should also consider the compatibility level. It will remain in 80
after the RESTORE. If you know that you don't have any compatibility
issues, change it to 90. (You can change it back, if your assumption
proves to be wrong.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx