SQL Server – Attaching Database Created in SQL Server 2012 to SQL Server 2014 Without Upgrading

attachsql server

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.

Best Answer

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.

Related Question