On Database Dump and Load

Sage ERP Accpac has always shipped with a number of very useful utilities for maintaining its data in good condition as well as transferring data between Accpac installations. This is useful for backing up data, or sending it to your business partner to do some work for you. These utilities are the reason that it is so easy to move an Accpac database from one database server to another, say from Pervasive.SQL to SQL Server or from SQL Server to Oracle.

Accpac database dump and upload

Database Dump and Load Utilities

Database Dump is an Accpac utility program that will export an Accpac company or system database to a set of database independent files in a folder you specify. You can run this either from the Start Menu – Sage Accpac – Tools – Database Dump or from the Database Dump icon in Administrative services. You need to be the ADMIN user to run this. These files can then be backed up, or sent to someone else to use.

Database Load is the Accpac utility program to load the files dumped by Database Dump. It doesn’t matter whether the source of the files was SQL Server, Pervasive.SQL or Oracle. Database Load can load them into any Accpac company (or system) database. You can run this either from the Start Menu – Sage Accpac – Tools – Database Load or from the Database Load icon in Administrative Services. You need to be the ADMIN user to run this. To run Database Load, all users must be out of the system. Database Load will first erase any tables in the database you have selected and then load the files into the newly emptied database. Generally you would load into a newly created database. The Database ID doesn’t have to be the same as it was when you dumped. If the Database ID is differences then Database Load will fix up and references to this in the database.

In addition:
Database Copy is a shortcut that copies a database from on company to another on the same system. It works just like doing a Database Dump and then a Database Load. The only difference is that it doesn’t bother creating the dumped files as it processes. This utility is only available from the Start Menu – Sage Accpac – Tools – Database Copy icon; it is not available from Administrative Services. Again you must be the ADMIN user to run this.

What Are the Dumped Files?
Database Dump creates a set of files consisting of an orgid.DCT file and then a subdirectory which is named after the orgid. This subdirectory then contains a large set of *.REC files each named after a database table that has been dumped. The orgid.DCT file contains the data dictionary for the database, it lists all the tables in the database along with all the fields in each table and their name, type and some other info. It also lists all the indexes in all the tables.

Since we don’t store any extra information, the set of REC files tends to be more compact than the original database. We use a very simple compression scheme to save some space; but any zip utility can further compress the files and provide a single archive file to email or burn to DVD.

If there is no data in the table being dumped, then we don’t create a .REC file at all. So if you see some files missing, don’t worry they are just empty (or only worry if they should have some data in them). If you delete a .REC file, then that file will end up empty when you load; this isn’t recommended as it will probably compromise database integrity.

Issues with Backing Up
Many people use Database Dump and Load as a mechanism to back up their company databases. This is fine, but it requires everyone be out of the system. It does make it easy to say send a backup to tech support or your Business Partner if you have a problem, but the higher end backup programs along with the backup programs built into the database servers have some advantages. For instance the SQL Server backup program is very fast, this is by far the fastest way to load a database. Also the SQL Server backup program can run while people are in the system and it will ensure that the backup is to a consistent place based on the transactions being executed. Either way is fine, just be aware that there are tradeoffs in any solution and what is best for you will perhaps be different than the solution for your neighbor.

Also beware that if Database Load fails, you won’t have a working database. Database Load needs to finish loading all the tables in order to give you a workable company database. If it does fail, fix whatever is wrong (usually out of disk space or a network interruption) and run it again. Database Load writes to the database using fairly small transactions of 50 records or so at a time, since from our performance measurements, this gives the quickest load time.

Summary
Database Dump and Load are extremely useful utilities that let you transfer Accpac companies between different organization IDs and even between different types of database servers. They provide an easy mechanism to send data to your Business Partner or Tech Support. They provide a mechanism to back up your data, if you need one. And they also provide a quick method of re-indexing and compressing your data.

Ken Adams is the VP of Professional Services - Accpac Division - of DSD Business Systems. Prior to joining DSD, Ken was the CEO of Horizon Information Systems, a firm that successfully sold, implemented and supported Sage Accpac ERP since 1988. Effective January 1st, 2009, Horizon Information Systems and DSD Business Systems merged. Ken's experience includes a broad range of successful implementations for various types of businesses including non-profits, distribution, and manufacturing companies. He has successfully implemented Accpac in multi-lingual and multi-currency environments in the U.S., Canada and Mexico.
Kadams
View all posts by Kadams
Kens website
Share
© 2010 DSDtips.com Suffusion WordPress theme by Sayontan Sinha