Compatibility Level: SQL Server 2005 (90)

Question

Hi, I have a database created in sql server 2008 R2 with the compatibility level set to sql server 2005 (90). I was hoping that I could create a .bak file and restore to sql server 2005. However, sql server 2005 does not seem to be able to.

'An exception occurred while executing a Transact-SQL statement or batch... The media family on device... is incorrectly formed. SQL Server cannot process this media family.'

Is there a way that I can create a bak file to acheive this?

Many thanks,

---

Jonathan

Answer 1

this might help :

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/c62dfa9c-dda3-44a3-95ba-1577f700fdfc/

http://sqlserverlearner.com/2011/the-media-family-on-device-is-incorrectly-formed-sql-server-cannot-process-this-media-family-error-3241

Answer 2

Hi,

Compatability level has nothing to do with the version of the database. As soon as you attach a database to a newer version of SQL server the database itself is upgraded which is a one way process. Once it is upgraded you can never take a backup or copy of that database back to a lower version of SQL server.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/04/26/what-s-the-difference-between-database-version-and-database-compatibility-level.aspx

http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-13-Difference-between-database-version-and-database-compatibility-level.aspx

http://social.msdn.microsoft.com/Forums/en-GB/sqldatabaseengine/thread/be4a070e-453f-4437-a6a5-fb0e5d91cfab

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Hi, thanks for these links.

My mistake is that I was under the impression that sql server compatibility level worked like it does in for example, MS Word. That is, a document created using Word 2010 and saved as type Word 97-2003 Document, can be opened using an earlier version of MS Word. However, from http://technet.microsoft.com/en-us/library/bb510680(SQL.100).aspx I get the the impression that sql compatibility level does not enable this at all. The purpose of sql compatibility level is so that the database will run in sql server 2008 r2 using sql server 2005 processes.

To acheive the outcome I want I have an option to create complete scripts, including data or actually use sql server 2005 to create the bak file.

Many thanks,

---

Jonathan

Answer 4

Thanks Sean for confirming this.

---

Jonathan