Restoring SQL2000 after upgrade

I performed an in-place upgrade of my SQL2000 database to SQL2005. I am having too many problems at the moment with compatability issues between SQL 2005 and VS2003 and need to roll back SQL 9.0 to 8.0. Are there any white papers, or published procedures for doing this? I have a tape backup of the 2000 system, but there have been various updates to the databases during the past 3 weeks since the upgrade. The structure of the databases have not changed.

Please help!

There is no vehicle to rollback. You will want to reinstall sql2k as a different instance, restore data from your tape backup and then use data import/export to update sql2k data with sql2k5 data.

If you're looking for a way to mount a sql2k5 database on a sql2k instance, it's not possible.|||

Are you running the SQL Server database in 9.0 or 8.0 compatibility mode? When you upgrade from 2000 to 2005, you will be in 8.0 mode unless you explicitly changed it to 9.0. If you are running in 9.0 mode and having problems you might try switching back to 8.0 and see if that helps as a temporary measure. What kind of problems are you having?

If you are already in 8.0 mode and still having problems that you cannot work through, then I would install a fresh copy of SQL Server 2000 (on a different server), apply SP4 and any hotfixes, and then restore from your tape backup. Then, you can use SSMS from SQL Server 2005 to connect to both servers are start trying to get your data back in synch on your three week old backup.

You can use "DataDude" (Visual Studio 2005 Team Edition for Database Professionals) to to compare the data between the two databases, and generate scripts to help you get the old copy up to date.

|||

I am running in 9.0 mode. The main problem is that all my applications are written in VS2003 and when I need to change a data adapter, I have to manually code the insert/update and delete methods rather than having VS generate the automatic code, which is a pain.

These applications manage extensive databases and there are probably a hundred data adapters involved, granted, most of them never change, but it makes maintenance a bear when they do. I know that the DB operations are generally more efficient if you code those methods manually, but the applications are used by only a handfull of people (20 or so) and I doubt the increased processing efficiency would be noticable vs. the ease of letting VB auto-create the code. Plus, I think the 2000 enterprise manager is much more intutitive and much faster than the the SQL2005 studio. Call me old fashion, but it strikes me that the 2005 Studio has so many bells and whistles it's become bloated and sluggish.

I've now re-installed SQL2000 as a separate instance and applied SP4, restored the data from tape and am about to copy the 2005 data to 2000. When all is well, I'll uninstall 2005.

At some point I will bite the bullet and migrate my applications to VS2005 and then it will probably make sense to upgrade SQL to 2005 as well. For the moment, I'll stay a version behind. Thanks for your comments.