For information about Globalscape, visit www.globalscape.com.

EFT Server Database Utility

A command line utility is included in the installer that is capable of performing various database-related tasks. This same utility is used by the EFT Server installer to handle upgrades of existing databases. Typically all common database tasks are handled by the EFT Server installer. However, on occasion it may be useful to use the command-line utility to verify the status of a database or perform an upgrade independent of the EFT Server installation process.

The database utility (DBUtility.exe) is included as part of the core EFT Server installation. Once installed it is located in the "DBUtility" sub-directory of the EFT Server program files installation directory. Typically this will be C:\Program Files (x86)\Globalscape\EFT Server Enterprise\DBUtility.

Requirements

• The utility requires the .NET Framework 4 (Full version)

• When operating against an Oracle database, the utility requires the 32-bit version of the Oracle Data Access Components (ODAC)

Capabilities

The database utility is capable of performing the following tasks:

• Check the version of the database to see if it is up-to-date. This may be used to see if an upgrade must be performed on a database before it is ready to be used the EFT Server application

• Generate an SQL script that may be used to manually create a new database schema (tables, views, etc.) within an existing database

• Generate an SQL script that may be used to manually upgrade an existing database schema.

• Analyze an existing database prior to performing an actual upgrade. The analysis will verify prerequisites, display information about the database, and display the actual SQL that will be used to upgrade the database

• Upgrade an existing database schema to the latest version

Logging

The utility is capable of outputting various levels of information ranging from errors to debug/trace level information.

By default the utility will output errors, warnings, and informational messages to the command window. If the "-verbose" command line parameter is specified the utility will also output more fined-grained debug/trace level messages to the command window.

The utility may also optionally output to a log file as specified using the "-logfile" command line option. The output to the log file will include all levels of messages from errors to debug/trace level information.

SQL Scripts

The utility requires the presence of various SQL Scripts located in database-specific sub-directories to perform its actions. These sub-directories contain scripts such as:

• create_* - scripts used for creating new, clean database schemas for use by the EFT Server application

• Purge* - scripts that may be used for purging data from the database

• *ODBC - scripts that may be used to create the necessary tables to use an ODBC data source for user authentication

• upgrade_* - upgrade scripts for upgrading various versions of the database

By default the utility will look for the "SQL Server" and "Oracle" directories under its current working directory. During installation of the Database utility these script directories will be created under the <InstallDir>\DBUtility directory, so typically the scripts will be available to the utility.

If for some reason the utility is unable to locate these sub-directories it will also attempt to consult the registry for the EFT Server "AppData" path and then look for the sub-directories under that location.

Additionally, the user may specify an alternate parent directory using the "-scripts" command line parameter.

Usage

The database utility is a command line utility and may be executed by opening a Windows Command Prompt and navigating to the "DBUtility" sub-directory under the EFT Server installation folder and running the command "DBUtility.exe".

Help

The utility includes built in help documentation. Additionally the utility will provide feedback on incorrect or missing command line parameters.

The built-in help documentation for the utility may be accessed using the command:

DBUtility.exe -help

More detailed help for the various top level actions may be accessed using the command:

DBUtility.exe -help -action <Action ID>

Where <Action ID> is on of:

• CheckVersion - checks the version of the database to see if it is up to date

• CreateScript - generates a SQL script that may be used to manually create a new database schema

• UpgradePreview - used prior to upgrading a database. This action will generate and display useful pre-upgrade information as well as the actual SQL that will be used to upgrade the database

• UpgradeSchema - upgrades the database, if needed

• UpgradeScript - generates a script that may be used to manually upgrade a database

Examples

Example executions for each of the actions supported by the utility may be viewed in the command line help for each action.

DBUtility Command Line Parameters

The following section describes each of the command line parameters for the utility. Depending on the action performed only a subset of the parameters will actually be applicable or required.

For the command line parameters that accept a value the value should be enclosed in double-quotes if the value contains spaces. For example, -logfile="C:\My Logs\MyLogFile.txt"

Parameter Definitions

• -help

• • Description: Display help on the command line. Refer to the "Help" section above for additional information.

• -logfile=<file>

• • Description: When specified the utility will log output of the execution to the specified log file.

  • Default: None

  • Example: -logfile="C:\My Logs\MyLogFile.txt"

• -optionsfile=<file>

• • Description: When specified the utility will load command line parameters from the file. The file should specify parameters in a "parameter=value" pair with one pair specified per line. Parameters specified on the command line override parameters specified in the file.

  • Default: None

  • Example: -optionsfile="C:\My Scripts\MyOptionsFile.txt"

• -scriptfile=<file>

• • Description: For actions that generate output SQL scripts this parameter defines the file to which the script should be written.

  • Default: None

  • Example: -scriptfile="C:\My Scripts\MySQLScript.sql"

• -errorfile=<file>

• • Description: When specified the utility will log terminal errors to the specified file. Mainly used for error handling when the utility is called by the EFT Server installer.

  • Default: None

  • Example: -errorfile="C:\My Scripts\MyErrorFile.txt"

• - resultfile=<file>

• • Description: When specified the utility will output result status codes for the execution to the file. Mainly used for state handling when the utility is called by the EFT Server installer.

  • Default: None

  • Example: =resultfile="C:\My Scripts\MyResultFile.txt"

• -pause

• • Description: When specified the utility will pause at the end of the execution. Useful when executing the utility through a shortcut to keep the console window from closing before the user has a chance to review the results.

  • Default: None

• -verbose

• • Description: When specified the utility will output additional debug level logging.

  • Default: None

• -action=<id>

• • Description: Specifies the overall action to be performed by the utility.

  • Valid values:

  • • CheckVersion - checks the version of the database to see if it is up to date

    • CreateScript - generates a SQL script that may be used to manually create a new database schema

    • UpgradePreview - used prior to upgrading a database. This action will generate and display useful pre-upgrade information as well as the actual SQL that will be used to upgrade the database

    • UpgradeSchema - upgrades the database, if needed

    • UpgradeScript - generates a script that may be used to manually upgrade a database

  • Default: None

  • Example: -action=UpgradeSchema

• -type=<type>

• • Description: The type/brand of database.

  • Valid values:

  • • SQLServer - a SQL Server/SQL Server Express database

    • Oracle - an Oracle database

  • Default: None

  • Example: -type=SQLServer

• -server=<server>

• • Description: The database server host or IP address

  • Default: None

  • Example: -server="Jupiter"

• -port=<port>

• • Description: The listener port for the database on the database server/host.

  • Default: None

  • Example: -port=1433

• -instance=<instance>

• • Description: The database server instance name.

  • Default: None

  • Example: -instance="MSSQLSERVER"

• -database=<database>

• • Description: The name of the database.

  • Default: None

  • Example: -database="GLOBALSCAPE"

• -timeout=<timeout>

• • Description: The timeout, in seconds, to continue trying to connect to the database. A value of 0 causes the utility to wait indefinitely and should be used with caution.

  • Default: 30 seconds

  • Example: -timeout=5

• -auth=<auth>

• • Description: The type of authentication to use when connecting to a SQL Server database.

  • Valid values:

  • • SQLServer - Use SQL Server authentication which requires specification of the username and password.

    • Windows - Use Windows authentication which will use the currently logged in user account.

  • Default: None

  • Example: -auth=SQLServer

• -user=<user>

• • Description: The login name to use when connecting to the database.

  • Default: None

  • Example: -user="eftdbuser"

• -pass=<pass>

• • Description: The password to use when connecting to the database.

  • Default: None

  • Example: -pass=“3qym9NCebHDJ”

• -scripts=<dir>

• • Description: Parent directory containing the SQL Server and Oracle SQL Scripts subdirectories. Refer to the "SQL Scripts" section above for additional information.

  • Default: Refer to the "SQL Scripts" section above for additional information.

  • Example: -scripts="C:\ProgramData\Globalscape\EFT Server Enterprise"

Advanced Parameter Definitions

The following parameters are typically reserved for use by the EFT Server installer and will normally not be useful to end users. However, they are documented here for completeness.

• -conn=<connection string>

• • Description: When specified this string will be used as the full connection string to the database rather than constructing the string based on the distinct parts.

  • Default: None

  • Example: -conn="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mth-oracle)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orastart)));Persist Security Info=true;User Id=eftdbuser;Password=3qym9NCebHDJ"

• -app=<application name>

• • Description: The application name to present to the database for use when identifying connections.

  • Default: None

  • Example: -app="EFT Server Database Utility"

• -installerdir=<directory>

• • Description: The directory containing the EFT Server Installer. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example: -installerdir="C:\Users\Administrator\AppData\Local\Temp\nsdB57C.tmp"

• -installationdir=<directory>

• • Description: The directory where EFT Server is installed or will be installed. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example: -installationdir="C:\Program Files (x86)\Globalscape\EFT Server Enterprise"

• -appdatadir=<dir>

• • Description: The directory that will be used for the EFT Server application data. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example: -appdatadir="C:\ProgramData\Globalscape\EFT Server Enterprise"

• -backupdir=<dir>

• • Description: The directory in which to store backup data. May be used during some upgrades.

  • Default: None

  • Example: -backupdir="C:\ProgramData\Globalscape\EFT Server Enterprise\Backup"