My Server database is SQL Server 2008 R2. The data file is running with 2005 compatibility mode. My local PC has 2008 installed but it is not R2. When I try to attach any of the Server data files, it gives error of version difference.
In this case, do I need to upgrade SSMS or SQL Server whole installation?
You're taking a database from a 2008R2 instance and attempting to put it on a 2008 instance? If that is the case you can't take databases from a higher version to a lower. You can, however, take a lower version database to a higher. It has nothing to do
with SSMS. In order to use the database you'll have to install a 2008R2 instance or get the person who gave it to you to script out the database and values and give you the scripts to execute on your 2008 instance.
Also, for note, compatibility mode only changes some SET defaults and certain compile/execution items, it does not mean that you can attach it to a 2005 instance.
Proposed as answer byJanos BerkeSaturday, March 03, 2012 2:49 PM