829.TXT
=====================================================================
Automating Database Backup
=====================================================================
PRODUCT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: General Information AREA : General Information
=====================================================================
One of the most difficult tasks of an application developer is getting
users to do regular backups of their database. All too often, when
the time comes to restore a backup, there is not a current backup or
it is faulty. The following procedure comes from Tom Grimshaw of Just
For You Software in Sydney, Australia. The procedure is compatible
with R:BASE 4.5 and higher.
Tom developed a routine that encourages his clients to backup their
database regularly, and can be used by any client as the database
name is not hard coded into the backup procedures.
Here are Tom's step-by-step procedures:
1. I entered the following two lines in the main application file to
set variables to the database name and the name I designated as the
old database name. The database names are different for each
application. The backup and restore procedures use the variables
instead of hard coding the database name into the procedure.
SET VAR vdbname TEXT = 'DBNAME'
SET VAR voldname TEXT = 'DB_OLD'
Note: you could use the CVAL function to return the database name to
a variable:
SET VAR vdbname TEXT = (CVAL('DATABASE'))
SET VAR vdbname TEXT = (CVAL('DBPATH'))
The keyword "DATABASE" returns the database name, for example,
concomp. The keyword "DBPATH" returns the full path name of the
database, for example, c:\rbwin\concomp.
2. I create a table in my application databases to store information
about the last backup. The users cannot modify the data in this table
from within the application.
CREATE TABLE backups_done +
(bu_date DATE , +
bu_numb INTEGER , +
bu_drive TEXT (1) )
3. I wrote a routine to determine the number of days since the last
backup to diskette and prevent access to the database until a backup
has been done to diskette if none found in the backups_done table
within the last seven days.
SELECT MAX(bu_date) +
INTO vlastbudate +
FROM backups_done +
WHERE bu_drive <> 'C'
IF vlastbudate < (.#DATE - 7) THEN
DISPLAY screen2 IN application_name.APX
PAUSE 1
RUN bu_now IN application_name.APX
ENDIF
If users should backup to diskette more frequently than every week,
simply change the IF statement to (#DATE - 1), for example, to
require backups every other day.
4. Then I realized there was nothing to stop a person from exiting
the backup program without doing a backup and resuming from the
place they left off in the main application so I wrote the
following to go immediately under the code in step 3 to check for
the above circumstance and force an exit from the application in
that case.
SELECT MAX(bu_date) +
INTO vdatelastbudone +
FROM backups_done +
WHERE bu_drive <> 'C'
IF vdatelastbudone < (.#DATE - 7) THEN
WRITE 'No backup recorded, please restart and do backup.'
AT 15,12
PAUSE 1
GOTO lend1
ENDIF
5. I obviously needed the following to call the backup to diskette
routine in the backup application file from the main application.
$COMMAND
bu_now
RUN disketbu IN dbcopybu.apx
RETURN
6. In the CLEANUP command block of the main application file I called
the backup application to remind operators to do a backup on the way
out of the application.
$COMMAND
CLEANUP
RUN dbcopybu IN dbcopybu.apx
RETURN
Following is Tom's complete application file. Tom's procedures are
designed for a single-user database application. Changes must be made
for multi-user applications as some of the processes, such as copying
or packing a database, cannot be done except in a single-user mode.
Also, you need to modify the code to reflect the database residing on
a file server other than the C drive.
$COMMAND
DBCOPYBU
DISCONNECT
LABEL LBEGBU
NEWPAGE
SET VAR SAVE_MESSAGES = (CVAL('MESSAGES'))
SET VAR SAVE_ERROR = (CVAL('ERROR'))
SET MESSAGES ON
SET ERROR MESSAGES ON
SET ECHO ON
SET COLOR WHITE ON BLUE
CONNECT &vdbname
SET VAR varerror = .SQLCODE
IF varerror <> 0 THEN
CLS FROM 1,1 TO 25,80
WRITE 'Could not connect to database.' AT 4,20
WRITE 'Recommend you do a restore from the latest backup.'
AT 6,20
PAUSE 1
RUN restr_bu IN dbcopybu.apx
ELSE
SELECT MAX(bu_numb) +
INTO vlastnumb +
FROM backups_done +
WHERE bu_date = .#DATE
IF vlastnumb IS NULL THEN
SET VAR vlastnumb INTEGER = 0
CLS FROM 1,1 TO 25,80
DISPLAY screen1 IN DBCOPYBU.APX
PAUSE 1
ENDIF
ENDIF
CHOOSE PICK5 FROM utilmenu IN dbcopybu.apx AT 6 21 BLACK ON
GRAY
IF PICK5 = '[ESC]' THEN
GOTO LENDBU
ENDIF
SWITCH (.PICK5)
CASE 'Backup to diskette'
RUN disketbu IN dbcopybu.apx
BREAK
CASE 'Backup to hard disk'
RUN hddbu IN dbcopybu.apx
BREAK
CASE 'Copy Database'
RUN cpymydb IN dbcopybu.apx
BREAK
CASE 'Pack In Place'
RUN pckinplc IN dbcopybu.apx
BREAK
CASE 'Reload'
RUN cpyrelod IN dbcopybu.apx
BREAK
CASE 'Restore Backup'
RUN restr_bu IN dbcopybu.apx
BREAK
CASE 'Exit'
GOTO LENDBU
BREAK
ENDSW
GOTO LBEGBU
LABEL LENDBU
SET MESSAGES .SAVE_MESSAGES
SET ERROR MESSAGES .SAVE_ERROR
*(CLEAR VAR SAVE_MESSAGES, SAVE_ERROR, pick5, varerror,
vlastnumb, +
vrespons_rest, vendkey)
NEWPAGE
RETURN
$MENU
utilmenu
POPUP |Back up and restore menu|
|Backup to diskette|
|Backup to hard disk|
|Copy Database|
|Pack In Place|
|Reload|
|Restore Backup|
|Exit|
ENDC
$SCREEN
screen1
You have not done a backup today. It is most strongly recommended to
do a backup to the hard disk after each hour's work and a backup to
diskette minimally at the end of each day, or even half day if a lot
of work is being entered into the database.
As Clint Eastwood might ask you, 'Do you feel lucky?'
The question to ask yourself is, 'How much work would I like
to re-enter if the database crashed because of a power surge?'
Your answer to that question determines how frequently you
backup.
$SCREEN
warnmsge
W A R N I N G ! ! !
W A R N I N G ! ! !
DO NOT INTERRUPT THIS PROCEDURE ONCE BEGUN.
TO DO SO WOULD ALMOST CERTAINLY CORRUPT YOUR DATABASE
AND YOU KNOW HOW MUCH WORK THAT WOULD MEAN!!!!
----
W A R N I N G ! ! !
W A R N I N G ! ! !
$SCREEN
userabor
Discontinued procedure at user request.
Returning control to keyboard.
$COMMAND
disketbu
CLS FROM 1,1 TO 25,80
WRITE 'This procedure will create a backup copy of the
database' +
AT 10,13
WRITE 'on diskette(s).' AT 11,13
WRITE 'Please insert a blank, formatted diskette in the+
drive' AT 13,13
WRITE 'to which you wish to back up and press any key when
ready.' +
AT 14,13
PAUSE 1
CLS FROM 1,1 TO 25,80
SET VAR vnom_backupdr TEXT = 'A'
DIALOG 'Enter drive to back up to - A or B etc.:' +
vnom_backupdr = 1 vendkey 1 AT 16
IF vendkey = '[Esc]' THEN
BREAK
ENDIF
SET VAR vbackupdr = (SGET(.vnom_backupdr,1,1))
SELECT MAX(bu_numb) +
INTO vlastnumb +
FROM backups_done +
WHERE bu_date = .#DATE
IF vlastnumb IS NULL THEN
SET VAR vlastnumb INTEGER = 0
ENDIF
SET ESCAPE OFF
CLS FROM 1,1 TO 25,80
DISPLAY warnmsge IN dbcopybu.apx
SET VAR vdrive TEXT = (.vbackupdr + ':\'), +
viyear INTEGER = (IYR(.#DATE)), +
vimon INTEGER = (IMON(.#DATE)), +
viday INTEGER = (IDAY(.#DATE)), +
vsuffix INTEGER = (.vlastnumb + 1)
SET VAR viyear TEXT, +
vtest1 INTEGER = .vimon, +
vtest2 INTEGER = .viday, +
vtest3 INTEGER = .vsuffix
IF vtest1 < 10 THEN
SET VAR vimon TEXT, vaddzero TEXT = '0'
SET VAR v1 TEXT = (.vaddzero + .vimon)
ELSE
SET VAR vimon TEXT
SET VAR v1 TEXT = .vimon
ENDIF
IF vtest2 < 10 THEN
SET VAR viday TEXT, vaddzero TEXT = '0'
SET VAR v2 TEXT = (.vaddzero + .viday)
ELSE
SET VAR viday TEXT
SET VAR v2 TEXT = (.viday)
ENDIF
IF vtest3 < 10 THEN
SET VAR vsuffix TEXT, vaddzero TEXT = '.00'
SET VAR vsuffix2 TEXT = (.vaddzero + .vsuffix)
ELSE
IF vsuffix BETWEEN 10 AND 99 THEN
SET VAR vsuffix TEXT, vaddzero TEXT = '.0'
SET VAR vsuffix2 TEXT = (.vaddzero + .vsuffix)
ELSE
SET VAR vsuffix TEXT, vadddot TEXT = '.'
SET VAR vsuffix2 TEXT = (.vadddot + .vsuffix)
ENDIF
ENDIF
SET VAR vbackupname = (.vdrive + .viyear + .v1 + .v2 +
.vsuffix2)
INSERT INTO backups_done VALUES (.#DATE, .vsuffix,
.vbackupdr)
SET NULL -0-
OUTPUT &vbackupname
BACKUP ALL
OUTPUT SCREEN
*(CLEAR VAR vbackupname, v1, v2, vsuffix, vsuffix2, vimon,
vlastnumb, +
viyear, viday, vaddzero, vdrive, vbackupdr, vtest1, vtest2,
vtest3, +
vnom_backupdr)
SET ESCAPE ON
RETURN
$COMMAND
hddbu
CLS FROM 1,1 TO 25,80
SET VAR vtxtmsg_bu2 TEXT = (.vdbname + '.cpy')
SET VAR vtxtmsg_bu3 TEXT = 'on the hard disk.'
SET VAR vtxtmsg_bu4 TEXT = (.vtxtmsg_bu2 & .vtxtmsg_bu3)
WRITE 'This procedure will backup your database to the file'
AT 10,10
WRITE .vtxtmsg_bu4 AT 12,10
DIALOG 'YES to commence backup, NO to quit:' +
vrespons_proc vendkey YES AT 18
IF vendkey = '[ESC]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_proc = 'NO' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_proc = 'YES' THEN
CLS FROM 1,1 TO 25,80
DIALOG 'Press [Enter] or amend file name & press [Enter]:'+
vtxtmsg_bu2 vendkey 1 AT 16
IF vendkey = '[Esc]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
CLS FROM 1,1 TO 25,80
SET ESCAPE OFF
DISPLAY warnmsge IN dbcopybu.apx
SET VAR vdriveload TEXT = 'C'
SET VAR vdrive TEXT = (.vdriveload + ':\')
SET VAR vdb_lob_file TEXT = (.vdbname + '.lob')
SET VAR vlastnumb = (.vlastnumb + 1)
INSERT INTO backups_done VALUES (.#DATE, .vlastnumb,
.vdriveload)
DELETE frthlast.cpy
DELETE frthlast.lob
RENAME thrdlast.cpy frthlast.cpy
RENAME thrdlast.lob frthlast.lob
RENAME scndlast.cpy thrdlast.cpy
RENAME scndlast.lob thrdlast.lob
RENAME &vtxtmsg_bu2 scndlast.cpy
RENAME &vdb_lob_file scndlast.lob
SET NULL -0-
OUTPUT &vtxtmsg_bu2
BACKUP ALL
OUTPUT SCREEN
SET ESCAPE ON
CLS FROM 1,1 TO 25,80
ELSE
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
ENDIF
RETURN
$COMMAND
cpymydb
CLS FROM 1,1 TO 25,80
SET VAR vtxtmsg_cpy1 TEXT = +
'It must be different from the existing name of'
SET VAR vtxtmsg_cpy2 TEXT = (.vtxtmsg_cpy1 & .vdbname)
WRITE 'This procedure will copy your existing database
files.' AT 10,13
WRITE 'Please enter the new name to copy the database files
to.'+
AT 11,13
WRITE .vtxtmsg_cpy2 AT 12,13
WRITE 'and no more than eight characters long starting with a+
letter.'+
AT 13,13
DIALOG 'Enter the new database name:' vresp_dbname = 8
vendkey 1 AT 16
IF vendkey = '[ESC]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
SET VAR vrespons_name = (SGET(.vresp_dbname,8,1))
IF vrespons_name IS NULL THEN
CLS FROM 1,1 TO 25,80
WRITE 'No filename specified to copy database to.' AT 8,12
WRITE 'Procedure terminating and returning control to
keyboard.' +
AT 10,12
PAUSE 1
BREAK
ELSE
CLS FROM 1,1 TO 25,80
SET ESCAPE OFF
DISPLAY warnmsge IN dbcopybu.apx
DISC
SET VAR vexistfile1 TEXT = (.vdbname + '.rb1'), +
vexistfile2 TEXT = (.vdbname + '.rb2'), +
vexistfile3 TEXT = (.vdbname + '.rb3'), +
vexistfile4 TEXT = (.vdbname + '.rb4')
SET VAR vnewfile1 TEXT = (.vrespons_name + '.rb1'), +
vnewfile2 TEXT = (.vrespons_name + '.rb2'), +
vnewfile3 TEXT = (.vrespons_name + '.rb3'), +
vnewfile4 TEXT = (.vrespons_name + '.rb4')
IF vexistfile1 = .vnewfile1 THEN
CLS FROM 1,1 TO 25,80
WRITE 'Cannot copy database to the same name!' AT 10,13
WRITE 'Procedure terminating and returning control to keyboard.' +
AT 10,13
BREAK
ENDIF
COPY &vexistfile1 &vnewfile1
COPY &vexistfile2 &vnewfile2
COPY &vexistfile3 &vnewfile3
COPY &vexistfile4 &vnewfile4
DISC
ENDIF
CONNECT &vdbname
RETURN
$COMMAND
restr_bu
CLS FROM 1,1 TO 25,80
WRITE 'DO NOT PROCEED UNLESS YOU ARE CERTAIN OF WHAT YOU ARE DOING!' +
AT 3,10
WRITE 'This action will DELETE your existing database and replace' +
AT 5,10
WRITE 'it with a copy from a back up file.' AT 6,10
WRITE 'Please be very certain of the full name and extension
of the' +
AT 7,10
WRITE 'back up file and its drive and path before
proceeding!' AT 8,10
WRITE 'If you don't have a back up file and you proceed
past' AT 9,10
WRITE 'this point without quitting now, you will erase the
existing' +
AT 10,10
WRITE 'copy of the database and lose all your data!' AT
11,10
WRITE 'If you are uncertain of any point either get advice
from' +
AT 12,10
WRITE 'someone who is certain or at least exit this
procedure now' +
AT 13,10
WRITE 'then rename the existing database files and write
down the' +
AT 14,10
WRITE 'exact drive, path and file name of the backup file
before +
proceeding.' AT 15,10
DIALOG 'YES to delete & restore from backup, NO to quit.' +
vrespons_rest vendkey NO AT 16
IF vendkey = '[Esc]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_rest = 'NO' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_rest = 'YES' THEN
CLS FROM 1,1 TO 25,80
WRITE 'You need to enter the backup drive, path & file
name.' AT 3,12
WRITE 'From the hard disk on a non-networked computer it
is' AT 5,12
WRITE 'normally [C:\rb\db\dbname.cpy] where dbname is the'
AT 6,12
WRITE 'name of your database.' AT 7,12
WRITE 'On a network the drive and directory may be
different.' AT 9,12
WRITE 'From a diskette it is normally [A:\19951012.001]' AT
11,12
WRITE 'being the year, month, day & number of the backup.'
AT 12,12
DIALOG 'Enter backup drive, path & filename & press
[Enter]:' +
vrespons_drive vendkey AT 16
CLS FROM 1,1 TO 25,80
IF vendkey = '[Esc]' THEN
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_drive IS NULL THEN
WRITE 'No drive specified to contain backup files.' AT 8,12
WRITE 'Procedure terminating and returning control to
keyboard.' +
AT 10,12
PAUSE 1
BREAK
ENDIF
DISC
DISPLAY warnmsge IN dbcopybu.apx
SET VAR vnewfile1 TEXT = (.vdbname + '.rb1')
SET VAR vnewfile2 TEXT = (.vdbname + '.rb2')
SET VAR vnewfile3 TEXT = (.vdbname + '.rb3')
SET VAR vnewfile4 TEXT = (.vdbname + '.rb4')
ERASE &vnewfile1
ERASE &vnewfile2
ERASE &vnewfile3
ERASE &vnewfile4
CLS FROM 1,1 TO 25,80
SET ESCAPE OFF
DISC
DISPLAY warnmsge IN dbcopybu.apx
RESTORE &vrespons_drive
SET ESCAPE ON
ENDIF
CONNECT &vdbname
*(CLEAR VAR vrespons, vbackupfile, vbackupname,vrespons_drive, +
vdrive, vdialogtext, vnewfile1, vnewfile2, vnewfile3,
vnewfile4, +
vrespons_rest)
RETURN
$COMMAND
cpyrelod
CLS FROM 1,1 TO 25,80
WRITE 'This action will rename your existing database' AT 4,10
WRITE 'and reload it back to the original name. In doing' AT 5,10
WRITE 'this it recovers any space consumed by deleted' AT 6,10
WRITE 'rows and collects the rows from each table into' AT 7,10
WRITE 'the same disk area which can speed access times.' AT 8,10
WRITE 'It is recommended that this procedure be done weekly.' AT 9,10
WRITE 'AND ONLY IMMEDIATELY AFTER A BACKUP so if there is' AT 10,10
WRITE 'any interruption and the database is destroyed you' AT 11,10
WRITE 'can easily restore from a backup with no loss of data.' AT+
12,10
DIALOG 'YES to copy & reload, NO to quit.' +
vrespons_copy vendkey NO AT 14
IF vendkey = '[Esc]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_copy = 'NO' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_copy = 'YES' THEN
CLS FROM 1,1 TO 25,80
DISCONNECT
DISPLAY warnmsge IN dbcopybu.apx
SET VAR voldfile1 TEXT = (.voldname + '.rb1')
SET VAR voldfile2 TEXT = (.voldname + '.rb2')
SET VAR voldfile3 TEXT = (.voldname + '.rb3')
SET VAR voldfile4 TEXT = (.voldname + '.rb4')
ERASE .voldfile1
ERASE .voldfile2
ERASE .voldfile3
ERASE .voldfile4
SET VAR vnewfile1 TEXT = (.vdbname + '.rb1')
SET VAR vnewfile2 TEXT = (.vdbname + '.rb2')
SET VAR vnewfile3 TEXT = (.vdbname + '.rb3')
SET VAR vnewfile4 TEXT = (.vdbname + '.rb4')
RENAME .vnewfile1 .voldfile1
RENAME .vnewfile2 .voldfile2
RENAME .vnewfile3 .voldfile3
RENAME .vnewfile4 .voldfile4
CONNECT &voldname
RELOAD &vdbname
DISCONNECT
CONNECT &vdbname
ENDIF
*(CLEAR VAR vrespons_copy, voldfile1, voldfile2, voldfile3,
voldfile4, +
vnewfile1, vnewfile2, vnewfile3, vnewfile4)
RETURN
$COMMAND
pckinplc
CLS FROM 1,1 TO 25,80
WRITE 'This action will pack your existing database in the
same' AT 4,10
WRITE 'location. In doing this it recovers any space
consumed' AT 5,10
WRITE 'by deleted rows which can speed access times but does
not' AT 6,10
WRITE 'collect the rows of a table to the same disk area. It
is' AT 7,10
WRITE 'recommended that this procedure be done weekly.' AT
8,10
WRITE 'AND ONLY IMMEDIATELY AFTER A BACKUP so if there is'
AT 9,10
WRITE 'any interruption and the database is destroyed you'
AT 10,10
WRITE 'can easily restore from a backup with no loss of
data.' AT 11,10
DIALOG 'YES to pack, NO to quit.' vrespons_pack vendkey NO
AT 16
IF vendkey = '[Esc]' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_pack = 'NO' THEN
CLS FROM 1,1 TO 25,80
DISPLAY userabor IN dbcopybu.apx
PAUSE 1
BREAK
ENDIF
IF vrespons_pack = 'YES' THEN
CLS FROM 1,1 TO 25,80
DISPLAY warnmsge IN dbcopybu.apx
CONNECT &vdbname
PACK &vdbname ALL
PAUSE 1
ENDIF
CLEAR VAR vrespons_pack
RETURN