Answered by:
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
Tuesday, November 20, 2012 8:13 PM
Answers
-
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.
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!- Edited by Sean Massey Tuesday, November 20, 2012 9:04 PM
- Marked as answer by Kiwi Jonathan Tuesday, November 20, 2012 9:14 PM
Tuesday, November 20, 2012 9:02 PM -
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
- Marked as answer by Kiwi Jonathan Tuesday, November 20, 2012 9:14 PM
Tuesday, November 20, 2012 9:10 PM
All replies
-
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
Tuesday, November 20, 2012 8:55 PM -
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.
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!- Edited by Sean Massey Tuesday, November 20, 2012 9:04 PM
- Marked as answer by Kiwi Jonathan Tuesday, November 20, 2012 9:14 PM
Tuesday, November 20, 2012 9:02 PM -
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
- Marked as answer by Kiwi Jonathan Tuesday, November 20, 2012 9:14 PM
Tuesday, November 20, 2012 9:10 PM -
Thanks Sean for confirming this.
Jonathan
Tuesday, November 20, 2012 9:14 PM