Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
1  
Time is one of the datatypes that isn't compatible; I found this out the hard way. Lesson learned: use the same version next time. –  Scott Jun 19 '09 at 19:29
    
Luckly, the date fields seem to convert to datetime and do not affect the application. I know we should use the same version, this one started as a Q&D need to gather data into a format that will let us do some special analysis, just this once kinda things, so we let a developer do it local on his desktop. Now we need to give others access to the data, so I had to move it to the main server. Squillman's answer was spot on. –  BillN Jun 19 '09 at 22:50
add comment

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!

share|improve this answer
    
No SP's. Just a few dates, and I can live with changing them to datetimes. The export wizard is running now. –  BillN Jun 19 '09 at 22:51
add comment

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.

share|improve this answer
    
Depending on the number of tables and number of indexes... might even want to just export to .CSV, import into SQL2000, then create your indexes, field properties, etc. –  KPWINC Jun 19 '09 at 19:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.