Restoring a SQL Server 2008 R2 backup on SQL Server 2005

All we need is an easy explanation of the problem, so here it is.

I’m getting an error whenever I restore a testing database on SQL Server 2005 that was backed up from SQL Server 2008 R2. It seems that I cannot restore from a higher version of sql onto lower version, how can I solve this?

The media family on device is incorrectly formed. SQL server cannot process this media family. RESTORE HEADERONLY is terminating abnormally.

I already tested to restore the same file in its own server (SQL Server 2008 R2) and it works, so it’s not corrupted.

I cannot install the updated version of SQL into my older server, so how can I use my older SQL Server 2005 as a dev server but with the updated life data from a different instance?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can’t restore a 2008 backup on SQL Server 2005. Simply not possible.

You’ll need to either:

(a) Restore it on 2008, then use Import/Export wizard, Generate Scripts Wizard, Copy Database Wizard, SSIS, 3rd party tools like SQL Compare, etc. to move the structure and data to the 2005 instance, or

(b) Upgrade. Developer Edition is $50 or so.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply