upgrade sql2000 to 2005

Question

I have sql 2000 server and sql 2005 server running on the same box right now. I want to start upgrading my sql 2000 databases to sql 2005. What is the best way to do this?

Answer 1

Use the SQL 2005 upgrade advisor to spot any problems first http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en. Take a copy of all the mdfs and ldfs and attach them to the SQL2005 instance (as you won't be able to reattach a file to SQL2000 if you need to roll back and SQL2005 has changed it). Change the databases' compatibility level to that of SQL2005 EXEC sp_dbcmptlevel YourDB, 90; and test.

Edit: At least the above is how I would do it. It does assume though that you have the luxury of taking the SQL2000 databases offline long enough to copy them. According to this post http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.tools/2008-06/msg00031.html restoring SQL2000 backups to SQL2005 ought to work to avoid this. Also you'll need to script out all users, and SQL Agent Jobs from the 2000 instance and re-associate them with the database users http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

Edit 2: One other thing that just occurred to me is (if the SQL 2000 databases will be being updated in the meantime while you are testing the SQL2005 ones Redgate SQL Data Compare is invaluable for getting them into synch before the final switch over)