Using the UpgradeDB.exe tool to upgrade the Sophos database

In some cases the Sophos database is not fully upgraded by the management server installer. There are various reasons for the Sophos database not having been upgraded, the most common are:

• An error in the upgrade process
• You have chosen to populate the database after the upgrade
• You previously uninstalled a version of the management server and reinstalled (i.e., the previous database is still in the SQL Server instance).

In any case you may have to run the UpgradeDB.exe command to migrate the data from the previous Sophos database to the latest version. This article explains how to use the UpgradeDB.exe program to upgrade (i.e., transfer data from) a previous Sophos database to the current.

Known to apply to the following Sophos product(s) and version(s)
Enterprise Console 4.7.0
Enterprise Console 4.7.1
Sophos Enterprise Manager 4.7.0
Enterprise Console 5.0.0
Sophos Enterprise Manager
Enterprise Console 5.1.0

What to do

1. On the Sophos management server, open a command prompt (Start | Run | Type: cmd.exe | Press return), and then change to the following directory:
   • Enterprise Console v4.x/ 5.x - \Program Files (x86)\Sophos\Enterprise Console\
   • Enterprise Manager v4.x - \Program Files (x86)\Sophos\Enterprise Manager\
   • Small Business v4.x - \Program Files (x86)\Sophos\SCC\
     
     
   NOTE: The utility queries the following registry string to locate what computer the database is on (local or remote) and SQL instance name the database is in.
   HKEY_LOCAL_MACHINE\SOFTWARE\(Wow6432Node)\Sophos\EE\Management Tools | DatabaseConnectionMS (STRING)
   
   
2. Enter the command upgradedb.exe and the required command line parameters:
   
   -sourceVersion=50
   specifies the SOPHOS50 database to upgrade from. NOTE: Capitalization of sourceVersion is important.
   
   -sourceVersion=47
   specifies the SOPHOS47 database to upgrade from. NOTE: Capitalization of sourceVersion is important.
   
   -sourceVersion=45
   specifies the SOPHOS45 database to upgrade from. NOTE: Capitalization of sourceVersion is important.
   
   -sourceVersion=2
   specifies the SOPHOS2 database to upgrade from. This option is required for Small Business v4.x upgrades. NOTE: Capitalization of sourceVersion is important.
   
   -reset
   if the Sophos Management service has been started (even briefly) and therefore the upgrade has proceeded pass the initial stage you can reset the 'new' database with this parameter.  
   
   -debug
   prints to screen useful information that is necessary to troubleshoot an upgrade problem.  It is always best to use this option. NOTE: -trace prints the same information
   
   -udl=filePath\fileName.udl
   if the DatabaseConnectionMS string in the registry is incorrect or needs to be temporarily overridden you can bypass the current registry string by creating a UDL test file and specify the file in command.

Example usage

Technical Information

'UpgradeDB.exe' is run as part of the management server installer and not the database installer.  It reads the database connection string from the registry of the management server, namely HKEY_LOCAL_MACHINE\SOFTWARE\[Wow6432Node]\Sophos\EE\Management Tools\DatabaseConnectionMS', to locate the SQL instance where the Sophos database resides.  

'UpgradeDB.exe' calls the SQL stored procedure 'dbo.FromXto4' in the database, so for example, on an upgrade to Enterprise Console 5.0, this stored procedure would be in the SOPHOS50 database.  

The 'dbo.FromXto4' stored procedure requires two parameters, the source version and a reset flag. Examples are shown below.

The 'dbo.FromXto4' Stored procedure, based on the parameters, then calls the relevant stored procedure that actually moves the data from the previous database to the new database. As an example: 'dbo.From45To50' would be called during an upgrade from Enterprise Console 4.5 to Enterprise Console 5.0. If this completes successfully the 'UpgradeStatus' value in the 'dbo.Upgrade' table of the new database is then set to 2 by 'dbo.FromXto4'.  

The upgrade status value is used to tell the Sophos Management Service that the new database has been upgraded as is ready to be used.  If the value is not 2 it will fail to start until the upgrade status is correct.