How to manually upgrade a Pharos Uniprint Database

Blog Post created by toleary on Aug 2, 2013

How to manually upgrade the Pharos database

On the Pharos Suite install CD, in the directory "..\database\db" there is a collection of SQL scripts. These scripts can be used to manually upgrade the Pharos database.

The names of the scripts show the version build. For instance, "dbug1950.sql" is for version build 1950.

To upgrade the Pharos db, start with the script with the next highest version number of the database build. For example, to upgrade the database from version 1211 to 1632, you will need to apply the following scripts against the database in the following order.

dbughead.sql (puts the database into a state where the upgrade scripts can be applied)
dbug1466.sql (upgrades the tables from 1211 to 1466)
dbug1573.sql (upgrades the tables from 1466 to 1573)
dbug1632.sql (upgrades the tables from 1573 to 1632)
dbugtail.sql (applies the 1632 triggers and stored procedures)

Prior to attempting a manual upgrade of the Pharos database make sure there is a current tested and verified backup.

Copy the contents of each of the scripts into the SAME SQL Server Query Analyzer window, DO NOT OPEN A NEW Query window for each script they must be run one after the other in the same window. After running each script, check the output for any error messages. While none should occur, better to be cautious.

1. To verify the current build. Open the Registry Editor and browse to:

HKEY_LOCAL_MACHINE\Software\Pharos\Installed Components

2. Note down the version (build) number value for the Pharos Database.
3. Start SQL Server Query Analyzer, and log on as the Pharos Database user.
4. Ensure that the Pharos Database is selected as the current database.
5. Open and run the appropriate upgrade files for your version of Pharos.

Upgrading from Pharos 5 or later

a. Open and run the script dbughead.sql.
b. Open and run all dbugXXXX.sql scripts, where XXXX is greater than the current version number (from the registry).
c. Open and run dbugtail.sql.

Upgrading from earlier than Pharos 5

a. Open and run the script XXX-470.sql, where XXX is the version of Pharos installed (the main product version number, not the build number from the registry). Skip this step if you are upgrading from Pharos 4.70.
b. Open and run dbughead.sql, followed by all dbugXXXX.sql scripts in order.
c. Open and run dbugtail.sql.

6. Update the build number for the Pharos Database in the Installed Components registry key.

NOTE: The dbinst.sql script in the directory can create a fresh (current to the CD version) database. You can use this to create a reference pharos database to verify the upgrade. To verify the upgrade: connect to the reference database and load the script Validating Upgrader.sql. Replace 'upg_db' with the name of the database that has just been upgraded and replace 'ref_db' with the name of the reference database. Run the script and then check the results set for any differences between the two databases. Any differences reported by this script will indicate that the upgrade has failed. (Possible exception is name of uniqueness constraints on categories and permission_types tables)

Note: When upgrading a Pharos Uniprint Database for to 8.0 there is no automatic mechanism to upgrade a remotely hosted Pharos Database on a remote SQL Server.