Error restoring backup from SQL2K to SQL2008R2

Question

I'm restoring a SQL 2000 user database on a sql 2008 r2 box, but sometimes I receive the following error. This is also at the SQL Log.

Error: 928, Severity: 20, State: 1

During upgrade, database raised exception 156, severity 25, state 1, address 0000000001C05A9F. Use the exception number to determine the cause

It has occured during upgrade phase, after restore percent hit 100%.

If I retry with the same script, sometimes I get the same error, but if I wait a little the script completes successfully. I've run DBCC CHECKDB on the source and no error has been returned.

Compatibility level of the database is 80 (sql 2000) and version is 539.

While running Upgrade Advisor on the problematic database of the time I see 27 objects with the old outer join sintax *=, =*

Unfortunattly I won't be able to change the procs to check if this is the culprit. One other possibility is that there are a lot of procs that doesn't have BEGIN and END delimiting its extensions, and if I create them in a batch this confuses SQL.

Answer 1

Check the compatibility of the databases in SQL Server 2000: are they 65?

Error 156 is incorrect syntax, basically, and it's usually caused by compatibility mismatch. "65" is not supported in SQL Server 2008

Did you try the upgrade advisor before backup/restore?

Edit:

Outer JOIN. Hmmm

Just noticed that for SQL Server 2005 it's discontinued but for SQL Server 2008 it's deprecated. My guess is that the *0 and 0* syntax is breaking things, regardless of what the upgrade advisor and MSDN says. Personally I haven't used it since, er, I migrated to SQL Server 7 from 6.5...