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

The 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, views, and sp's, and then create scripts to copy the data from the existing database.

Answer 2

As others already said there is no default way to do this. It’s just not supported. Here are more extensive details on how to do this properly and avoid any migration issues.

You need to generate scripts for structure and data and then execute these on SQL 2000 (like others already said) but there are couple things to take into account.

Generate scripts in SSMS

• Make sure to check option for scripting data for SQL 2000 to avoid issues when trying to create something like geography type column on SQL 2000.
• Make sure to review execution order of scripts to avoid dependency based errors

This is a great option for small to medium size databases and requires some knowledge of SQL Server (dependencies, differences between versions and such)

Third party tools

• Idea is to use third party database comparison tools such as ApexSQL Diff or Data Diff
• Good side is that these will take care of script execution and differences between versions
• Not so good is the fact that you’ll need to pay for these after trial ends
• I’ve used these two tools successfully but you can’t go wrong with any other tool on the market. Here is a list of other tools in this category.