Framework:Database on SQL Server

Tables

You need to distinguish between system tables and data tables.

Data tables are created via the object management. They have a COPSYS_ID as their name.

System tables are tables pre-defined by the CoPlanner and start with “tb_”. These are:

• tb_AggMskConfig
• tb_Formeln
• tb_FrmlFelder
• tb_FrmlParameters
• tb_FrmlVerb
• tb_ImpFeldzuordnung
• tb_ImpObject
• tb_ImpTabzuordnung
• tb_InpMskCnfg
• tb_MakroAction
• tb_MakroActionDetails
• tb_MakroActionParam
• tb_MakroActionParamDetails
• tb_ObDetails
• tb_ObjektRechte
• tb_ObTypes
• tb_OlapStructure
• tb_PlanBenutzer
• tb_Planverwaltung
• tb_TabDetails
• tb_tabdim
• tb_VersionInfo
• tb_zeit

Views

The only CoPlanner view is the “time” view. This results from:

Create View [time] As SELECT dbo.[tb_time].* FROM dbo.[tb_time]

This is used in the OLAP for the time dimension.

It is possible to create the CoPlanner data tables as a view which then is given the corresponding name as in the CoPlanner object management.

For this purpose, there is a specific menu item in the CoPlanner under Administration – Create views. Existing views are not deleted thereby, but if there exist already views with a name that is to be created such views are overwritten.

If required, you can delete all views (except for the “time” view) and have all CoPlanner data tables newly created as views.

Stored Procedures

The CoPlanner does not use any stored procedures.

Backup / Restore

From within the developer menu you can backup and restore the entire database as well as all definitions of the server directories. The following data is saved:

• The entire SQL database which is set in the connection string in SvrConfig.xml.
• The “Agg Masks”, “Reports” “Report” and “Transfer” server directories as well as their sub-directories.

Please note:

• Any existing content in the saved directories will be overwritten during the restoring process.
• Configuration data (SvrConfig.xml, CopServer.exe.config, ClientConfig.xml and coplanner.exe.config) are not saved.
• The database contained in the backup will be restored with the name which is defined in the SvrConfig.xml in the connection string – irrespective of the name of the originally saved database.
• OLAP data is not saved.
• In case of doubt, carry out a backup before the restoring process.

In order to backup a database, proceed as follows:

Backup database

Entry  Click the button and choose a directory on your local client computer.

The following dialog box will appear:

Error creating thumbnail: File missing

Framework, Backup database

Entry  Click the “Start” button. Following this, a backup of the database is created in the defined directory on the CoPlanner server and transferred to your client computer.

Hint  Please ensure that in the svrConfig.xml, which is located in the server directory, the <Backuppath> line is correctly defined. You must enter a path for which the SQL server has writing rights. After some time (please be patient – the file is compressed on the server and then transferred to the client) the backup is saved in the directory indicated. Please also note that files which exceed the “BackupRestoreLimitinMB” (default volume = 50 MB) as set in the “copservice.exe.config” server configuration file, are not transferred to the folder indicated but are left in the folder given under “Backuppath” in the “SvrConfig.xml” configuration file which is used for temporary storage.

Restore database

Entry  Please select the “Developer mode/backup” command from the CoPlanner menu.

The following dialog box will appear:

Error creating thumbnail: File missing

Framework, Restore database

Hint  Verify if the database which you wish to overwrite by means of the restoring process has been defined in the connection string in SvrConfig.xml. Again, you need to ensure that the <Backuppath> is configured correctly. Furthermore, it is important to make sure that the pooling=false part exists in the connection string. Besides, no other client, query analyzer, enterprise manager or the like must be connected on the SQL server. If this is not the case, please change the parameters accordingly and restart the server and the client.

Entry  Click the button and select a backup file and then click the “start” button.

After some time there will be a message saying that the backup has been integrated. The server will then be restarted and you can continue to work with the new data.

Hint  Close the existing masks before any restoring procedure since the underlying data no longer needs to be available.