Question Dumping and loading entire database - best way?

Jesper Nielsen

New Member
We've run into a problem for several of our customers recently, where the backup counters for the databases have overflowed (we do hourly online backups).
The suggested fix seems to be a dump/reload of the database, and support sent a link to a script that I plan on modifying for our own needs, "How to generate scripts to run binary dump and load for all tables?"

But I also need to dump and load sequences, and who knows if there are other things I need to do that I haven't thought about.

Does anyone have some good advice for how best to proceed with this? I must say I was a bit shocked to learn that Progress doesn't simply have a command to dump an entire database.

Cringer Moderator
Staff member
A large part of what you need depends on what you use. The things you need to consider (not a complete list!) are:
  • Users table
  • SQL views
  • Sequences
  • Domains
  • Permissions
  • Anything loaded into the secondary buffer pool
  • ...
The actual process depends a little on how big your database is, and the hardware you're using, etc. This is partly the reason there isn't a one stop shop for this. There's so many things you can adjust and tweak to speed up the process but it depends on what you have.

Whilst you're at it, it's a good chance to check the database structure is valid, so everything should be in Type II storage areas, nothing other than the schema in the schema area, etc.

Why are you doing hourly backups? After Imaging should take that necessity away in terms of backup strategy - in most cases.

Jesper Nielsen

New Member
We have already moved our tables to Type II storage area.
We're doing hourly backups to minimize data loss in case of hardware failure. I understand After Imaging would ensure data consistency in case of crashes, but it won't help much against a drive failure I would think?

Cringer Moderator
Staff member
It's probably more robust really. You are no doubt copying the backup files off the box once they're created, so why not just copy the AI files off the box. And you can set the AI Archiver to manage the files to switch, say, every 5 minutes, meaning, worst case you only lose 5 minutes data instead of your current situation where you could lose a whole hour.
Obviously you would need a backup as well, but you can easily do that every night.
We use Ant and Jenkins to schedule backups and After Image work. I've OpenSourced the code from a presentation I've done on the topic here: Platform independent, robust, reliable OpenEdge database backups – not as hard as you might think!
You may well want to split out the AI file copies from the backups themselves so you can copy them offsite more frequently, but it was easier to present the whole thing as one tool.


If your data is so valuable that losing 5 minutes is too risky you should implement OE Replication.

Almost everyone probably ought to do that anyway. The modern world is far more data dependent than businesses tend to realize.

Rob Fitzpatrick Sponsor
This thread is a bit of a hodge-podge. In the interest of future readers, I suggest opening multiple threads for different discussion topics (e.g. AI).

Briefly off-topic:
  • Always use after-imaging in production. No exceptions.
  • Don't do hourly backups. It's a waste of I/O and won't protect you better than properly-configured AI.
Now on to dump and load.

Dump and load and index rebuild is a complex subject. Lots has been written and there have been a great many conference sessions presented and even books written about it. To be frank, you aren't ready yet to plan a dump and load. I'm not saying that to insult you, but rather to set a realistic expectation. You can get there, and we'll help you (if you're prepared to help yourself), but you're not ready yet. This exercise should be done by an experienced, knowledgeable DBA and it requires planning, practice, verification, and documentation.

There are several ways to dump and load your data and no one way is "right" for all cases. It depends on your needs (e.g. large DB vs. small; some tables vs. all; dumping just data vs. data and meta-data; small vs. large downtime window) and your capabilities (e.g. fast vs. slow hardware; amount of RAM; # of physical cores; ability to borrow hardware or bump up hardware specs during the D&L).

I suggest you start with the documentation for your version. There is a section about dumping and loading data. Once you read that, feel free to ask questions here and post your tentative plans. But we would need to know a lot about the particulars of your platforms, databases, OE versions, etc. The OE version(s) you are working with is particularly important, as best practices can change dramatically just based on version. Right now we know nothing.

Jesper Nielsen

New Member
Thanks - the short-term goal is to do a dump and load of the entire database as it is, in lieu of being able to simply reset the backup counter. It would definitely be preferable to simply be able to change these 2 bytes instead of going through this... This is important in order to ensure that backups are working.

There are probably better ways to ensure data consistency, but that would be a possible step 2, after securing day-to-day production. Our long-term goal is migrating to another solution.

Our current version is 11.6.4, running on Windows Server 2016. Some customers have other Windows versions.
The ABL software is old ADM1 code with WinKit and Infragistics on top - new windows are made as Infragistics forms.

We did test a procedure yesterday that seems to work - there are some other small steps we take like copying files and editing paths in ST files but in short:

Prostrct list to ensure current .st file.
Dump all definitions, sequence current values and all data (Data Administrator)
Create new database with .st file, procopy empty DB, convert to UTF-8 (Command-line)
Load definitions, sequence values and data (Data Administrator)
Rebuild indices

Granting SQL access has been a bit of a hurdle - we've solved it but we're not happy because we're not quite back to the situation we had before.

On our old databases the _Users table is empty - but we're able to grant SQL access with the username "Administrator" - without actually creating such a user.
On the new database we have only succeeded in doing so by creating a "sysprogress" user, performing the grants and subsequently deleting the "sysprogress" user. This means if we create new tables that have to be accessed by SQL we need to recreate and delete the user again.

Is the "Administrator" "pseudo-user" an old legacy feature from older versions of Progress, that has somehow survived in the old database files?

Rob Fitzpatrick Sponsor
In an OpenEdge database, there are separate security models for ABL-client users, who can only see data in the PUB schema, and SQL-client users, who can see PUB and other schemas, e.g. sysprogress. Read this article for an overview:

Basic Guide to Defining Progress SQL-92 Database Permissions & Security

A user with the DBA role has all permissions on all tables, including inheriting all permissions on all newly-added tables. These DBA permissions are stored in sysprogress.sysdbauth. To see them:
sqlexp -db dbname -H hostname -S SQLbrokerport -user username -pass password
SQLExplorer>select * from sysprogress.sysdbauth;

Non-DBA users only have table-level permissions that are explicitly granted to them by a user with permission to do so. As the security model is default-deny, those users inherit no permissions on new tables. When tables are added, a DBA needs to give them the desired permissions. These table-level user permissions are stored in sysprogress.systabauth.

If you use SQL, I recommend that you use the sysprogress user to create a SQL-only DBA user and then delete sysprogress. Then use that DBA user to grant new permissions as needed.

I do the following after schema changes that add tables, to grant SELECT permission to a BI user so it can read data in application tables. Not pretty, but it works.

/*  GrantSelect.p
 *  This program generates a SQL script in the current directory
 *  for the current database.
 *  This script can then be run from SQL Explorer to grant
 *  table select privileges to a user; it prompts for the user name.
 *  SQL explorer must be run with the credentials of an existing user
 *  that has the privileges required to grant table SELECT privileges
 *  to another user.
 *  To run the output of this program:
 *  sqlexp -db <dbname> -S <SQL broker port>
 *     -infile grantselect.sql
 *     -outfile grantselect.out
 *     -user <username> -password <userpassword>
 *  Rob Fitzpatrick
 *  08/21/2013

define variable v-username as character no-undo.
define variable v-dbname   as character no-undo format "x(11)".
define variable v-filename as character no-undo format "x(26)".

define frame a with no-labels no-box.
define frame b with no-labels no-box.

  v-dbname   = ldbname( "dictdb" )
  v-filename = "grantselect_" + v-dbname + ".sql".

display "Name of the DB user to receive SELECT " skip
        "privilege on " + v-dbname + " tables: " format "x(37)"
  with frame a.

update v-username
  format "x(20)"
  with frame a.

output to value( v-filename ).

for each _file no-lock where _tbl-type = "T":
  put unformatted "grant select on pub." + quoter( _file._file-name ) + " to " + v-username + ";" skip.

put unformatted "commit work;" skip.
output close.

Is the "Administrator" "pseudo-user" an old legacy feature from older versions of Progress, that has somehow survived in the old database files?

From the linked article:
"The user who creates the database becomes the default DBA. The default DBA can be used to set additional database Administrator accounts."
If the Administrator account in Windows was used to create the existing DB, that would explain why it is also a SQL DBA.