Tool of the Month

An Introduction to the Sage Accpac ERP Database Utilities

As you may or may not be aware, Sage Accpac ERP includes three important database utilities that you need to be aware of. These 3 utilities are
called:

  • Database Dump or DBDUMP
  • Database Load or DBLOAD
  • Database Copy or DBCOPY

The DBDUMP utility is used to provide you with a convenient format for storing database backups (because the extracted data format is smaller
than a full database).

The DBLOAD utility is used to “load” the “dumped” data into an Accpac database. When used in conjunction with the DBDUMP,
the DBLOAD can be used to copy a database from one storage location to another or allow you to create a new Accpac company with the identical copy
of data from the “Live” system for training or testing purposes. You can use these tools to create a development environment for testing
reports, customizations, or new business processes.

In addition to the portability function of the DBDUMP and DBLOAD process, extracting (dumping) the data and then reloading the data into a database
frees up the empty space left after removing transaction details or other history, resulting in a smaller database.

In addition to weekly or more frequent data integrity checks, databases should be dumped and loaded at least once of month allowing for the data
to be “re-indexed” and the data dictionary to be refreshed. One of the benefits of doing a DBLOAD is that the utility recreates all
of the indexes in the data tables. Though discussed later in this article, it is necessary to make sure a very recent working backup of the data
exists prior to starting a DBLOAD. Because the first step in the DBLOAD process is to remove all of the existing data from the database, it is important
for you to have a backup that can be restored easily if something were to happen during the load process.

The DBCOPY utility copies data directly between two databases without creating an interim, extracted dataset. This utility lets you copy data between
Pervasive and SQL Server databases (if you have both versions of the System Manager). If the Test or Training company is going to reside on the
same server as your “Live” data, you can “Copy” the data from the “Live” database ID to the “Test”
database ID making sure you select the right Source ID and the right Target ID. If you so desire, you can also change a database’s ID by copying
it to a new database with the desired database ID and then deleting the original database.

***Note: Please make sure there are NO users currently accessing Accpac when using these utilities ***

You can start Database Dump and the Database Load from either the Accpac Web desktop, the client/server Accpac desktop or from the Windows/Start
menu. Database Copy can only be accessed from the Windows/Start menu.

Using Database Dump

Database Dump extracts data from an Accpac database. The data can then be stored or loaded into an Accpac database (in Pervasive or Microsoft SQL
Server format — depending on your version of System Manager).

You can start Database Dump from the Accpac Web desktop, or from the Windows/Start menu.

Running DBDUMP from the Windows/Start Menu

1. From the Windows Start Menu, choose Programs, then choose Accpac, then Tools, and then click Database Dump.

2. If asked for a password, type the ADMIN password for Accpac, and then click Close to exit from the message.

3. Select the database whose data you want to extract. (Database Dump finds the database list from the SharedData entry in the Windows registry.)

4. Choose the Set Directory button, and then enter the folder where you want to put the extracted data files (called datasets). Database Dump
creates a control file with filename extension “.DCT” in the folder you specify, and puts the actual extracted data in a subfolder of
the same name as the DCT. The default location of the folder is \\ACCPAC\Runtime.

5. The name of the control file is the database ID with the “.DCT” extension.

Note: When storing extracted data you must retain both the control file and its associated subfolder.

6. Choose the Dump button to proceed.

Running DBDUMP From the Accpac Web Desktop

1. Click the Database Dump icon

2. Select a database from the list in the Database field.

3. Type a path in the Dataset Directory, or click Browse to choose the path.

4. Type a description in the Description field.

5. Click the Dump button, and then click Close.

Using Database Load

Database Load takes a dataset (extracted data) and loads it into an Accpac database. Any data already in the database will be deleted.

Note: You must create the target database and register it for use with Accpac’s Database Setup program before using Database Load.

You can start Database Load from the Accpac Web desktop, or from the Windows/Start menu.

***IMPORTANT: Make sure a recent backup of the data exists prior to starting a DBLOAD. Depending on your database, your Company data and System
data will be located in the \Program Files\ACCPAC\Data\ companydatabaseID and \Program Files\ACCPAC\Data\systemdatabaseID folders or in the default
location of your particular database software (i.e. MS-SQL, Oracle, or DB2). If you have any questions or need assistance regarding backing up your
Accpac data and shared data folders, please contact your consultant to assist you.***

Running Database Load from the Windows/Start Menu

1. From the Windows Start Menu, choose Programs, then choose Accpac, then Tools, and then click Database Load.

2. If you are asked for a password, type the ADMIN password for Accpac, and then click OK to exit the message.

3. Choose the Set Directory button, and then enter the folder containing the .DCT file for the extracted data you want to load. (The .DCT file
is a control file. The actual extracted data is in a subfolder of the same name as the DCT file.)

4. Select the first dataset that you want to load, and then choose the Next button.

The load utility will allow you to select, in turn, each of the datasets you want to load (for example, the company and the system datasets).

Remember that each Accpac company database is paired to a system database. (You can load the databases in any order.)

5. Select the empty database that you created for the dataset you selected in the first window.

Then choose the Next button. Make sure you select the correct database.

The Load Database utility will delete all existing tables in the database you select before loading the extracted data. It will replace an existing
database.

6. Check the data and destination list in the final window.

a. To specify another dataset for loading, choose the Next button. Then follow steps 4 and 5 to specify the dataset.

b. To load the datasets that are in the list, choose the Finish button.

c. Database Load starts to load the dataset into the target database.

d. If the target database already contains data, Database Load displays a message asking if it should overwrite the existing data. Check that
you are loading the data into the correct location, then choose OK or Cancel.

7. When the loading is finished, click OK to finish.

Running DBLOAD from the Accpac Web desktop

***Note: Please make sure there are NO other users currently accessing Accpac when using these utilities ***

***Warning: You cannot “load” into a database that you are logged into***

1. Click the Database Load icon.

2. Select a database from the list in the Database field.

3. Type a path in the Dataset Directory, or click Browse to choose the path.

4. Choose a Dataset from the drop-down list in the Dataset field.

5. Click the Load button, and then click Close.

Using Database Copy

Database Copy copies the contents of one database to another database without creating an interim extracted dataset. This allows you to copy data
between Pervasive.SQL and Microsoft SQL Server databases.

Note:

  • You must create the target database and register it for use with Accpac’s Database Setup program before using Database Copy.
  • All data already in the target database will be deleted.

To run Database Copy:

***Note: Please make sure there are NO users currently accessing Accpac when using these utilities ***

1. From the Windows Start Menu, choose ACCPAC – Tools – Database Copy.

2. Type the ADMIN password for Accpac, and then click OK.

3. Select the database that you want to copy, and then click Next. (DBCOPY finds the database list from the SharedData entry in the Windows registry.)

4. Select the target database, and then click Next.

Database Copy lists all databases of the same type (that is, system or company) except for the source database.

5. On the final screen (Copy Database), check that the source and target databases have been specified correctly, and then click Finish. Click
Yes on the Confirmation window

If you have any questions or need additional assistance, please contact your AXIS consultant or email us at info@axisintegratedsoltions.com for more information.

Bookmark and Share
This entry was posted in Newsletter, November 2006 and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>