Restore SQL Server 2008 DB *to* SQL Server 2005

Question

Got myself in a bit of a pickle here ... working on a CMS project, under the assumption that sql server 2008 was greenlighted as the db of choice. Well it wasn't, we now have to backport all of our content out SQL Server 2008 and into SQL Server 2005.

A simple backup/restore procedure yields: "RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)".

Unfortunately, exporting the data to an excel spreadsheet yields multiple OLE errors which I believe is actually a problem in the db of the cms.

Does anyone out there have other approaches they would like to recommend for this task? Thanks in advance

Answer 1

I only use mysql but can you export your data into sql statments, and then import then into sql2005? Just a thought..

Answer 2

Rather than do a backup and restore you might try using SQL 2005's Import/Export Data wizard.

http://support.microsoft.com/default.aspx?scid=kb;en-us;314546

http://msdn.microsoft.com/en-us/library/ms140052(SQL.90).aspx

Answer 3

It's not possible to restore to previous versions in SQL Server

Is there no SQL 2005 backup around? Otherwise you really are limited to export the entire database in 2008, and re-import back into 2005, or the Import/Export wizard in 2008

Or rely on 3rd party tools. e.g. Red Gate Data Compare is able to sync. the DATA between 2 servers/databases

Answer 4

I faced a similar problem (sql 2005 to sql 2000), and found that I happened to have a blank database at the older version. I used bcp.exe to copy all the data.

Answer 5

Use RedGate

http://www.red-gate.com/products/sql_data_compare/index.htm

Answer 6

Use the Generate SCripts to create the database and schema and ensure you target SQL Server 2005 and script data.