Error On Upgrade From SQL 2005 Database

  • mnDBA

    SSCrazy

    Points: 2032

    On occasion, when restoring a SQL 2005 database to a new SQL 2008 instance for testing, I get the following error:

    Error: 928, Severity: 20, State: 1.

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

    It is coming right after "Database 'xxx' running the upgrade step from version 628 to version 629." So either it is the step from 628 - 629 or from 629 - 630. Regardless, i cannot seem to find a lot of info on this error.

    The thing that is making me nervous is that it is not constant. Sometime it happens, sometimes it does not. I have yet to be able to discern a pattern.

    Any ideas?

  • mnDBA

    SSCrazy

    Points: 2032

    mnDBA (10/13/2010)


    On occasion, when restoring a SQL 2005 database to a new SQL 2008 instance for testing, I get the following error:

    Error: 928, Severity: 20, State: 1.

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

    It is coming right after "Database 'xxx' running the upgrade step from version 628 to version 629." So either it is the step from 628 - 629 or from 629 - 630. Regardless, i cannot seem to find a lot of info on this error.

    The thing that is making me nervous is that it is not constant. Sometime it happens, sometimes it does not. I have yet to be able to discern a pattern.

    Any ideas?

    This is interesting.....

    After I receive the error, the database is left in an unusable state. When this has happened in the past, I just dropped it and ran the restore again. This time, I just reran the restore. It would appear that the upgrade to 2008 started up again where it left off. This time I got an actual error.

    The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

    This error I can deal with and the other part of it even tells me the proc that is causing it.

    However, I am curious as to why this error is not being thrown all of the time.

    Thoughts?

  • mnDBA

    SSCrazy

    Points: 2032

    I love solving my own problems! 🙂

    So, the restore process I am using is wrapping some restore code up in a custom proc that allows some of our developers to run restores. Anyways, I encapsulated the restore code in a TRY/CATCH block. It would appear that although this "warning/error" does not stop a restore from continuing.... my TRY/CATCH block catches it and stops.

    That's what I get for trying to be accommodating! 🙂

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    I know this thread is super-old but I am in the same boat...did you ever figure how to bring back the wrapper proc?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply