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?

Delectate

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.

Biometry

I've just finished doing this myself. The only way I found to do it was to script the entire database (including data) and run the script on the target machine. Now, this is a pain, because the 2008 DB may create a script that won't run on 2000 (well, mine did) so you will need to edit the generated script before running in 2000. Stock up on Tylenol before you start. Good luck. – Changeup

Yes, that's the only way to do it. SQL Server has never been backwards compatible, e.g. you have never been able to restore a newer version to an older server. I don't think this is likely to change – Fossilize

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.

Pudgy

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

Mannered

Answer 4

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

Benzyl

Answer 5

You can use Script Generator for your database and then select in the properties form : General-> Script for server version : SQL Server 2000.

The script generator will show you things which not compatible with your server version.

Primp

Answer 6

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.

Hexavalent

Of course you'll have to hand manipulate them as the script tool puts in things that 2000 won't recognize. – Marlie

Answer 7

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.

Gerena

Answer 8

We got a similar situation. A very low-tech but handy solution is:

1. backup and truncate the tables in SQL 2000.
2. create a LINKED server in SQL 2008, pointing to SQL 2000
3. run a select query at sysobjects to generate a query script for insert into LINKED SERVER.table select * from table
4. execute query script.

Willpower