Attaching a database (created in SQL Server 2012) to an instance of SQL Server 2014 without upgrading it

I’ve a database in a production server (SQL Server 2012) and I’ve a development server (SQL Server 2014).

I need to attach the database in the development server but I’ve realized that once attached it’s automatically upgraded to the version 2014 so I can’t attach it again in the production server.

Is it possible to attach the database in the development server without upgrading it?

We are planning to make changes in dev, detach, and attach it back to production.

No.

Several possible workarounds, none of them trivial:

1. Install a SQL 2012 dev instance (preferred).
2. When applying changes (both to structure and data) to your 2014 dev database, only do so via script. Ensure each change made is compatible with both SQL 2012 and 2014. Keep all these scripts you’ve created in dev, and when your work is complete, run them all on the production server.
3. Make changes to your 2014 dev database, and when you are ready to re-deploy to production, use the “Script Database Wizard” with the “include data” option, targeted for SQL 2012. Run this huge script in production.
4. If database size makes #3 impossible, use the Script Database Wizard for the structure only, then use SSIS or data import/export wizard to move all data across.
5. Make a dev copy of your production database on your production server (mydatabase_dev).

5 is probably the simplest and most straight-forward, if disk space allows. 2 is probably the one that most conforms to “best practices”, if you can’t install a 2012 dev instance. 3 and 4 are the easiest to mess up, if you miss a piece somehow. I’d avoid these if you can.

Clearly the best option, for the current need and whatever the next one brings, is to have a permanent dev environment that matches production as closely as possible. Obviously do this on a new server/vm or as a second instance in the dev server, not on the production box.

It is not possible for that upgrade to not happen. There are changes required for the database to be attached and usable on newer versions.

This is under-the-hood stuff like internal references and pointer formats, the exact internal changes from version to version aren’t documented. See here for some detail if only to satisfy your curiosity, but I would never risk trying to edit those bits directly.

It is possible to not change the compatibility level of the database, however you would still not be able to take a backup of that database and restore it to another SQL Server 2012 instance.

Because of differences in server/database versions your production and development servers should be on the same versions to really get the full benefit of testing in dev. If you aren’t going to upgrade production soon, then you could downgrade dev.