compatibility between sql versions and applications

I have 2 or 3 applications that are only officially compatible up to sql server 2014.  I would like to move them to a sql server 2016 sql instance.  I would test of course.  Is there a way to make a 2016 server behave exactly like a 2014 sql server at the database level.  ie... some databases on this instance would be at 2016, some at 2014, and some at 2012 but the instance itself is officially running 2016
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
Set compatibility level to 120 for SQL Server 2014.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:

The compatibility level is a per-database setting.

BUT! It only affects SOME query behaviour. You will still need to test and test extensively to be sure that this works for you. Fortunately this change appears to be completely reversible with no downsides, and only requires a restart of the client connection to take effect. It's almost completely unnoticed by users.

In SSMS in it is the options tab of the properties screen.


Kind regards
Doug BishopDatabase DeveloperCommented:
See this page for more information.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

jamesmetcalf74Author Commented:
follow up question.
so lets say i move forward with moving a 2012 database to my sql server 2016 database and after using it for a week, it turns out it was a bad decision.  Now i have a weeks worth of data added while it was on the 2016 server.  am i allowed to backup the database at the 2012 level and then the 2012 sql server would be able to restore it?
David ToddSenior Database AdministratorCommented:

I think this is a no from me.

Pretty much Backups can only move forward and not backwards.

Just tested by creating a new blank database on 2016 and it gave an error restoring on 2012.

jamesmetcalf74Author Commented:
Thanks Gentlemen
i got what i was looking for
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.