I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2. My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of these is not an option.

How should I check?


Your question is too wide I am not sure a clear answer can be given but I would try. As such sitting from here I cannot tell you what could break after chaning compatibility level. Only you can find it out by running each and evry procedures,functions, views and triggers you have.

Microsoft has documented breaking changes in SQl server 2008 R2 before changing the compatibility level you must go through this article.

You must also read Change database compatibility level BOL document to see what could get affected after changing compatibility level. Please read complete article most of the things are documented.

Did you ran upgrade advisor before you migrated database to 2008 R2 ? that should as well have pointed out breaking changes.

The good thing with compatibility level is it can be immediately changed to previous value if you face issue. So you also have got hit and try method to check out

  • thank you for your quick response!! i migrated to 2008 R2 long time ago but left some databases with compatibility level 80. Now I'm thinking to upgrade to 2014 but before that step I think to upgrade all my databases to 100. Should I run the upgrade advisor on 2008 R2? – RITA Aug 4 '15 at 10:03
  • You can upgrade database directly from 2008 r2 to 2014. But first change compatibility level to 100 and see every thing works fine. After you are done with this run SQl Server 2014 upgrade advisor and then accordingly proceed with upgrade. – Shanky Aug 4 '15 at 12:50

There has been no changes to structures or schema of "stored procedures, functions, views, triggers " , so you do not really have to change anything. If you were going from 2008R2 to 2012, all you have to worry about is the date functions. The rest is backward compatible.

Not the answer you're looking for? Browse other questions tagged or ask your own question.