I have recently come to realise an issue with database restoring in SQL Server 2005.
Depending on how the database is restored from another database, there are chances for some stored procedures to stop working (AFAIK this could be random), because their execution plan isn’t relevant anymore. The typical symptom is that when you run a stored proc, it simply sticks in the running state forever. This requires recompilation of the stored proc.
Recompiling stored procs is quite simple. This can be setup as automatic SQL jobs after each restore to prevent broken stored procs.
Recompiling all stored procs that access a key table
EXEC sp_recompile 'table_name';
Otherwise we can also target a specific stored proc
EXEC sp_recompile 'sp_name';
If identifying these stored procs is not realistic (due to its randomness), we can also consider recompiling all DB objects by using
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
sp_recompile does is simply marking the target object for recompilation. The objects will be recompiled when the next time it is accessed. Even though, the last option could take a long time because it loops through each table in the DB and recursivly marks all objects that depends on their recursive parent objects for recompilation.