Error when trying to restore SQL Server 2016 backup on SQL Server 2014 with compatibility mode 110

Question

I'm getting an error when trying to restore a SQL Server 2016 backup on SQL Server 2014 with compatibility mode 110

Msg 3169, Level 16, State 1, Line 38
The database was backed up on a server running version 13.00.4001. That version is incompatible with this server, which is running version 12.00.5532. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Msg 3013, Level 16, State 1, Line 38
RESTORE DATABASE is terminating abnormally.
An error was encountered during execution of batch. Exiting.

Upgrade to SQL Server 2016 is not a option for this solution.

Answer 1

You CANNOT ever restore a backup from a newer version of SQL Server (e.g. 2016) onto a server with an older version (e.g. SQL Server 2014) - there's no documented (or undocumented) way, no method, no workaround, no hack to achieve this - it's just not possible.

The internal database structures are just too different to provide a "backwards" compatibility path. And fiddling with the compatibility level also doesn't help, since this doesn't really change the internals of the database file - it just enables/disables certain T-SQL features.

The simplest solution is to just use the same version of SQL Server on all your systems. That way, you can easily back up from one machine, and restore that database onto another one.

If that isn't possible, then you must use T-SQL scripts to transport changes in structure and/or contents (data) from the newer version to the older. You can use tools like Redgate SQL Compare and SQL Data Compare to help you with generating those T-SQL scripts, or you can author those scripts yourself. Or you build some other means of synchronizing the two databases with each other.

Or if you need to move "only" the data, you could check into the Import/Export wizard, and dump your data from SQL Server 2016 into an intermediate format (Access, Excel) and re-import it on the older SQL Server from there.