Creating SQL Server 2000 database using SQL Server 2008

Question

My development machine has SQL Server 2008 Developer edition on it. A production server I am going to do some development for has SQL Server 2000 on it. Is there a way to create a 2000 database using my 2008 developer edition? Or do I need to create it on the 2000 server and move it to my development machine?

Answer 1

yes, the database can;'t be restored from SQL 2008 even with 80 compatibility on SQl 2000 instance. If you have to restore , script the DB and objects and recreate them on SQl 2000. -Ashok

Answer 2

I suggest you try it this way:

If your SQL 2008 database, right click, select Tasks, Generate Scripts. Now select your database, select your options in the next window (like script Drop, script Data...), continue through the wizard until Finish.

Now SQL will make your qualified script and it is ready to run.

Answer 3

create a database in 80 compatibility mode and you should be ok with regards to sql you use. but you can't restore a 2008 db on a 2000 server.

Answer 4

It will not be possible to move the development database into production. Once a database file has been upgraded to the SQL 2008 format, it is impossible to downgrade to SQL 2000 format.

You should focus your development on creating T-SQL scripts instead of creating database objects. This includes initial database creation and any subsequent schema changes. Perhaps you can use a version based approach for your schema and catalog data. As long as you don't use any SQL 2008 specific functionality, the scripts will run fine on SQL 2000.

Setting the db compatibility level to 80 on development will help making the behavior of the 2008 server closer to the 2000 server, but it does not mean the 2008 specific features will not be usable. You have to pay attention and make sure every functionality and feature you use in development will also be available in SQL 2000.

Answer 5

It depends on how you're going to get the new database back to the production 2000 server; if you create it in your 2008 instance, you can't back it up and restore it to 2000, even if you create it in compatibility 80 as Mladen noted; but you could generate scripts to export your database to the 2000 instance, as long as you don't have to copy any of the data in the tables.

If you need to do a full backup and restore of the new database, you'll be better off with a 2000 instance to connect to for development, MSDE could be installed as a named instance alongside the 2008 edition.