Transferring databases from SQL Server 2008 to SQL Server 2005?

Question

How to transfer database from SQL Server 2008 to SQL Server 2005?

I've set the Compatibility Level to 90, and created a backup.

An exception message says:

media family on device ... is incorrectly formed

There is no chance to establish connection between databases to copy data using SSIS wizards.

How can you effectively transfer a database from SQL 2008 to 2005?

Answer 1

I'm pretty sure it isn't possible to restore a database backed up by a newer version of SQL server at all, unfortunately.

While you can not connect the machines to perform an export using the wizards, would they perhaps support generating scripts containing both the structure and the data (much like a mySQL backup)? The standard tools can certainly produce scripts for sturcture and stored programability (procs, triggers, ...). There may be a little legwork exporting the data and reimporting, but it would be possible.

The other option is to install SQL2008 somewhere local to the SQL2005 instance and try do the wizard based transfer that way (restore to that SQL2008 and transfer by SSIS from there). There is a time-limited evaluation edition of SQL2008: http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx
Likewise you could try the same, installing SQL2005 somewhere local to the SQL2008 instance, transferring the stuff using SSIS to there and therefor being able to produce a SQL2005 compatbile backup file.

Answer 2

Once upgraded, a database can never be downgraded. Setting compatibility level only changes certain ways the query execution behaves, but has absolutely no impact on the physical format of the database: the 90 compatibility mode database is a SQL 2008 database, not a SQL 2005 one.

So if you need to deploy a database on SQL 2005 you must create it on SQL 2005. Not only that, but it has to be the same version of SQL 2005, meaning the same SP and CU level. Or at least a version that is earlier than the targeted server. So if the targeted server is SP1 CU3, then you can create the database on RTM any CU and on SP1, SP1 CU1, SP1 CU2 or SP1 CU3. But you cannot create it on SP1 CU4 nor on any post SP1 releases (like SP2). As I said, a database can be upgraded, but never downgraded.

You options are to either script out the database and the content into text SQL and then import the scripts into the target server, re-create the database on a proper SQL server version, or upgrade the target to 2008.

Answer 3

How big is the database? I assume you're not using any of the new 2008 data types.

You can script out the database schema in SQL Server 2008 & make the script compatible for 2005. After this, you can to do a BCP out of all the tables from 2008 & import them into 2005. You can achieve this via SSIS too, but BCP would be quicker (for me at least)