Does the compatibility level for sql server work reliably for scripts?

We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some customers still use).

So my question: from your experience/knowledge, is this really necessary, or does setting the compatibility level back to 90 on SQL Server 2008 (2008 R2) fix the issue of unnoticed breaking scripts in SQL Server 2005? MSDN says "compatibility level provides only partial backward compatibility with earlier versions of SQL Server ", so I'm unsure here.

Thanks...

I would like a concrete list of “features will pass compatibility level 90 but break on SQL Server 2005" or link. If the list is short list, we could convert that to an internal "don't do that!" list and save a lot of work.

No the compatibility level is to allow deprecated features to work not to prevent you from writing code that won't work on a lower version.

If your production machine is SQL Server 2005, you must develop on a SQL server 2005 machine to ensure that features allowed in 2005 are the only ones in the code.

Of course in Books online there is a page which shows all the features that are new in 2008, so you could simply forbid your developers from using them. And check for them in code reviews, but developing against a correct database is the only real solution.

The list is not short and exotic, the compatibility mode is not intended to allow you to develop on a higher database and deploy to a lower one.

StackExchange Database Administrators Q#6731, answer score: 8