Upgrade from SQL Server 2000 to SQL Server 2005

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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Upgrade from SQL Server 2000 to SQL Server 2005”

Comments are closed.