backup batch script for after image daily backup

[bfreedlander]

I am using progress 9.1d on a windows 2003 server.

I am trying to create a bat file to run each night to:
1. take down my database that has ai
2. backup my ai file (I only have 1 variable length file)
3. backup my database (about 2gb in size, takes about 3 minutes)
4. restart my database with ai

Below is my script. The rem'd lines represent the lines of code in the called bat files. The batch file works well until it gets to starting the database. It does start the database but it keeps the backup.log file locked until I take the database down. If I run the start database bat file individually from a cmd prompt then all is well (the backup.log file is not locked). Below the script is the data found in the backup.log.

Does anyone have any idea why this is happening?

rem Backup steps

echo shutdown the database at %DATE% %TIME% > c:\temp\backup.log
call R:\Syte6\ksb\batch\z-stopsyteline.bat >> c:\temp\backup.log 2>&1
rem d:
rem cd d:\sytedb
rem r:\dlc91d\bin\_mprshut.exe symix -by

echo backup the ai files at %DATE% %TIME% >> c:\temp\backup.log
copy z:\symix.a* z:\ai.backup /y >> c:\temp\backup.log 2>&1

echo disable ai at %DATE% %TIME% >> c:\temp\backup.log
call R:\Syte6\ksb\batch\z-disable-ai.bat >> c:\temp\backup.log 2>&1
rem d:\dlc91d\bin\rfutil symix -C aimage end

echo backup syteline at %DATE% %TIME% >> c:\temp\backup.log
call R:\Syte6\ksb\batch\z-backupsyteline.bat >> c:\temp\backup.log 2>&1
rem r:\dlc91d\bin\probkup.bat d:\sytedb\symix Z:\symix -verbose

rem starting database with ai

echo start ai at %DATE% %TIME% >> c:\temp\backup.log
start /wait R:\Syte6\ksb\batch\z-begin-ai.bat
rem d:
rem cd d:\Sytedb
rem d:\dlc91d\bin\rfutil symix -C aimage begin >> c:\temp\backup.log 2>&1

echo wait 30 seconds to give ai begin time to finish before starting database at %DATE% %TIME% >> c:\temp\backup.log
r:\temp\wait 30

echo start styeline at %DATE% %TIME% >> c:\temp\backup.log
call R:\Syte6\ksb\batch\z-startsyteline.bat
rem r:\dlc91d\bin\proserve symix -pf r:\syte6\db\symix.pf -aistall >> c:\temp\backup.log 2>&1

************************************************************************
Backup.log
shutdown the database at Thu 06/14/2012 15:01:56.65

R:\Syte6\ksb\batch>d:

D:\>cd d:\sytedb

D:\Sytedb>r:\dlc91d\bin\_mprshut.exe symix -by
Shutdown is executing. (1613)
Shutdown complete. (1614)
backup the ai files at Thu 06/14/2012 15:01:58.76
z:\symix.a1
1 file(s) copied.
disable ai at Thu 06/14/2012 15:01:58.79

D:\Sytedb>d:\dlc91d\bin\rfutil symix -C aimage end
PROGRESS Version 9.1D08 as of Tue Jan 20 17:34:57 EST 2004
After-image disabled. (846)
backup syteline at Thu 06/14/2012 15:01:58.87
PROGRESS Version 9.1D08 as of Tue Jan 20 17:34:57 EST 2004

573359 active blocks out of 573359 blocks in d:\sytedb\symix will be dumped. (6686)
0 bi blocks will be dumped. (6688)
The blocksize is 4096. (6994)
Backup requires an estimated 2.2 GBytes of media. (9285)
Restore would require an estimated 573359 db blocks using 2.2 GBytes of media. (9286)
Backed up 21750 db blocks in 00:00:10
Backed up 52661 db blocks in 00:00:20
Backed up 100232 db blocks in 00:00:30
Backed up 175660 db blocks in 00:00:40
Backed up 247899 db blocks in 00:00:50
Backed up 302358 db blocks in 00:01:00
Backed up 336153 db blocks in 00:01:10
Backed up 370457 db blocks in 00:01:20
Backed up 403947 db blocks in 00:01:30
Backed up 438760 db blocks in 00:01:40
Backed up 470994 db blocks in 00:01:50
Backed up 504993 db blocks in 00:02:00
Backed up 539025 db blocks in 00:02:10
Backed up 573359 db blocks in 00:02:19
Wrote a total of 16898 backup blocks using 2.2 GBytes of media. (9284)

Backup complete. (3740)
start ai at Thu 06/14/2012 15:04:19.43
PROGRESS Version 9.1D08 as of Tue Jan 20 17:34:57 EST 2004
The BI file is being automatically truncated. (1526)
wait 30 seconds to give ai begin time to finish before starting database at Thu 06/14/2012 15:05:19.82
start styeline at Thu 06/14/2012 15:05:49.96
PROGRESS Version 9.1D08 as of Tue Jan 20 17:34:57 EST 2004
15:05:50 BROKER : This broker will terminate when session ends. (5405)

*****************************************************************
The following is the output of the start the database when run directly in a cmd prompt window and is what I would have expected in the backup.log with the backup.log being released.

D:\Sytedb>r:\dlc91d\bin\proserve symix -pf r:\syte6\db\symix.pf -aistall
PROGRESS Version 9.1D08 as of Tue Jan 20 17:34:57 EST 2004
15:17:38 BROKER : This broker will terminate when session ends. (5405)
15:17:38 BROKER 0: File z:\symix.a1 is on a remote device. (9466)
15:17:38 BROKER 0: Multi-user session begin. (333)
15:17:38 BROKER 0: Begin Physical Redo Phase at 0 . (5326)
15:17:38 BROKER 0: Physical Redo Phase Completed at blk 0 off 1752 upd 0. (7161
)
15:17:38 BROKER 1: Started for syteline using TCP, pid 5952. (5644)
15:17:38 BROKER 1: This is an additional broker for this protocol. (5645)
15:17:38 BROKER 1: This broker supports both 4GL and SQL server groups. (8865)

 

[Cringer]

Progress 9.1d is completely obsolete and unsupported. You really should get to version 10 or even 11. I can't remember if online backups are available in 9.1d, but they certainly are in 10. That would save you no end of hassle.

 

[RealHeavyDude]

For the record: Online backups are available since V6, but I have to admit that I didn't succeed back then ( beginning of the '90s when I was a Progress newbie ).

As I have never used any V7 whatsoever I can't tell from my experience whether it worked or not in V7. But I did successfully use online backups from V8 on.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Thanks for the clarification RHD

 

[RealHeavyDude]

I am not Windows scripting guru - but I guess you should use call with the commands you rem'd in your bat. I always use it and don't have any problems. But I have to admit that I rarely do database administration related tasks on a Windows machine.

Plus, as Cringer pointed out - if I were you I would switch to online backups. I don't see much benefit in doing offline backups when they are not combined with other maintenance tasks that require the database to be offline. One of them would be an index rebuild for example.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Aside from all of that...

You have a single AI file? Why?

A proper implementation of AI requires a minimum of 4 extents. And some background scripting to rotate and archive them.

 

[bfreedlander]

Thank you all for your responses. No one seems to have an answer to how to run proserv from a bat file while writing to a log file and proserv releasing the log file. Adding call did not help. I have now stopped redirecting the output of proserv to my log file and my bat file works. In answer to Tom, my database is relatively small and I am doing a full backup every night. This is my first time setting up ai. Under my circumstances I don't see the need for multiple ai files. I have one variable length ai file on a raid system with plenty of room. Perhaps you could explain.

 

[trx]

If you don't use multiple AI files, then you cannot recover in case of database crash. I am not quite sure, but in case of crash you will have damaged database and no good (complete) AI file - you will have database backup from previous day which you can restore, but you won't have "finished" AI files to apply and roll forward. For recovery purposes you need at least 2 variable AI extents - one busy and one empty and manually (or via cron) switch between them and archive full AI extents. Then you will be able to apply archived AI to your backup and restore database to point in time till last finished AI.
Now after executing your script you have database backup (for current day) and AI valid for previous day backup - so in fact you can only restore to any point between previous backup and current. That doesn't protect you in case of database crash.
As for your logs - it seems to me that backup.log is also log for Progress database server - just use another file for server logs.

 

[bfreedlander]

I tried changing the log name. That did not help. Thanks for the suggestion.

 

[RealHeavyDude]

For the record: Any AI file that you still have after a database crash is a good one. For that you don't need more than one - you just need to make sure that it is not on the same hard disk ( or file system ) where the database and / or the before image is located. That way, if you lose the database, together with the last good backup ( by good I mean tested ) and the after image, regardless of how many files it consists, you can restore the database to the point in time when the crash happened - minus the transactions that were open at that time.

Having multiple after image files is necessary as soon as you change your backup strategy to involve online backups as that will automatically switch to the next free extent. If there is none it will crash. Plus if you log based replication by periodically copying the after image to a luke warm standby and roll it forward you will also need more than one extent. That's why Tom recommends to have at least 4 extents.

If you keep the offline backups you can keep your one after image extent - but I still recommend you to switch to online backups. Every time you shut down the database server you will not only take the database offline, you will also destroy the buffer pool optimization and users will face a not so good performance when they start working next day in the morning until the buffer pool is optimized again.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

For some ideas on how to properly implement after-imaging: http://dbappraise.com/ppt/ai.pptx

4 extents is the minimum because you need:

1) The active extent.
2) A FULL extent which is in the process of being archived to a safe location. "Safe" being at the very least on another physical server. Best practice is in a distinct geographic location that is out of the natural hazard zone (earthquakes, floods, blizzards, hurricanes etc.)
3) An EMPTY extent to handle the possibility that an online utility (such as probkup) will kicking while still in the process of archiving.
4) A second EMPTY to handle the possibility that you may someday implement OE Replication and need to allow for LOCKED extents. Plus it makes it an even number and a power of two -- which means that you can, in theory, alternate extents between disks if you are worried about IO contention while archiving.

Yes, AI will "work" with just one extent and off-line backups. And there are a few failure modes where you might even benefit from it. But the bulk of the use-cases for after-image based recovery assume that the system where the database resides has been destroyed. Which means that the last ai file (in this case the only ai file) has also been destroyed.

 

[bfreedlander]

After hearing from everyone, I am now trying to set up online backup with ai. I have set up 8 ai files and I am now just trying to get through the process before I attempt to write an automated script. If anyone has an automated script they wish to share please send it on.

 

[Rob Fitzpatrick]

If you can get off of 9.1D and onto a recent release, e.g. 10.2B or later, you will see many benefits. One of these is the AI file management daemon. It isn't perfect, but it certainly does make AI extent management easier. It eliminates the need to script extent switching and archiving on your production database. It doesn't take care of roll-forward to a target database, but it's better than nothing.

Also, you have an array of choices with AI. I recommend that you go with all variable extents and switch at a given interval, as opposed to using fixed-size files and switching when they are full.

 

[bfreedlander]

I must stay on 9.1d. Our ERP supplier will not support our version of the software on anything higher than 9.1d

 

[TomBascom]

If that is the case then your ERP isn't supporting you NOW and hasn't been for quite some time. You lose nothing by upgrading. You will even save money as a result since your annual maintenance fee is a few points lower if you are on a supported Progress release.

If you can recompile the code you can upgrade to any version of Progress that you want.

If you cannot then you can still upgrade but the end of the road is the end of version 9 -- which is 9.1E service pack 4. Which has plenty of execllent bug fixes and performance improvements over 9.1D. And, as I recall, several of those bug fixes are related to after-imaging.

 

[repostor]

If you want this to be automated, than you can have a look at "Data Protector for Progress OpenEdge 4GL using IBM Tivoli Storage Manager".
With this solution you can do Full / Incremental / After-Image (log) backups directly to TSM.
The restore is just "one click away", a oneliner in command line:

(example to restore as far as possible)

# progressrestore -s yourdb

(it will first restore latest full backup)
(restore all incremental from last full until point-in-time, or as far-as-possible)
(restore and apply all after images from last incremental until point-in-time, or as far as possible)