Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)

Question

I'm migrating an ancient SQL Server 2000 database to 2012 (with a quick pass through 2008 R2 since 2012 is not compatible with 2000). The DB contains hundreds of views and stored procedures, and in my testing I'm now starting to see that some views are migrated in some old version. I did not even know that there were two (or more) versions of views in 2000, but it seems so.

In the cases I've found, so far, the effected views has been using the CASE-select, and I'm starting to think that it has been edited by using a "double click on the view" rather than "design". The version that is migrated to 2012 (and 2008) is clearly from a few years old, and after resaving the offending view in design-mode in 2000, and migrating again, it gets the correct version.

But I can't do this manually for hundreds of views. Is there any way to force an version-sync of sorts in 2000? Any other ideas on a solution is much welcomed.

[Edit/Additon]

I've kept testing, and I have now narrowed down where the problem occurs. The SQL Server 2000 backup restores fine in SQL Server 2008 R2, and the view (in question) is up to date. But then I run a few checks and updates, and after running sp_refreshview on all views, it reverts back to an old version (that was not even visible in 2000 this time).

In summary: why does sp_refreshview, from SQL Server 2008 R2, revert some of my views back a this-was-the-original-design-of-the-view-in-2001 state?

Having found more info I had an easier time finding similar problems: http://sqlblog.com/blogs/davide_mauri/archive/2010/10/12/bug-with-sp-rename-sp-refreshview.aspx But I'm still unsure on how to fix this issue since I don't know how many views are effected with this "bug".

Answer 1

So I finally found the error, and I'll just have to cross my fingers that this was a lone occurance.

The view that kept reverting, after a sp_refreshview in SQL2008 r2, was named "qryCreateSnapshot". Then there was another view, a backup of sorts, named "qryCreateSnapshot_Old". The problem was that in the old view, the body was still "CREATE VIEW dbo.qryCreateSnapshot". I have no idea why this even compiled, but there you have it.

So when I refreshed in 2008, I guess the new view got overwritten with the old. This did not happen in 2000.

Now to compare hundreds of view names with their respective body name... ;-)

For anyone in a smiliar situation, here is a small query to find mismatches between header name and body name of a view:

SELECT     
  O.name AS HeaderViewName, 
  RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) AS BodyViewName, 
  CASE 
     WHEN RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) = O.name THEN 'Yes' 
     ELSE 'Nope, fix body' 
  END AS Match, 
  C.text AS SourceText
FROM sysobjects O 
INNER JOIN syscomments C 
   ON O.id = C.id
WHERE O.type = 'V' 
AND   LEFT(C.text, 12) = 'CREATE VIEW ';

I know it's not perfect, but it worked for my data and I found another ~15 mismatches.