question

skbarik avatar image
skbarik 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?
sql-server-2008sql-server-2008-r2backupupgradecompatibility-level
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
skbarik avatar image skbarik commented ·
That means we can't restore a database from Higher version to a lower version even if changing the compatibility level,like SQL Server 2008R2 to SQL server 2008/2005/2000. Am I right @Grant fritchey...
0 Likes 0 ·
skbarik avatar image skbarik commented ·
Thanks Grant Fritchey for your valuable answer...
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.