DB move from 2008 to 2005

[rubikcube]

Is there any way to move the DB from a 2008 server to a 2005 server? I get the following error when trying to restore:

The media family on device 'E:\SQL BACKUP\FortressMove\sgmaster_move.sgvbak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is termination abnormally. (Microsoft SQL Server, Error: 3241)

Is this because 2005 can't read a backup from 2008?

[Beth]

I have never had a database be successfully moved to a previous version of SQL work. I thought it was supposed to be one of those impossible things. You might want to check with Microsoft directly.

If you have a backup of your database before it was upgraded to SQL 2005, then you might want to consider restoring that instead if you have to go to SQL 2005. Do you have a lot of changes since the upgrade to SQL 2008?

[jclausius]

A SQL Server 2005 database could not be restored/attached to an instance SQL Server 2000 because it is not able "downgrade" the format of the database files.

This might have been something MS addressed, but I would not be surprised if the same held true for SQL Server 2008 to SQL Server 2005.

[AjarnMark]

I know this is late in coming, and Rubikcube is probably well past needing an answer, but thought I'd throw out this idea for anyone facing a similar scenario... I am reluctant to espouse the use of another company's products one company's support forum, so let me just put it this way... There are SQL tools that will COMPARE schema structures and DATA, and will produce scripts that could be used to synchronize the two databases. So, if you were really stuck, you could install a fresh database to the SQL 2005 server, use the aforementioned tool to compare the empty database to the SQL 2008 database and produce scripts to insert the data into the 2005 version. Just an idea...

There's probably also a good way to accomplish much the same goal by using the Fortress API and build a custom migration tool. I did that in order to migrate out of Serena TeamTrack into Dragnet a while back and bring most of our history with us.

[Beth]

Thanks for the additional information. I'm sure some user will find it handy.

rubikcube: If you found a resolution, could you post it for the other users?

[chadslagle]

Using the method of taking a backup, and restoring will not work when doing a "downgrade". It is true, the format is different, and it is also possible that by default your backup is being compressed (a feature in 2008 that isn't available in 2005).

My suggestion would be to use the database copy utility in the SQL Server Management Studio. There are two methods, one just moves the data files, and another longer method actually moves the DB objects. You would need to start this process, and the job it needs to run, on the 2008 server, with the 2005 server as the destination.

But can't you also just run the database in 2005 compatibility mode?