Database administration

The installation directory of the CADMATIC eShare server contains the DatabaseManager.exe command-line tool that you can use to manage eShare databases. The default location of this tool is C:\Program Files\Cadmatic\eShareAdminTools\eShareDBTools\; in this document the location is defined as <eShare>\eShareAdminTools\eShareDBTools\.

Many of the administrative tasks that you can perform with this tool can be done in two ways, whichever you prefer:

• Perform the whole task with DatabaseManager.exe.
• Use DatabaseManager.exe to generate an SQL script file, and then run the script on SQL Server.

For a complete list of commands that the tool supports, run the command DatabaseManager.exe help.

For information on a specific command, run the command DatabaseManager.exe <command> help. For example, DatabaseManager.exe drop help.

• Database administration
• Database creation
• Database upgrade
• Database removal
• Database permissions

Database creation

Database administration | Database administration | Creating a project database | Creating an SQL script that creates a project database | Adding a project database to the main database | Creating an SQL script that adds a project database to the main database

The main database must exist and contain a fully created database schema when the eShare server is started.

Project databases can be created as empty, for example with Microsoft SQL Server Management Studio, and eShare will create the database schema before adding project data to the database. Or, you can create a project database and its schema using DatabaseManager.exe.

Creating the main database

Perform the following to create the main database and its schema.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe create -t Main -c <connection string> -d <database to create>

Example

• A command to create the main database "EShare" on the default instance of SQL Server running on localhost, using your credentials:
  DatabaseManager.exe create -t Main -c "Data Source=localhost; Integrated Security=True" -d EShare

Creating an SQL script that creates the main database

Perform the following to create an SQL script file that you can then run on SQL Server to create the main database and its schema.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe create -t Main -f <sql file to create> -d <database to create>

Example

• A command to create the script file CreateMainDatabase.sql for creating the main database "EShare":
DatabaseManager.exe create -t Main -f "C:\DatabaseScripts\eShare\CreateMainDatabase.sql" -d EShare

Creating a project database

Perform the following to create a project database and its schema.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe create -t Project -c <connection string> -d <database to create>

Example

• A command to create the project database "EShare_PowerPlant" on the default instance of SQL Server running on localhost, using your credentials:
  DatabaseManager.exe create -t Project -c "Data Source=localhost; Integrated Security=True" -d EShare_PowerPlant

Creating an SQL script that creates a project database

Perform the following to create an SQL script file that you can then run on SQL Server to create a project database and its schema.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
   
2. Run this command:
   DatabaseManager.exe create -t Project -f <sql file to create> -d <database to create>

Example

• A command to create the script file CreateProjectPowerPlant.sql for creating the project database "EShare_PowerPlant":
  DatabaseManager.exe create -t Project -f "C:\DatabaseScripts\eShare\CreateProjectPowerPlant.sql" -d EShare_PowerPlant

Adding a project database to the main database

Perform the following to add a specified project database to the main database. The command also has checks for if the project is already in the main database, or if the project database does not exist.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
   
   
2. Run this command:
   DatabaseManager.exe addProjectToMain -p <project database to add> -n <project name> -c <connection string> -d <main database name>

Example

• A command to add the project database "EShare_PowerPlant" to the main database "EShare" on the default instance of SQL Server running on localhost, using your credentials:

  DatabaseManager.exe addProjectToMain -p EShare_PowerPlant -n PowerPlant -c "Data Source=localhost; Integrated Security=True" -d EShare

Creating an SQL script that adds a project database to the main database

Perform the following to create an SQL script file that you can then run on SQL Server to add a specified project database to the main database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
   

2. Run this command:

   DatabaseManager.exe addProjectToMain -p <project database to add> -n <project name> -f <sql file to create> -d <main database name>

Example

• A command to create the script file AddProjectPowerPlantToEShare.sql for adding the project database "EShare_PowerPlant" to the main database "EShare":

  DatabaseManager.exe addProjectToMain -p EShare_PowerPlant -n PowerPlant -f "C:\DatabaseScripts\eShare\AddProjectPowerPlantToEShare.sql" -d EShare

Database upgrade

Upgrading all databases | Creating an SQL script that upgrades all databases | Upgrading the main database | Database administration | Upgrading a project database | Creating an SQL script that upgrades a project database

When eShare server is upgraded to a newer version, there might be a need to update database properties or schemas. These updates are performed automatically if the database login that the installer uses has the required permissions. But, if the installer is unable to perform these updates, you must manually upgrade the databases to be compatible with the new version of CADMATIC eShare. You can upgrade all databases at the same time or one database at a time, as appropriate.

If a required database upgrade is not performed, the following will occur:

• If schemas are not up-to-date, eShare does not start.
• If the properties of the main database are not up-to-date, eShare displays a notification to the system administrator on the front page and on administrative pages.
• If the properties of a project database are not up-to-date, eShare displays a notification to the system administrator and to project administrators on the project administration page.

Upgrading all databases

Perform the following to upgrade the main database and all related project databases.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgradeall -c <connection string> -d <main database>

Example

• A command to upgrade all databases that relate to the main database "EShare" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgradeall -c "Data Source=localhost; Integrated Security=True" -d EShare

Creating an SQL script that upgrades all databases

Perform the following to create an SQL script file that you can then run on SQL Server to upgrade the main database and all related project databases.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgradeall -c <connection string> -f <sql file to create> -d <main database>

   Note: Connection string is needed because DatabaseManager.exe checks the current state of the database to be able to create a proper upgrade script.

Example

• A command to create the script file UpgradeAllDatabases.sql for upgrading all databases that relate to the main database "EShare" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgradeall -c "Data Source=localhost; Integrated Security=True" -f "C:\DatabaseScripts\eShare\UpgradeAllDatabases.sql" -d EShare

Upgrading the main database

Perform the following to upgrade just the main database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgrade -t Main -c <connection string> -d <main database>

Example

• A command to upgrade the main database "EShare" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgrade -t Main -c "Data Source=localhost; Integrated Security=True" -d EShare

Creating an SQL script that upgrades the main database

Perform the following to create an SQL script file that you can then run on SQL Server to upgrade just the main database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgrade -t Main -c <connection string> -f <sql file to create> -d <main database>

   Note: Connection string is needed because DatabaseManager.exe checks the current state of the database to be able to create a proper upgrade script.

Example

• A command to create the script file UpgradeMainDatabase.sql for upgrading the main database "EShare" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgrade -t Main -c "Data Source=localhost; Integrated Security=True" -f "C:\DatabaseScripts\eShare\UpgradeMainDatabase.sql" -d EShare

Upgrading a project database

Perform the following to upgrade a specific project database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgrade -t Project -c <connection string> -d <database to upgrade>

Example

• A command to upgrade the project database "EShare_PowerPlant" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgrade -t Project -c "Data Source=localhost; Integrated Security=True" -d EShare_PowerPlant

Creating an SQL script that upgrades a project database

Perform the following to create an SQL script file that you can then run on SQL Server to upgrade a specific project database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe upgrade -t Project -c <connection string> -f <sql file to create> -d <database to upgrade>

   Note: Connection string is needed because DatabaseManager.exe checks the current state of the database to be able to create a proper upgrade script.

Example

• A command to create the script file UpgradeProjectPowerPlant.sql for upgrading the project database "EShare_PowerPlant" on the default instance of SQL Server running on localhost:
  DatabaseManager.exe upgrade -t Project -c "Data Source=localhost; Integrated Security=True" -f "C:\DatabaseScripts\eShare\UpgradeProjectPowerPlant.sql" -d EShare_PowerPlant

Database removal

Removing a project database | Database administration | Removing a project database from main database | Creating an SQL script that removes a project database from main database

When an eShare project administrator removes a project from CADMATIC eShare, by default eShare tries to remove (drop) also the project database. If eShare does not have sufficient permissions to remove databases, you must remove redundant project databases manually, using Microsoft SQL Server Management Studio or the DatabaseManager.exe tool.

Note: If eShare is not supposed to remove databases, you should prevent it from even trying. You can do this in the Application Settings view of Internet Information Services (IIS) Manager, by setting Cadmatic.DropProjectDatabases to "false".

When a database administrator removes the project database of a project that still exists in eShare, the project reference is removed from the main database and the project is no longer listed in the user interface.

Your site should have an established procedure for archiving projects before they are removed.

Removing a project database

Perform the following to remove a project database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe drop -t Project -c <connection string> -d <database to drop>

Example

• A command to remove the project database "EShare_PowerPlant" from the default instance of SQL Server running on localhost:
  DatabaseManager.exe drop -t Project -c "Data Source=localhost; Integrated Security=True" -d EShare_PowerPlant

Creating an SQL script that removes a project database

Perform the following to create an SQL script file that you can then run on SQL Server to remove a project database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe drop -t Project -f <file to create> -d <database to drop>

Example

• A command to create the script file RemoveProjectPowerPlant.sql for removing the project database "EShare_PowerPlant":
  DatabaseManager.exe drop -t Project -f "C:\DatabaseScripts\eShare\RemoveProjectPowerPlant.sql" -d EShare_PowerPlant

Removing a project database from main database

Perform the following to remove a project database from main database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe removeProjectFromMain -n <project name in eShare> -c <database connection string> -f <output file for the script> -d <main database name>

Example

• A command to remove the project database of "PowerPlant" from the main database on the default instance of SQL Server running on localhost, using your credentials:
  DatabaseManager.exe removeProjectFromMain -n PowerPlant -c "Data Source=localhost; Integrated Security=True" -d EShare

Creating an SQL script that removes a project database from main database

Perform the following to create an SQL script file that you can then run on SQL Server to remove a project database from main database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe removeProjectFromMain -n <project name in eShare> -f <output file for the script> -d <main database name>

Example

• A command to create the script file RemoveProjectPowerPlantFromEShare.sql for removing the project database of "PowerPlant" from main database:
  DatabaseManager.exe removeProjectFromMain -n PowerPlant -f "C:\DatabaseScripts\eShare\RemoveProjectPowerPlantFromEShare.sql" -d EShare

Database permissions

Checking permissions in a database | Granting permissions to a database | Creating an SQL script that grants permissions to a database

Checking permissions in a database

Perform the following to check permissions in a database. This command can be used for debugging, as it lists permissions needed, checks if they have been granted and why these permissions are needed.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe checkPermissions -l -c <database connection string> -d <database name>

Example

• A command to check permissions in database "EShare" on the default instance of SQL Server running on localhost:

  DatabaseManager.exe checkPermissions -l -c "Data Source=localhost; Integrated Security=True" -d EShare

Granting permissions to a database

Warning: Consider generating a script and running it manually instead. Allowing the tool to write permissions directly to the database is dangerous.

Perform the following to grant permissions in a database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe grantPermissions -l <eShare login> -u <user> -c <database connection string> -d <database name> --IUnderstandThisIsDangerous

Note: Option "--IUnderstandThisIsDangerous" must be specified to write permissions directly to database.

Example

• A command to grant permissions to "eshare_user" to database "eShare_tankerdemo" on the default instance of SQL Server running on localhost:

  DatabaseManager.exe grantPermissions -l eshare -d eshare_tankerdemo -u eshare_user -c "server=localhost\SQLEXPRESS; Integrated Security=true; initial catalog=EShare; TrustServerCertificate=True; ; MultipleActiveResultSets=True" --IUnderstandThisIsDangerous

Creating an SQL script that grants permissions to a database

Perform the following to to create an SQL script file that you can then run on SQL Server to grant permissions in a database.

Do the following:

1. In a command-prompt window, go to <eShare>\eShareAdminTools\eShareDBTools\.
2. Run this command:
   DatabaseManager.exe grantPermissions -l <eShare login> -u <user> -f <output file for the script> -d <database name>

Example

• A command to create the script file GrantPermissions.sql for granting permissions to "eshare_user" to "eShare_tankerdemo" database:

  DatabaseManager.exe grantPermissions -l eShare -u eshare_user -f "C:\DatabaseScripts\eShare\GrantPermissions.sql" -d eshare_tankerdemo