Database Migration – How to Migrate Large Databases in SQL Server 2008 R2

It really depends on your downtime window.

My preferred steps would be :

• Create a full backup of the SQL Server 2008 R2 database and restore it on the SQL Server 2012.

• Perform all of the post-restore steps.

• Add the database to an Availability Group.

  • With modifications as per your environment, you can probably automatically add databases using T-SQL script.

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.