We’ve developed some resources to help you work effectively from home during COVID-19 Click here to learn more

Database maintenance

In this article

    Earlier versions of DBSetup may differ a bit, basic functionality is still the same. Please take extra note if you are also using Customer Service*

    Rebuild (copy) a database, version 6.0 - 7.5; no version change 

    Make a copy of an existing 6.0 - 7.5 database on another database server. The source database will not be changed.

    The new database can be on any supported server type for that kind of database (you should not rebuild a Travel database to Oracle, for instance, since that is not a supported server for local Travel databases). You can force conversion to Unicode by setting the checkbox, otherwise the new database will be the same as the old one.

    Upgrade (not copy) from version 7.0 to 7.1 or 7.2 to 7.5

    Upgrade a database from version 7.0, 7.1 or 7.2 by updating the database schema in-place. You MUST HAVE a backup, there is no undo and no recovery in case of crash halfway.

    Update 7.5 beta

    Update from beta/RC version of 7.5 to current. You MUST HAVE a backup, there is no undo and no recovery in case of crash halfway.

    Kill a database, version 6.0 - 7.5

    Kill a database completely: Removes database users (prior to 7.0), data tables (including partner-defined tables that have been registered in the dictionary), and the dictionary itself. If you have no backup, the database is gone for good.

    Create a new, blank 7.5 database

    Create a new 7.5 database; you will need an Internet connection and valid license information. Any existing database on the given data source will be deleted. You can choose whether the new database should store text as unicode (recommended!) or ANSI.

    If you are running an international enterprise, unicode is necessary to be able to mix languages between Western Europe, Eastern Europe and Asia.

    Some preferences that ServerSetup writes during installation will not be written, for example E-mail client and client autoupdate.

    Load or re-load initial data into a 7.5 database

    Import or update the basic (initial) data in an existing database. You can choose which section to import from the dropdown list, which shows sections defined in your SoTables.ini file.

    The system-reserved sections General, Init, Upgrade and Register are not shown, other sections are shown.

    If you leave the dropdown list blank it will just re-calculate the sequence next_id.

    Recalculate the Sequence table

    Recalculate the Sequence table, by scanning all data tables.

    If there is a lot of traffic on the database, the results MAY be wrong; so choose a quiet moment.

    If importing an SDA file that was exported from a heavily used database, running this operation is a good idea as Sequence is exported first, and may not be fully up to date in the SDA file.

    Export a database to file

    Export the entire database (6.0 - 7.5) to a flat file. Both SuperOffice and registered partner data, as well as Customer Service extra fields/tables are exported.

    The file is written in compressed format and won't compress further. You should use any SuperOffice user with login rights.

    Import a database from file

    Import a database (6.0 - 7.5) from a flat file created by the Export function. Any existing database on the target WILL BE DELETED!

    The resulting database will be in the original version, no upgrade is performed; however, platform conversion (for instance, Oracle 11g -> MSSQL 2008) is done as needed.

    You should use a database user with rights to perform DROP TABLE, CREATE TABLE and user administration commands.

    Inspect a database or export a file

    Open an import file OR database and retrieve some information from it. This command will not make any changes to any database, just use it to peek inside.

    If an import file name is specified, then that file will be inspected; otherwise, the Source database specification will be used and a database will be inspected.

    Run a SQL script file

    Load a text file and run sql statements in it. Statements are separated/terminated by a semicolon character.

    WARNING: No checks are made - if you mess up the database 'by accident', it's your problem...

    Create or re-create freetext index

    Create or update the freetext index on an existing 7.5 database. You need to have DROP TABLE and CREATE TABLE rights on the database to do this. The same function is available in the Admin client.

    Rebuild SAINT counters and statuses

    Perform a total rebuild of the SAINT counters, and subsequently the SAINT status flags. Depending on the number of contacts, project, appointments, sales and Intent list entries, this can be a fairly lenghty process.

    Recalculate Next Due Date for all Sales

    Scan all appointments bound to sales, and update the Next Due Date on all sales. Depending on the number of sales and appointments, this can be a fairly lengthy process.

    The user name must be a valid employee or system user, not just a database user

    Recalculate Next Milestone for all Projects

    Scan all appointments bound to project, and update the Next Milestone on all projects. Depending on the number of sales and appointments, this can be a fairly lengthy process.

    The user name must be a valid employee or system user, not just a database user

    Delete and recreate database users

    Synchronize database users with application users, by deleting and recreating all users currently in the Associate table.

    Please note that this function is irrelevant and does nothing for version 7.x databases.

    Database consistency checks (choose tests after pressing [Start])

    Here you may run and fix problems found in the database:

    • Clean phone numbers (From 7.1 SR1, will remove wrong values stored in the database, like InterAreaPrefix (0), DialInCode (like +47) and so on)
    • Module License Hashes
    • Update Phone's SerachPhone field
    • ActiveInterests count on Contact and Person.
    • ActiveLinks on Appointment, Sale and Document.
    • VisibleFor on Appointment, Sale, Selection
    • ContactId vs PersonId on Appointment (when the Contact_id on the appointment refers to a different contact than the person you will get the "jumping activity syndrome" where clicking the activity archive automatically refers you to another company card). Running this option will fix this problem.
    • Many/many releation on Assoc_id - UserGroup and Associate_id - role_id
    • Update phones SearchPhone field
    • Person: Update persons rank pr contact_id
    • Projectmember: rank (per person_id)
    • StatusDef: Rank
    • DiaryView: Rank (per assoc_id)
    • DiaryViewRow: Rank (per assoc_id)
    • Rank: MDO List Tables
    • Sale: ContactId vs PersonId
    • SelectionMember: ContactId vs PersonId

    If you have imported data to the SuperOffice database via SOAdmin we recommend you run these to verify that the rank is set correctly. Rank will not be set during import due to performance issues.

    Start DBSetup with parameters

    From CRM 5.6 dbsetup.exe can be run with command line parameters to specify an action. The program will perform the action automatically and exit. User interaction may occur if something goes wrong (this may be corrected in a future version ).

    The command line consists of a command followed by parameters. Valid commands are: REBUILD, UPGRADE60, CREATE7, IMPORTINITIAL, FREETEXTINDEX, KILLDATABASE, MAINTENANCE, IMPORT, EXPORT, REBUILDSAINT, SYNCUSERS or RUNSQL. They correspond to the radio buttons in the GUI.

    Parameters also correspond to the GUI. For the REBUILD command, there must be 8 parameters: Source DSN, user, password and prefix (remember ODBC: for ODBC databases); followed by target DSN, user, password and prefix. Parameters are separated by spaces and all are required. Note that this means that blank passwords are not supported.

    The UPGRADE60, CREATE7, KILLDATABASE, REBUILDSAINT, SYNCUSERS and FREETEXTINDEX commands take 4 parameters: DSN, user, password and prefix.

    IMPORTINITIAL takes 5 parameters: DSN, user, password, prefix, name of section in SOTABLES.INI to import tables for.

    IMPORT and EXPORT takes 5 parameters: DSN, user, password, prefix, name and path to import/export file.

     

    An error message is given if the supplied command line has the wrong command or number of parameters. Normal errors are given for other problems, such as wrong password etc. If everything is OK, normal progress dialogs are shown, but the program terminates automatically after execution.

    Note! If you are using DBSetup to rebuild or move a database, you MUST run <cs installfolder>\bin\Upgrade.exe after to set the CS fields values correct.
    NULL Values (may be used on some fields made in CS): DBsetup do not have the data structures/flags required to carry this additional information. So, when doing a rebuild/import of a database that contains NULL values (some CS tables do), those will be changed into the nearest-to-empty value for that type - empty string, number 0 etc.