So I restored a backup from from MSSQL 2005 to a MSSQL 2008 database, which kept the database in “SQL 2005 compatibly mode”. I thought that this would mean that the database could actually be backed up from the MSSQL 2008 server to the MSSQL 2005 server, however it turns out that this is not the case. Backing it up from 2008 and trying to restore it, resulted in the the following slightly cryptic error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
The media family on device ‘C:\Temp\db.bak’ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
After some googeling it turns out that MSSQL 2005 can not read backups from 2008. The solution I found to the problem was to export the database as a SQL script, and running the script on the SQL 2005 server. Maybe not the best solution, but it saved my day.
This is how you create the script file:
In Object Explorer in SQL 2008 management studio, right-click the database, select Tasks->Generate Scripts. In the options dialog enable everything, including Script Data. Make sure you select “Script for SQL 2005″ (otherwise you will export a SQL 2008 script file). Then run the script on your SQL 2005 server and hopefully you are done!
