Developing with SQL Server 2005 and deploying to SQL Server 2000

Question

I'm currently developing a web application using SQL Server 2005, ASP.Net MVC, ASP.Net SQL Membership Provider (for authentication and authorisation), and Linq to SQL. Everything works great and is running as planned in the development environment.

However, the production environment has a SQL Server 2000 installation, and we are unable to obtain a copy of SQL Server 2000 to use in development.

Upon deploying the web application to the the production environment, what options do I have for migrating the database (schema + data) from 2005 to 2000?

Answer 1

You should make sure that your database compatibility in your development system is set to SQL Server 2000, and then consider using a tool such as the Redgate toolbelt for scripting out your schema and data for deployment.

Answer 2

Well, you could use the Database Publishing Wizard to generate a script of your database to SQL 2000 format.