Convert SQL Server 2008 to 2000

I'm looking to convert a database that was converted to 2008 format back to 2000 format. I've tried all the methods a开发者_高级运维vailable in SQL Server Management Studio. Export data, script database, all that, and they all fail. Export doesn't seem to export everything needed, and the generated scripts always contain errors. The database is so huge, its impossible to find the errors in the generated scripts.

Does anybody have any advice.

You'll have export/import using SSIS/DTS (whether wizard or manual) or SMO. Or use a 3rd party compare tool such as Red Gate SQL Compare

There is no downgrade option

Here is a link related to sql server database downgrading: http://weblogs.sqlteam.com/mladenp/archive/2007/02/15/60101.aspx

Also, there's a thread in serverfault related to this subject: https://serverfault.com/questions/28856/move-downgrade-database-from-sql2008-to-sql2000

You can use sql script generator to extract database defination and data and run that script in sql 2000.

This is easily available free of cost from http://www.sqlscriptgenerator.com/

Maybe you should go the other direction. Create a new, blank database on SQL 2000 and use the Import feature to pull from SQL 2008. As long as the SQL 2008 database doesn't use features specific to 2005/2008 (which it shouldn't since you said it was converted from 2000 already), then you should be fine. The only issue is when importing objects that have dependencies. In this case, for example, make sure to first import all tables that act as the PK to other tables' FKs. Then do Functions. Then do Views, since you may have store procedures that depend on those views. Then do SPs. You may have to manually script certain objects and put them in a specific order if you have overlapping dependencies.

Unfortunately there is a bug in generate scripts option in SSMS and the only way to do this easily in reasonable amount of time is to use third party database comparison or scripting tools.

Apart from SQL Compare that’s already mentioned I’ve also used ApexSQL Diff in the past for similar projects. You can’t go wrong with any of these. Both tools are top notch.

The reason Generate Scripts option creates scripts with errors is that it doesn’t take into account dependency order and that comes back to the bug that exists in sp_depends.