Restore SQL Server 2008 database to SQL Server 2000

Question

I have to move an entire database from a SQL Server 2008 machine to a SQL Server 2000 machine.

I created a backup using Management Studio 2008, copied it to the hard drive of the 2000 box, and from withing Management Studio 2008, I choose Restore Database to the 2000 box.

I get an error message stating, "The media family on device ... is incorrectly formed. SQL Server cannot restore this media family".

If I use Enterprise Manager 2000 I get the same error.

Is there a way to move a whole database from the newer SQL server to the older?

Answer 1

only thing i can think of is to recreate the whole structure and then copy data from a live database. so, create scripts that will create the tables and views and sp's, and then create scripts to copy the data from the existing database.

Answer 2

I've heard you can only do it by generating the SQL statement dump from the DB administrator tool and re-running those queries on the target older database.

Answer 3

you can't move backups from a newer version to an older, in that case you can script your database, execute it in the 2000 box, then you can use the standard data transfer to transfer any data you want

Answer 4

You can generate a script that will recreate all the objects and transfer all the data...as long as everything in the db is valid in SQL 2000. So no ROW_NUMBER(), no PARTITION, no CTEs, no datetime2, hierarchy or several other field types, no EXECUTE AS, and lots of other goodness. Basically, there's a pretty good chance it's not possible unless your db is pretty basic.

Answer 5

Provided you have a network connection between the machines use SSIS. Much easier and a lot less messing around.