The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, attach works across 32-bit and 64-bit environments. A database detached from a server instance running in one environment can be attached on a server instance that runs in another environment.
While trying to Attach a database using the .mdf and .ldf files which are detached from another SQL Server instance of different version, you may see below error sometimes.
The database ‘D:\TestDB.MDF’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘D:\TestDB.MDF’. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\TestDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
If you clearly look at the error message it says that the database version supported by the SQL Server instance where you are attaching is 665 and the .mdf file which you are using is of version 661(so this database may have been detached or the .mdf and .ldf files are copied from an SQL Server 2008 instance). In this case this error occurred because the .mdf file was from SQL Server 2008 R2 instance, but the attempt was made to attach it to SQL Server 2008 instance.
This is applicable to all versions of SQL Server, meaning, no version of SQL Server instance supports restoring or attaching an database of higher version to lower version. So, we can Restore or Attach databases from SQL Server 2005 to SQL Server 2008 R2, SQL Server 2012 or SQL Server 2014, but you cannot Restore or Attach databases from SQL Server 2008R2, 2012, 2014 to an SQL Server 2005 instance.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.