On Database Dump and Load

Sage ERP Accpac has always shipped with a number of very useful utilities for transferring data between Accpac installations. This is useful for backing up data, or sending it to say 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.

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.

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.

Why Two Sets?

You might wonder why there are two sets of utilities, those run from the Start Menu and those run from Administrative Services. If you’ve run these, you will have noticed that these are similar programs, but they aren’t exactly the same. The set of program run from the Start Menu are the original set, these have been included with Accpac since the beginning. However with version 5.0A we introduced our current Web Deployed mode. In this mode screens can run inside the IE browser as ActiveX controls. The original utilities weren’t ActiveX controls and people might want to run these from the Browser. The original utilities also talked directly to the Accpac Database API rather than Views (Accpac Business Logic Objects), and once Web Deployed a program can only talk to the Accpac COM API and not directly to the Database API. So we moved all the logic for these to a new View: AS0025. This View does all the real work of dumping and loading. You can even call it from a macro if you want. Then we created two simple Accpac VB UIs that drive this View. These can be run Web Deployed. We didn’t move Database Copy, because we didn’t feel there was much need for it, and that people really just used Database Dump and Load. For Sage ERP Accpac 6.1 when we move Administrative Services to be a true HTML/Javascript Web Based UI, we will create new UIs for these, but continue to use AS0025 (now wrapped as an SData Web Service).

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 on 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. From the DCT files, we create an empty database containing the correct database structure to be used by our programs. The *.REC files then contain the data. Since all the structure information is stored in the DCT file, the *.REC file contains only data. Basically we take the data from each table and store it in our internal format (the same one used by the Views). There is one line for each record in the database. We don’t store the indexes, these are re-created by the database server as the records are loaded (due to this, some people use Database Dump/Load as an index repair utility; this sometimes works, except when index problems prevent the table being dumped correctly; generally it’s better to use the database vendor’s repair utility).

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 a utility like 7-zip (http://www.7-zip.org/) will do a much better job of compressing 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 Third Party Tables

Some third party ISV programs create additional tables in the Accpac company databases. However if they don’t use the Accpac Database API to create these tables, then we don’t know about them and won’t dump or load them. Most third party programs do use our API these days, but there are still a few out there that don’t. If you have a third party add-in for Accpac that doesn’t dump and load properly then perhaps suggest to the vendor that they switch to using our Database API, so their files can be as easily transferred.

This is part of the reason we create the DCT file. The structure of an Accpac database isn’t the same for all installations or even all companies. The structure of each database depends on which applications have been activated for that database, including both Accpac applications and third party applications and add-ons.

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. Some people will do a selection of backup techniques so if there is a problem with one, and then they have another, a backup of the backup.

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.

Update 2010/8/8: One thing to watch out for when moving data between database server is sort order. Generally the data will be re-sorted in the new sort order (if they are different) when you Database Load. However if the original sort order is binary based and the new sort order is case-insensitive then you run the risk of any keys that allow upper and lower case being rejected as duplicate records and the Database Load failing. The main case of this is the Unit Of Measure table where people often have EA, Ea and ea. To avoid problems you have to delete the records whose keys only differ by case before dumping.