We have a database that was developed in SQL2008. We want to put it into production. Unfortunately, our Production DB Server is SQL2000.
What is the best way to migrate the DB.
Note the DB is simply a collection of Datatables that have been created from our ERP system. There are no Stored Procedures/Triggers, and nothing fancy about the data, so I'm not worried about running into an issue where the DB is using new features that 2000 won't handle.
TIA
You'll have to export the tables and import them into the SQL2000 db, backup/restore or detach/attach won't work for downgrading.
In SSMS (for the 2008 db) right-click on the database and choose Export... from the Tasks submenu. Then follow the wizard to connect to your SQL2000 server.
Keep in mind that some of the SQL2008 datatypes are not compatible with SQL2000.
As squilman said not all datatypes (such as date) are supported by SQL 2000, also if your database uses CLR SPs or functions it won't be possible either.
My recommendation would be to use SQL Compare (there's a trial available) that should allow you to compare and push the schema across. I am unsure if you can compare both databases directly but if not you should be able to dump the development box schema to files and then compare the files to the production server which also gives the option to create the entities.
Assuming you get the schema re-created you will then need to transfer the data. For this you can either go table by table with INSERT statements using a linked server as the source/destination or use SSIS.
Good Luck!
Can you just export the table SQL data?
It appears Microsoft doesn't have much support for this... Surprise! My best guess would be to export as much as you can of the SQL 2008 database into straight SQL, and write a script to create the tables in SQL 2000 and add the data.