Unofficial guide: Upgrade from SQL 2000 to SQL 2005

When Microsoft released SQL 2005, they created an upgrade path to upgrade the whole SQL 2000 servers to SQL 2005. Microsoft never came up with a simple method to upgrade a single database on a SQL 2000 server to a SQL 2005 database.
We have seen many requests regarding how to upgrade from SQL 2000 to SQL 2005.

Here?s an unofficial guide on how to upgrade from SQL 2000 to SQL 2005 database on DiscountASP.NET

1. Install SQL 2005 clients (either the full version or express version)
-Check potential compatibility issues on SQL 2005 book online
-Do a search on sp_dbcmptlevel in SQL 2005 book online. The document titled sp_dbcmptlevel (Transact-SQL) should contain a list of compatibility issues.
-Note that SQL 2005 is at Compatibility Level 90 and SQL 2000 is 80
-If you see any issue, you should update the SQL 2000 database to avoid potential problem before upgrading

2. Order SQL 2005 addon service in the control panel (https://my.discountasp.net)

-Optional step: If you site performs constant updates to the database, I recommend that you disable your site during this process to avoid losing data. You can do so in the IIS manager in the control panel - use the stop/start tool.

3. Backup your SQL 2000 database using the SQL 2000 Tool (https://my.discountasp.net/mssql-tools.aspx). You should have the SQL 2000 backup file in your website root. Note the filename down.

4. In the SQL 2005 Manager in the control panel, use the SQL 2005 restore tool to restore the SQL 2000 backup file you just created.

5. Your SQL 2005 database should now contain the SQL 2000 data.

6. Test couple queries directly with your SQL 2005 query and make sure you database is migrated properly

7. Update your web application?s connection string to point to the SQL 2005 server

8. Contact our billing department to terminate the SQL 2000 addon [/list]

Note that we have not done exhaustive testing on this process and some objects may not be migrated properly to SQL 2005 in the restore process.