SQL 2008 R2 upgrade error: Wait on the Database Engine recovery handle failed

While preparing for an upgrade of a complex SQL Server 2000 installation to SQL Server 2008 R2 I had to figure out a way of reusing the MSDB database of the original SQL Server 2000 installation on the new SQL Server 2008 R2 setup. The reason is the third-party job scheduling tool refers to the jobs with their job_id and neither scripting the jobs or using the SSIS ‘Transfer Jobs Task’ gave me the possibility to keep the job_id. The easiest way to accomplish this is:

  • Backup the original SQL Server 2000 MSDB database
  • Restore the SQL Server 2000 MSDB database on a temporary SQL Server 2000 with the same patch level as the original
  • Do an in place upgrade of the tempory SQL Server 2000 instance to SQL Server 2008 R2
  • Backup the upgraded SQL Server 2008 R2 MSDB database
  • Restore the upgrade SQL Server 2008 R2 database on the newly installed SQL Server 2008 R2 server

Unfortunately trying out this approach I stumbled upon the following error during the in place upgrade from SQL Server 2000 to SQL Server 2008:

 The SQL Server error log during setup is stored in ‘%programfiles\Microsoft SQL Server\100\Setup Bootstrap\Log’. In this folder a ‘Summary.txt’ is found with the summary of the last install attempt. Looking in the newest sub folder more detailed log files are found. Opening the ‘SQLServer_ERRORLOG_somedatetime.txt’ and scrolling down gave me this error:

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.

 And off course, since the msdb database is owned by the SQL Login ‘sa’ the SID of the ‘sa’ account on the originating server is not the same as the SID of the ‘sa’ account on the upgrade server. To correct the situation and let the upgrade continue I ran the following commands from a command prompt:

Stop the running SQL Server Service:

net stop mssqlserver

Start the SQL Server services with these trace flags so the upgrade scripts don’t start running again:

net start mssqlserver /f /T3608

Use a dedicated admin connection to connect to the SQL Server Instance and change the owner of the MSDB database back to the ‘sa’ login:

sqlcmd -E -A
USE msdb
GO
sp_changedbowner ‘sa’
GO
EXIT

Use the net stop mssqlserver command again to stop the SQL Server Service.

Now I can restart the setup program of SQL Server 2008 R2 but instead of choosing a new installation I need to choose to REPAIR my latest installation. After the repair finishes I finally have my upgraded MSDB database. And when restoring the database to my target server I must keep in mind to change the owner of the restored MSDB database to the ‘sa’ login of the new server.

Advertisements

10 Responses to SQL 2008 R2 upgrade error: Wait on the Database Engine recovery handle failed

  1. Brent says:

    Hi. Thanks for the info. I’m trying to do my update in the extact same fashion (with the temp server etc.) as you and got the same error. I’m not an expert in SQL Server and have a question maybe you coud further explain. I want mine to run all the way thru without the error and not have to do the Repair. So if I did the “USE msdb; GO; sp_changedbowner ‘sa’” BEFORE I start my update then it should run all the way thru without this problem?
    Thanks

    • axel8s says:

      Brent,
      It depends on the exact error messages you’ve got in the errorlog. But changing the dbo is one thing you have to do. If there are no other problems you’re upgrade should run fine. Good luck

  2. Brent says:

    I’m not sure if I’m using the cmd line correctly. I do “USE [dbname]”, “GO”, “select owner_sid from sys.databases where database_id=db_id()”. For Master, I get “0x01”. For MSDB and user DBs, I get something like “0x010500000000000000000515000000…”. (I’m doing this on the failed update test server.) The way I started before the update was to take a backup from the production server and restore it to the test server. I’m guessing that is what did it. So on the DBs that are not “0x01”, do I do both of the following or one of them?
    “USE “, “GO”,
    “Exec sp_changedbowner ‘sa'”
    then
    “ALTER AUTHORIZATION ON DATABASE TO ‘sa'”.

    Also, being the update error only mentions “msdb” as the SID difference causing the error, does that mean it is the only one with the error and the user DBS don’t have to be ‘0x01’ or is that the first one it hits and so it just stops there? In another words, is it fine that user DBs are not ‘0x01’?

    Thanks for your help. I really appreciate it.

  3. Brent says:

    Oops, I used “less than” and greater thans” and it took them out. The code is:
    “USE msdb“, “GO”,
    “Exec sp_changedbowner ‘sa’”
    then
    “ALTER AUTHORIZATION ON DATABASE msdb TO ‘sa’”.

  4. Brent says:

    Also, when I plan to do it for real, I will be copying the ldf and mdf files from the production server to the temp server (instead of Backup/Restore) – Win 2003/SQL2000 -> upgrade to SQL2008 -> then copy ldfs/mdfs -> to Win2008/SQL2008. So will that avoid this issue completely since I will be doing file copies?

    • axel8s says:

      You can do a backup and restore or an attach if you like. You only need to change the dbo on MSDB because this is the One coming from the original server so there will be a mismatch between the user and login Sid.

  5. Brent says:

    Ok, Thanks. (Actually I restored ALL DBs from original server – system and user. And will copy ALL ldf and mdf files in future. Hope that doesn’t make a difference.)

    • axel8s says:

      If you also replace the master you’ll have the originating Sid’s this should go well

      • Brent says:

        Sorry to keep hassling you. So that I understand, are you saying it should go well because I am COPYING all the files (ldf & mdf) instead of doing a Backup/Restore? Because if I understand this issue correctly, it is all caused by the Backup/Restore. Thanks.

  6. axel8s says:

    It doesn’t matter if you restore or copy the files. The login security info is in the master db. So if you copy/restore also the master database there should be no problem with the accounts in the other databases.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: