Hi Victor,
Below are the steps
1) Install SQL Server 2005 as a different named instance. (If you have done it then you can ignore this step.)
2) Once installed, install the latest service pack (SP2) for sql server 2005.
3) You can also apply any hotfixes released after sp2. For the list you can check microsoft website.
4) Take a backup of database from the sql server 2000 instance and restore it in SQL SERVER 2005 INSTANCE. This is very important for u. I think here you are doing the mistake. I think you are restoring it in OLD instance of SQL server 2000 only and viewing it from SQL server management studio.
MAKE SURE You are connected to the new named instance of SQL server 2005 only.
6) Change the compatibilty level of the database restored in the sql server 2005. (you can do it from SQL server management studio or can also execute the command mentioned in the article.)
And you are done :).
Make sure to create any dependent job or linked server if requried.
Below are my comments for the issues.
1. Outer join operators *= and =* are not supported in 90 compatibility mode
In SQL Server 2005, old method of writing Left outer join or Right outer join are not supported any more. You have to replace them with "Left outer join" and "Right Outer join" respectively.
2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default.
Incase you have full text index in your database then drop them before upgradin into SQL server 2005, else your upgradation can fail.
Please read the article. I have mentioned the script to delete all full text indexes.
Once the database is upgraded recreate the FT indices which were removed. So make sure to note tables and columns which have full indices before deleting them so that you can create them later.
3. Column aliases in ORDER BY clause cannot be prefixed by table alias
If you have any query like,
Select studentid, count(studentid) as the CountofStudents from students group by studentid order by CountofStudents
Doing an order by on alias created "CountofStudents" can work in SQL server 2000 but in case of SQL server 2005, it won't. So you have to mention the exact column name i.e "count(studentid)" and not the alias name.
Hope that helps.
Regards
Sachin