A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should guarantee that they are compilable and executable.

There are many stored procedures and triggers to check. To do it manually, I would need maybe 1 month. So, I want to take another approach.

What kind of checking procedure would you recommend me, so that I can say surely that the stored procedures and triggers will also work in SQL Server 2008?


2 Answers 2


Generally speaking, if you just reapply of the stored procedures and no errors show up, you should be good to go (based on my experience from migrating 2 databases (2000->2005) (2005->2008). On a side note, this is why we started writing unit tests for our database code to handle problems like these.

  • Do you mean that applying the sps and getting no syntax errors would also mean that the execution will be ok? (All the original SPs are assumed to be working fine in sql2005) I somehow need to write a report as a conclusion. Do you have any sources that I can reference to in that report?
    – EngelbertCoder
    Commented Nov 28, 2012 at 10:20
  • @EngelbertCoder Yeah, that is exactly what I mean. It's not fool proof but I do not think a fool proof solution exists. Additionally, it's worth noting that if your just upgrade the server from 2005 to 2008, the database doesn't upgrade with it. That requires you to change the compatibility mode on the database itself by going to it's properties. As for the sources, this all based on my personal experience. Sorry I don't have a more concrete answer.
    – mikerobertking
    Commented Nov 28, 2012 at 10:33
  • The 'compatibility level' confuses me a bit. So, if the compatibility level on SQLSERVER2008 DBs are 'SQL Server 2005 (90)', does it mean that 2008 should be compatible with 2005 but we will not be able to use the new features introduced by 2008Server?
    – EngelbertCoder
    Commented Nov 28, 2012 at 11:32
  • Are you really suggesting that as long as your code compiles it will absolutely work fine when executed? Commented Nov 28, 2012 at 14:56

Is the 2005 instance still your production instance? If so, you could use a server side trace to capture a workload and replay it on the 2008 instance. You should try to capture your normal workloads. You have to decided what is normal in your system. This wouldn't guarantee that all stored procs will work, but you day to day activity should be fine.

As far as compatibility levels please review the BOL link: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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