Convert SQL Server Database from 2005 to 2000

Question

We have a Microsoft SQL Server 2005 database that needs to be converted back to SQL Server 2000. Does anybody have any suggestions on the approach or tools I should use to perform this conversion? We don't utilise any SQL Server 2005 specific features in the database so this should not make the conversion problematic.

I should mention that I have found the Microsoft SQL Server Export facility very buggy in dealing with Auto Generated Keys so suggestions for alternative tools would be appreciated.

Answer 1

1. Script out the entire database
2. Create a new SQL2000 database
3. Run the script on the new database
4. Import the data from the SQL2005 to the SQL2000 database (lots of ways to do this)

There probably is a tool somewhere that does all this for you, although I don't know how commonly this is done.

Answer 2

Generate a full script for your database in SQL2005, and change the "Script for Server Version" option to SQL Server 2000. You can now recreate your database on the SQL 2000 server. After this is complete, use the export data feature to export from SQL 2005 to SQL 2000.

Answer 3

Get a trial of RedGate SQL ToolBelt, then use:

1. SQL Compare to transfer the database
2. SQL Data Compare to transfer the data

Answer 4

What you want to do is called downgrading. Google gives lots of results, but here's a good walkthrough on a thread of Microsoft's forums (scroll down near the bottom of the page).