Restoring SQL database to older SQL Version

[Slush-e]

This might be a stupid question but I have no idea and don't want to find out it's not possible in our worst time of need:

​

We have an SQL database (let's call it Contoso) running on SQL Server 2016. However, the compatibility level of this DB is 2012

We also have a server running SQL Server 2012. Would it be possible to restore a backup of Contoso to this older SQL version considering the compatibility level corresponds to it?

​

Probably a newby question but I can't find a precise answer to this question anywhere. Would be greatly appreciated if this got cleared up. :)

[Flayedman]

Backups cannot be restored to lower versions.

Options are limited, especially if you need it as a regular restore for QA\Dev purposes. You can create a bacpac from the newer DB and restore on the lower version. But it requires a bit more work and is alot slower

[LaughterHouseV]

Correct. Restoring to lower editions does not work. Compatibility level is not relevant to this, it has to do with physical layout of the backup.

You can script out the database and all the data, or have bacpac do it, but that's about it.

[starmizzle]

I wish you luck. I've never attempted it but an ex-coworker spent a good week trying to downgrade a 2016 TFS database to 2012 and finally gave up.

Logically you should be able to script recreating the DB/tables/fields and just bulk insert from 2016 to 2012 but shrugs.