Creating and connecting to a database from a backup

Posted by Admin on 20-Oct-2009 14:25

I've got a full database backup that I need to restore as a new database on a fresh install of OpenEdge (version 10.2A on Windows Server 2003)

I've successfully got the AdminServer running and connected to localhost database server via the Progress Explorer tool. I was even able to take the backup and "restore" it using prorest.bat dbname c:\backuplocation

However, I cannot figure out how to mount the new database and browse through its objects.

I tried creating a database in Progress Explorer but it could never connect. I tried using dbman, which appears to have the same problem as connecting in Progress Explorer. Finally, I've also tried starting the database using proserve.bat.

Can anyone walk me through the scenario where I have a backup and want to create a new database from it, and then connecting to it through the Explorer tool so I can see the schema? I'll need to run queries too - but needless to say since I can't connect to the database, I haven't yet looked into how that might be done.

Thanks for your help, and please feel free to ask any followup questions that might be helpful.

All Replies

Posted by kevin_saunders on 20-Oct-2009 15:25

When creating an instance in the Progress Explorer tool you are not creating the actual database. You are only creating a connection for managing it..

To create a void database structure to restore into, you want to use the 'prostrct create' command. You can use the 'prorest db-name backup-name -list' command to get a listing of the required structure, before creating the new db - this information needs to go into a .st file in the appropriate format. Then use 'prorest db-name backup-file-name' to restore the backup into the newly created void database.

Posted by Admin on 20-Oct-2009 16:04

Thanks for the reply. I had assumed that since it produced .st, .b1, .d1, .lg, and .db files without having run prostrct create that the backup restore had worked.

I tried it the way you suggested - that I get the params and create an .st file, then feed it to prostrct create, and only then restoring the backup.

However, it tells me the blocksizes don't match. I'm hoping this isn't the block size of the drive which would mean I need to reformat my disk. Tell me I'm wrong and this is a setting in the structure file (or perhaps we can reformat just that file in some way)?



Posted by kevin_saunders on 21-Oct-2009 02:22

Interesting - because if it produces all those files then it did work.. I suspect I shouldn't respond to post later than 5PM.. :-D

Fortunately, you are wrong.. The blocksize it is referring to is the blocksize of the database. When you do the prostrct create, add the -blocksize parameter. The default on Windows is 4096, but it needs to be identical to the original.

After creating the instance in Progress Explorer, what errors did you get when attempting to start the database? Also, what errors did you get when trying with proserve??

Posted by Admin on 21-Oct-2009 08:16

When I try to create a config file to the first DB (the one restored without first having a void DB), this is what I'm doing and what happens:

1) Right click on Databases in Progess Explorer, then click new

2) Name the DB "dbname"

3) Path and filename of database: c:\path_to_db\dbname.db (I've also tried the .d1 file and leaving off the extension)

4) defaultConfiguration is the only option for config, and it is highlighted. I leave the boxes for clustering and autostart unchecked.

5) I notice that it says "Database not found" at the bottom of the window, above the buttons.

6) Click OK

7) Right click on the database and click "Start"

8) Wait for what seems like forever for it to say that the startup of the database timed out.

After that, I try to start it again and it says the item is not networked and it won't have communication w/ the admin server. I tell it I want to proceed anyway and it then says "Error in startup of database ... Message: BROKER ** This process terminated with exit code 1. (8619) (JUNMsg024)"

That last message is also what I get when using dbserve from the command line (I suppose it uses it behind the scenes anyway)

I haven't been successful in finding any results from google when searching for those error codes, and when I try exact phrases and include the JUNMsg024, it fails to find any documents whatsoever.

When I try using the blocksize parameter when creating a void DB, the restore process starts working and then tells me "Unable to extend data files enough to proceed..." There is plenty of space on the hard drive. Perhaps I need to set the size of the DB in the .st file? (The .st file for doing the first restore - without first having a DB does not have this info in it, but the prorest -list shows it. However, the size shown in prorest -list is still smaller (by more than 1/2) than the size of the DB when it was expanded without first creating a void DB)

Does any of that help you diagnose?

Thanks again for your time. It's is much appreciated!

EDIT: I was able to indicate the size in the .st file and have the backup restore to void DB run without failing. However, the "minmum blocks to restore" is not the same as the number of blocks it says it read:

OpenEdge Release 10.2A as of Mon Nov  3 22:05:01 EST 2008
Start of extending target DB to needed size... (9432)
This is a full backup of OriginalDBLocaiton. (6759)
This backup was taken Wed Sep 23 22:00:02 2009. (6760)
The blocksize is 8192. (6994)
It will require a minimum of 645343 blocks to restore. (6763)
Start of restoring the target DB... (9433)
Read 634072 db blocks in 00:09:22

Posted by kevin_saunders on 21-Oct-2009 09:36

It's entirely possible that the database did start the first time you attempted it. But, if you don't specify a Network Port the AdminServer cannot communicate with the DB broker and doesn't know whether it is running or not.. So, the second time it errors out because it is already running.. Best bet is to check the dbname.lg file to check if it started or not.

Minimum blocks to restore "It will require a minimum of 645343 blocks to restore. (6763)" and DB blocks "Read 634072 db blocks in 00:09:22" are not the same thing. It is possible there are some BI blocks required for the restore too, depending on the type of backup taken originally.

Posted by Admin on 21-Oct-2009 10:11

Ok, great news. I added -S 12123 to the "other server arguments" in the configuration and tried to start it again, but got the same error. I checked the log file dbname.lg and found out the database was indeed running, and it had some "Unable to find protocol ..." message just before an "Unrecoverable error..." message.

That meant the reason for the exit w/ code 1 message from additional connection attempts in the Explorer tool was because it was already running. I killed it via cmd line tool "proshut dbname" and restarted via Explorer and now the status is running.

So that leads to the last of the original question: is there Progress client I should be using to connect to the DB so I can explore its schema and query it? I thought Progress Explorer was going to allow me to do that, but now my understanding is that it just manages all the different Progress products.

I checked the descriptions of OpenClient and OpenEdge Studio, but neither seem to be what I'm looking for.

Do I need the Results product for this, or is there something built in? I'm more interested in GUIs (ideally something like SQL Server Management Studio) but I'm OK with command line tools if that's all that are available.

Thanks again, you've been incredibly helpful!

PS: failing a built-in option, is there an ODBC driver available that I could install on my system to use to connect with DB Studio tools I already own?

Posted by Thomas Mercer-Hursh on 21-Oct-2009 11:37

A simple mpro dbname should get you into a client session, assuming you have the correct path set up, which seems likely since you can use other command line tools.  Go to the menu for tools and you will find DB administration where you can explore the schema.

ODBC is also possible.

Sounds like you could use a smidgen of training ...

Posted by Admin on 21-Oct-2009 12:11

Thanks for the reply.

I tried "mpro dbname" from the command line. It looks like it draws "Progress" as ascii art and proceeds to a blank blue screen too quickly for me to read anything. Typing any key after that returns me to the command prompt. Still willing to go this route if I can learn why this happens and how to prevent it from doing so.

As for training, it may be advisable. My first look at OpenEdge was yesterday morning, a few hours before I posted my question. (That few hours was spent trying to find the info on my own). I don't expect to spend much time in OpenEdge though (at least not at the moment). For the task I'm trying to accomplish, I just need to get in, look at this database, and pull out some information. I'm competent enough with DBs to do the rest without any assistance; it's the "getting in" part I'm having trouble with here. =) At least for the moment, I don't need to administer or do anything long term. Just browse the schema and write a couple of queries once I figure out where the info I need resides. I don't want to sound unconsciously incompetent, but it sounds that way anyway... =)

So, back to the questioning: Do you have any recommendations for where I might find an ODBC driver? I got a couple installed without having any success at getting them to work. I tried the SequelLink from DataDirect and another one from an installer for an app that uses OpenEdge, but that one was for 10.1B, and says the setup routines could not be loaded when I try to add a new datasource through Windows ODBC administrator.

Thanks again for your help. I appreciate both of your efforts, and anyone else who wants to join in for that matter!

Posted by Thomas Mercer-Hursh on 21-Oct-2009 12:29

For your issues with mpro, I suggest you look in the dbname.lg file to see if there are explanations.

For ODBC, you have two pieces of setup.  One is the server side.  There is a document about that somewhere on PSDN, but you could also try looking at this, particularly the child pages, for the issues about set up.  Note that, when installing the Progress license, you need to have specified both 4GL and SQL clients.

You have a SQL client as a part of the database which you can run on the server.  This is sqlexp ... not the most beautiful thing, but adequate for basic tasks as described in the cited pages.

For access from a PC or other remote system, you will need the driver.  I don't recall if you said what version, but if V9 or below, this was packaged as a separate license called either Client Networking or SQL Client Networking and there was a license fee.  OE10 and above it is a separate download availble through your ESD account for free.

Posted by ChUIMonster on 22-Oct-2009 07:24

The "blue screen" is because the character client (mpro) has some ridiculous default geometry like a 300 row screen.  You can fix that by right clicking the icon in the top left corner.  (I have no idea why PSC hasn't fixed it.  But it has been that way for at least 10 years.)  Set it to something reasonable like 30 lines and you should see "the editor".  This is a character interface to the 4GL.  THE 4GL IS NOT SQL.  Even though it will accept some SQL commands -- they are SQL-89 and they have lots of quirks.  This is not the natural way to use Progress.  These SQL-89 commands inside the 4GL are an ancient holdover that, IMHO, was only ever useful as a marketing "checkoff".  You sound like a SQL guy.  You will be much better off using the SQL-92 interface through ODBC.

If you installed a client license you should, by default, have the ODBC drivers.  They will be on the Data Source Administration "Drivers" tab under "OpenEdge".  Setup a DSN and use your favorite GUI SQL tool to explore the database.

Posted by Admin on 22-Oct-2009 10:14

I ended up never able to use the client tools. I haven't tried the blue-screen fix from Tom Bascom because I finally got a connection through ODBC. I am currently on the trial version, so I couldn't download the Progress-provided ODBC drivers. I looked into the DataDirect drivers, but was unable to find one for Progress OpenEdge. They had some for very old versions, and had a bundle I tried installing, which didn't seem to have the ones I needed.

I did have ODBC drivers for 10.1 from a client tool install I used from the vendor of the application that uses the DB I'm trying to connect to. However, they just responded with error messages when I tried to set up a data source.

I finally got a successful test connection using the ODBC drivers for OpenEdge 10.0 from OpenLink. I tried setting up a linked server in SQL Server so I could use SQL Server Management Studio to issue queries, but it just hung on me when trying to connect. Finally, I downloaded Squirrel SQL and was able to connect, browse the schema, and start writing queries.

Thanks to all of you again for helping walk me through this process!

This thread is closed