Question

How to manually create or upgrade BarTender System Database using SQL scripts?

Answer

There are some instances where you may want to manually create or upgrade the BarTender System Database using SQL scripts, such as when a 3rd party vendor or administrator manages your SQL Server database, and you don't want to install BarTender to create or upgrade the system database.

Creating the System Database

You can manually create a BarTender System Database (without the system database wizard) by doing the following. Two files from the main BarTender installation location (C:\Program Files\Seagull\BarTender Suite\Database for 64 bit BarTender or C:\Program Files (x86)\Seagull\BarTender Suite\Database for 32 bit BarTender) are required:

1. CreateDatabase.bat
2. CreateDb.sql

These files must be copied to a directory on the computer/host running SQL Server. CreateDatabase.bat is a batch file that is run to create the system database and can be executed from a Command or Windows Powershell window.

Here are the commands and parameters for creating the system database:

• CreateDatabase.bat <SqlInstance> [Database Name] [schema only flag] [path]
  
  • Parameters:
    • <SqlInstance> (non-optional) is the instance name of the desired SQL Server. The computer name may also be required as part of the instance name. For example, IEPC2\SqlExpress represents the SQL Server instance named 'SqlExpress' on computer IEPC2. If you are only using the default instance, you may just specify the computer/server name.
    • [Database Name] (optional) is the name of the database. 'BarTender' will be used if this parameter is omitted.
    • [schema only flag] (optional) if set to "true" instructs the script to create only the database schema on an existing database file.
    • [path] (optional) is the path where you want the physical database file located.

• CreateDatabase /? - Displays this Help

• CreateDatabase /L - Displays a list of available SQL Servers on the network

• CreateDatabase /E - Displays a list of example command lines.

Example:

• CreateDatabase.bat <InstanceName> (Where InstanceName is the name of the SQL Server and instance, e.g.: “IEPC2\SqlServer”.) This will create a system database with the default database named "BarTender" or "SystemDB" depending on BarTender version.

Upgrading the System Database

For upgrading the system database, the SQL scripts are also found in the BarTender install location (C:\Program Files\Seagull\BarTender Suite\Database for 64 bit BarTender or C:\Program Files (x86)\Seagull\BarTender Suite\Database for 32 bit BarTender). They should be files named UpgradeDB.sql through UpgradeDB_1.x_to_1.y.sql. Again, the scripts need to be copied to a directory on the computer/host running SQL Server.

To upgrade:

1. Use Admin Console or the system database UI to find the current version of the System Database Schema - it should say "1.x".
   
2. Open and run all the UpgradeDB_1.x_to_1.y.sql scripts in order in SQL Server Management Studio, starting with the one prefixed with their schema version. For example, if they have schema 1.30, run "UpgradeDB_1.30_to_1.40.sql" first, followed by all versions greater. Note that each UpgradeDB script uses the parameter [$(dbname)] - this will have to be replaced in each script with the name of the System Database.