There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.
1.) Detach all use databases (don’t need to have system databases with us)
2.) Back up all the job if you have any in your SQL Server 2000
3.) Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133
4.) Now, this is the time to uninstall SQL Server 2000
5.) Install SQL Server 2005 and upgrade it with latest hot fixes and patches
6.) Attach all your databases which were detached from SQL Server 2000
7.) Restore all jobs which were backed up from SQL Server 2000
8.) Run the transfer login script which we have generated in step 3 above.
Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah