Search This Blog

Thursday, September 10, 2009

Upgrade from SQL 2000 to SQL 2008 failed

In-place upgrade from SQL 2000 to SQL 2008 failed with error

Error Description
Error: 33009, Severity: 16, State: 2.
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'msdb'. You should correct this situation by resetting the owner of database 'msdb' using the ALTER AUTHORIZATION statement.

Error: 912, Severity: 21, State: 2
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 33009, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

ROOT CAUSE
Different owner of msdb and master caused upgrade script to fail.

1) Run select name,owner_sid from sys.databases
It would Return something like this 0x0105000000000005150000004E69B for msdb and 0x01for master.

2) We can see it’s because owner SID is not same for master and msdb.
Now Start SQL from command prompt before that ensure SQL Server service is stopped.

net start mssqlserver /f /T3608

3) Exec sp_helpdb ‘msdb’
It will show owner as which is not matching with master. where the master’s owner was sa.

4) Exec sp_changedbowner 'sa'
This will change the owner as sa for msdb

5) ALTER AUTHORIZATION ON DATABASE::msdb TO [sa]

6)After this stopp and start the sql from command prompt and run Repair option

7) Repair completed smoothly

2 comments:

SQLORCL said...

This is extremly great article and i had this issue during one of the upgrade and i follow your instruction and it work successfuly.

Hentie said...

Thank you so much! People on Experts Exchange couldn't even help me with this.