February 12, 2013 at 1:23 am
I have a lot of confusion with respect to backward compatibility. I have gone through a lot of articles but most of them were confusing.
Please see http://msdn.microsoft.com/en-us/library/bb510680.aspx . Versions of SQL Server support compatibility for two previous versions according to this article that tells how to set the database for that backward compatibility.
Here is a blog by one of the SQL Server Guru’s: http://blog.sqlauthority.com/2012/12/25/sql-server-restoring-2012-database-to-2008-or-2005-version-and-2-other-most-asked-questions/
My understanding is this:
For example, we have a database created in SQL Server 2012. We have an application which is configured to work with this database and it encounters some issues. If this application was working fine with SQL 2012 or SQL 2008, we can downgrade the compatibility of the database within the same instance to level 90(2005) or level 100(2008) from level 110(2012).
As far as my knowledge goes, we can neither restore a SQL Server 2012 database into SQL 2008 or SQL 2005 nor attach the data and log files from a higher version to a lower version.
Please help me understand this concept better.
February 12, 2013 at 1:35 am
Benki Chendu (2/12/2013)
For example, we have a database created in SQL Server 2012. We have an application which is configured to work with this database and it encounters some issues. If this application was working fine with SQL 2012 or SQL 2008, we can downgrade the compatibility of the database within the same instance to level 90(2005) or level 100(2008) from level 110(2012).As far as my knowledge goes, we can neither restore a SQL Server 2012 database into SQL 2008 or SQL 2005 nor attach the data and log files from a higher version to a lower version.
Correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2013 at 1:38 am
Thanks Gail. Have u seen a better comprehensive article?
February 12, 2013 at 7:12 am
I researched further more on this and the information that I had shared earlier looks correct.
Here is another illustration where a SQL Server 2008 Database is being downgraded to SQL Server 2005. But its only the compatibility of the database that’s reduced but we cannot backup and restore the database. All we are doing here is, making a SQL 2008 database act like a SQL 2005 database.
http://www.mssqlfix.com/2011/12/change-database-backward-compatibility.html
So, my question is, once we change the compatibility to a lower version, can we not backup that database and restore on a instance with a lower version.
Hope my query is not confusing.
February 12, 2013 at 7:23 am
Benki Chendu (2/12/2013)
So, my question is, once we change the compatibility to a lower version, can we not backup that database and restore on a instance with a lower version.
No. Compatibility is just that, compatibility. You're making the SQL 2008 database behave as if it were a SQL 2005 database. You're not making it a SQL 2005 database, it's attached to SQL 2008, it's a SQL 2008 database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply