asked
Compatibility Issue 2008 R2 to 2008
While restoring a database in SQL server2008 which is backed up in SQL server 2008 R2 i am getting an error that some compatibility issue. So i can't restore the database as i know that the compatibility level of both 2008 and 2008R2 is 100, So why this type of problem arises?
Please remember, the site runs on votes. For all helpful answers click on the thumbs up next to those answers. If any one answer directly solved the problem presented in the question, mark that answer by clicking on the check box next to that answer.
You can't restore backwards. Once a database is created in a newer version of SQL Server, there have been changes made to it, even in compatibility mode, that make it not possible to restore to an older version of SQL Server.
Yes. Going backwards just isn't possible through backup and restore. You can export all the database and import it, or use SSIS or a third party tool like Red Gate SQL Compare & Data Compare, but not backup and restore.
Not a problem. Remember, all helpful answers should get a vote on the thumbs up. If any one answer solved the question, indicate this by clicking on the check box next to that answer.
Database compatibility levels are distinct from SQL Server Database versions. A database that is hosted on a SQL Server will be of the version of that SQL Server and nothing else. When you take this database and move it by any means to a SQL Server that is a higher version, it will be upgraded to that version. You cannot move the database to a lower version of SQL Server. When it is first moved to a new SQL Server it will have a compatibility mode that matches the previous server version. This means that it will not have the features of the newer SQL Server version but crucially it will (more or less) have all the features of the old version. This means that you can often move a database to a newer server and not have too many problems with any applications that work with the database. As you make changes to you application you can change the compatibility level to the higher version and then make use of the new features. To have an older version of SQL Server host your data you will have to extract it and load it with some external process (@Grant Fritchey has mentioned Red Gate tools that can do this). There is no native method to move a database to an older version of SQL Server
Just additional information to the other two answers. There is a difference between [compatibility mode and database version][1]. You have run into the fact that the [version number][2] of the database changed between SQL 2008 and SQL 2008 R2, not the compatibility number. This is an internal number held within the boot page of the database that SQL Server will check when trying to bring the database online. Another good write-up [here][3]. [1]:
http://www.sqlskills.com/blogs/paul/search-engine-qa-13-difference-between-database-version-and-database-compatibility-level/ [2]:
http://www.sqlskills.com/blogs/paul/sql-server-2008-r2-bumps-the-database-version/ [3]:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx
you can generate the script for the database in SQL Server 2008 R2 with SQL Server 2008 compatability and run it on SQL Server 2008
17 People are following this question.
