Attached database to SQL Server 2016 and now won't work in SQL Server 2014

Question

I have a SQL Server 2014 database (on a portable hard drive) which I had to attach to a SQL Server 2016 machine using the normal attach method.

However, since attaching it to that computer, it no longer works on the SQL Server 2014 computer even though I didn't change the compatibility level or anything else for that matter.

I'm now stuck because SQL Server 2016 will not work on Windows 7 machines which is what all the other computers run on. I just happened to attach it on a laptop running Windows 10 and SQL 2016 thinking it wouldn't be a problem.

Is there any way to reverse this situation? I'm not able to get any good information from the error screen in management studio.

Answer 1

When you attached the mdf file(s) to the SQL Server 2016 instance the database was upgraded. The files were marked as being of the new version. There is no downgrade path in SQL Server.

The best option is restore from backup. Else, you many be able to script the schema and data and build a new database on the 2014 instance which is identical to the previous one. Here's one way of achieving this. Be aware of the things the tool may not script, like users, permissions, synonyms etc.

See also: How to move a database from SQL Server 2012 to SQL Server 2005