FiveTech Software tech support forums

Posted: **Sat Aug 06, 2016 5:00 am**

(Note: Enhancements and additions in FWH16.08 are noted in Red Color in this post)

FWMariaConnection object provides 3 methods for backup and restore operations. As already mentioned, all features of FWH co-exist with other libraries like TMySql, TDolphin, etc and also ADO. Applications mainly relying on other libs can also use these methods for backup and restore.

For using these features we need to obtain a new FWH connection and close after use. In case the application is already connected using TMySql or ADO, it is very easy to open FWH Connection without providing user credentials again.

oFWCon := mysql_Connect( oAdoConObject ) // from ADO
or
oFWCon := mysql_Connect( oMySql ) // from TMySql
or use the standard method of providing host,database,username,password

The backup/restore functions properly handle all types of fields, eg timestamps, autoincrement fields, memo fields with embedded CRLF and special characters, binary data like images, etc properly.

Sample table backed up and restored:

METHODS: (updated)

Code: Select all

METHOD BackUp( [source], [dest], [bProgress], [nRecsInBatch], [nMaxBufperSQL] ) 
       --> cBackUpFileName
METHOD BackupIndex( cBackUpFileName, [lView] ) --> aIndex
METHOD Restore( cBackupFile, [aTables], [bProgress], [cNewDB] ) --> nil  // cNewDB added FWH16.08
METHOD RestoreFromSqlDump( cFile, bProgress ) // New in FWH 16.08

METHOD BackUp() // all parameters are optional.

Param-1: Source:
(a) Defaults to current database. Full database will be backedup
(b) cDataBasename: If the parameter is a character value, it is considered as Database name and the full database is backedup.
eg: oCn:BackUp( "fwh" )
(c) aTables: If the parameter is an array, it is treated as list of tables in the current database and all the tables in the array are backedup.

Param-2: Destination
(a) Defaults to current folder
(b) cFolderName: If the parameter is a character value without extension or terminated with '\', it is considered as folder name. The folder is created if it does not exist.
(c) cFileName: If the parameter is a character value with extension, it is considered a file name. The file is created if does not exist and is overwritten if exists.

Notes:
(a) & (b): Saving to folder.
The system is primarily designed for silent scheduled backups overnight. Depending on the date and time of backup, a sub-folder with the name of the weekday is created and the backup is saved with the name of the database and extention ".sql".
Example:
oCn:Backup( "fwh", "c:\mybackups" ) --> cBackupFilename
Assuming that this command is executed silently every night, each day the backups are created as:
c:\mybackups\sunday\fwh.sql
c:\mybackups\monday\fwh.sql, etc.
After one complete week, the previous week's file is overwritten. In other words, we always have one week's backup at any time. Since it is the general practice to run such a backup routine in the midnight around 00:00 hours or early small hours, the weekday name corresponds to the day before midnight.
Eg: Backup created at 5th August 2016 11:00 pm and also 6th August 2016 04:00 am are both saved to "friday" folder, indicating that the data is at the end of friday.
(c) Saving to a filename:
This option is suited for adhoc backups of selected tables.
Eg: oCn:Backup( { "states", "customer" }, "c:\mybackups\adhoc\stcust.bkp" )

Param-3: bProgress (optional)
If provided, this codeblck is evaluated for every table with table-name, serial-number, total-number-of-tables, time-in-seconds-from-start. It is generally not recommended to use this parameter since it only delays the process. On local host, even one million records table is saved within 5 seconds.

Param-4: Number of records per SQL. Optional. Default = 100 records.
Param-5: Maximum bytes in single SQL statement: Optional. Default = 4 MB.

Notes: These two parameters may be used keeping in view the max_packet_size permitted for the connection.
In case the table contains a blob or text (large memo) field, the number of records per sql is internally reduced to only 1 and this can not be changed.

METHOD BackupIndex( cBackupFile, [lView] ) --> aIndex // for information only

Every backup file contains an index of the tables saved. The information can be obtained and viewed using this method. If the optional second parameter is .t., the information is displayed in a browse.

Sample Browse:

METHOD Restore( cBackUpFile, [ aTables ], [bProgress], [cNewDB] ) --> nil

Param-1: Backup file name. This parameter is necessary
This file should be a valid backup file created by METHOD Backup (upto FWH 16.06)
If this file is a valid MySQLDump file, this method calls the method RestorefromSqlDump( cBackupFile, [bProgress] ) // 16.08

Param-2: One table name or an array of table names. Optional.

(a) If the second parameter is ommitted:
(i) If the backup file is for a full database, the entire database is created if does not exist on the server and all the tables are created/overwritten in that database.
(ii) If the backup file is for some tables only, all these tables are created/overwritten in the currently selected database on the server.
(b) If the second parameter is supplied:
All the tables specified, if exist in the backupfile, are created/overwritten in the currently selected database on the server.

Param-3: bProgress. Optional
If specified, this codeblock is evaluated with each table name.

Param-4: cNewDB Optional. Added in FWH 16.08
If specified for a full database backup, the backup will be restored with new database name : cNewDB. This parameter enables duplication of an existing database.

Explanation:

1) When cBackUpFile is Full Database Backup and 2nd and 4th parameters are omitted, full database and tables are restored to the same database from which the backup was made.
2) When cBackUpFile is Full Database Backup and 4th parameter is specified, new database cNewDB is created and all tables are restored to cNewDB.
3) If 2nd parameter is specified, table[s] named in the 2nd parameter are restored to the Currently logged in database, irrespective of the database from which the backup was made.

This enables copying some table[s] from one database to other database.

Code: Select all

oCn:SelectDB( "db1" )
oCn:BackUp( { "table1", "table2" }, "temp.sql" )
oCn:SelectDB( "db2" )
oCn:Restore( "temp.sql", { "table1" } )

In the above example, table1 of db1 is copied as table1 in db2.

METHOD RestoreFromSqlDump( cFile, bProgress ) // New in FWH 16.08

This method enables restoring from a MySqlDump file.

Posted: **Mon Aug 15, 2016 5:21 pm**

Main purpose of backup/restore is to backup all objects of database with the ability to reconstruct the full database in case of need.

FWH's backup/restore feature is greatly enhanced to meet this requirement fully.

Backup of full database backsup Tables, Views, Triggers, Functions and Procedures. Restoring restores all these objects in proper order making it on par with other professional backup/restore tools.

This enhancement is available in the next build due for release any time now.

Posted: **Tue Aug 16, 2016 6:01 am**

Mr. Rao,

Great job !!!

Thanks in advance.

Best regards,

Posted: **Fri Sep 30, 2016 11:10 am**

FWH 16.08

Added:
Facility to Restore a database backup with a different databasename
and
Facility to Restore a regular MySqlDump file.

Please see the revisions noted with Red color in the first post of this thread.

Posted: **Tue Oct 25, 2016 1:32 pm**

Hi, is it possible to hide msginfo messages for the 'backup' method? In other words, bypass msginfo messages for any error.

Posted: **Sat Oct 29, 2016 4:35 pm**

Mr vmarimon

This is the change implemented in FWH 16.09:

METHOD BackUp() is now made silent by default. In case of
error, oCn:nError is set to non-zero value, oCn:cError
is set with error information and returns .F..

If oCn:lLog or oCn:lLogErr is set to .T., the error is
logged to the default log file( oCn:cLogFile )

If oCn:lShowErrors or oCn:lShowMessages is set to .T.,
the error is displayed.

Does this meet your requirement?

Posted: **Mon Oct 31, 2016 4:26 pm**

Does Restore method a SET_FOREIGN_KEY_CHECKS=0 command before execute sql file? I have several errors with foreign keys index executing sql from heidysql, and Restore method don't restore database.

Posted: **Mon Oct 31, 2016 5:01 pm**

Yes
Please try restoring a full database with a different name and let us have your feed back.

Take backup
oCn:BackUp( cDatabase, cBackupFile )

Restore by database by other name

oCn:Restore( cBackUpFile, nil, nil, cOtherDB )

Posted: **Wed Nov 02, 2016 7:56 am**

Restore method dont restore using alternate database name, same issue. On both questions about creation and Use i answer YES, method restore return EMPTY (no NIL) and dont restore database.

Posted: **Wed Nov 02, 2016 8:59 am**

Code: Select all

   HB_LangSelect("ES")
   HB_CDPSelect("ESWIN")
   FWSetLanguage( 2 )

   oCn := mysql_Connect( cHost, cUsr, cPsw)
   ? cResult:=oCn:Backup('prova','c:\pp\prova.sql')
   ? oCn:Restore("c:\pp\prova.sql",NIL,NIL,"prueba")

Windows 10 client
Mariadb 5.5.51
FW1608

Posted: **Fri Nov 04, 2016 3:44 am**

Method BackUp() returns backup file name.
Method Restore() was returning NIL. Now modified to return .T. on success and .f. on failure.

During development, we kept some msgyesno() for testing. We regret that we forgot to remove them. Now we removed these messages.

Some more enhancements have been made after release of FWH 16.10. Backup/Restrore now handles :
1) Tables with calculated virtual columns.
2) Tables with fields of type geometry.

Binary Data:
Backup/Restore system works perfectly as long as binary data (like images, documents, etc) is stored in BLOB fields but not in TEXT fields. Storing binary data in text fields is not a good practice, though this might seem be working in some cases. Before using backup/restore, please ensure that binary data is not stored in TEXT fields.

TEST:
In case of full installation of recent versions of MySql, with examples, a sample database by name `sakila` is also automatically installed. This database contains tables, views, triggers, stored procedures and functions.

This is a fully normalized database and consequently many tables reference others by foreign keys. In one case there is even mutual referencing. This database is a good example how to normalize our databases.

This database, with all its complexities, is ideal for testing FWH BackUp and Restore functionality.

We executed the following code to backup the database `sakila` and then restore it with another name `sakira`.

Code: Select all

function TestBackUpRestore()

   local cBackFile, cOldDb, cNewDB

   oCn:SelectDB( "fwh" )
   cBackFile   := "c:\tests\sakila.sql"
   ? "Backup Start"
   ? oCn:BackUp( "sakila", cBackFile )

   oCn:BackUpIndex( cBackFile, .t. )

   ? "Restore Start"
   ? oCn:Restore( cBackFile, nil, nil, "sakira" )

   xbrowser oCn:ListTables() title oCn:CurrentDB()

return nil

Now we compare the tables of the original database `sakila` with restored database `sakira`.
We used this code to compare the tables. This code can be used to compare other cases also.

Code: Select all

function DbCompare( db1, db2 )

   local aTable1, aTable2

   aTable1  := TableInfo( db1 )
   aTable2  := TableInfo( db2 )
   AEval( aTable2, { |a| AAdd( aTable1, a ) } )
   ASort( aTable1, nil, nil, { |x,y| x[ 2 ] + x[ 1 ] < y[ 2 ] + y[ 1 ] } )

   xbrowser aTable1 autofit SETUP XBrSetup( oBrw )

return nil

//----------------------------------------------------------------------------//

static function XBrSetup( oBrw )

   oBrw:cHeaders := { "DB","TABLE","ROWS","REFERENCES","REFERENCED-BY" }
   oBrw:bClrStd  := { || { CLR_BLACK, If( oBrw:KeyNo % 2 == 0, RGB(160,225,255), CLR_WHITE ) } }

return nil

//----------------------------------------------------------------------------//

static function TableInfo( db )

   local aTables  := {}
   local n, tmp

   AEval( oCn:ListBaseTables( nil, db ), { |c| ;
      AAdd( aTables, { db, c, ;
         oCn:QueryResult( "SELECT COUNT(*) FROM " + db + "." + c ), ;
         "", "" } ) } )

   AEval( aTables, { |a| a[ 4 ] := FW_ArrayAsList( oCn:FKReferencedTables( a[ 1 ] + "." + a[ 2 ] ) ) } )
   for n := 1 to Len( aTables )
      tmp   := oCn:FKReferencedBy( aTables[ n, 2 ], aTables[ n, 1 ] )
      if ! Empty( tmp )
         tmp         := ArrTranspose( tmp )[ 1 ]
         AEval( tmp, { |c,i| tmp[ i ] := AfterAtNum( ".", c ) } )
         aTables[ n, 5 ]   := FW_ArrayAsList( tmp )
      endif
   next

return aTables

Result of comparison

White lines belong to the original database and the blue lines refer to the restored database. This table compares the foreign key relationships also in the original and restored databases.

It may be seen that the tables `store` and `staff` reference each other. Also, the table `address` has a field `location` which is of type "geometry".

After tables, we now compare all other objects with this code.

Code: Select all

function DbCompare2( db1, db2 )

   local aCmp  := {}

   ObjComp( "VIEW",      db1, db2, oCn:ListViews(      nil, db1, .t. ), oCn:ListViews(      nil, db2, .t. ), aCmp )
   ObjComp( "TRIGGER",   db1, db2, oCn:ListTriggers(   nil, db1, .t. ), oCn:ListTriggers(   nil, db2, .t. ), aCmp )
   ObjComp( "PROCEDURE", db1, db2, oCn:ListProcedures( nil, db1, .t. ), oCn:ListProcedures( nil, db2, .t. ), aCmp )
   ObjComp( "FUNCTION",  db1, db2, oCn:ListFunctions(  nil, db1, .t. ), oCn:ListFunctions(  nil, db2, .t. ), aCmp )

   xbrowser aCmp SETUP ( oBrw:cHeaders := { "OBJECT", "NAME", "TABLE", db1 + "==" + db2 + "?" } ) ;
      TITLE "COMPARE OBJECTS"

return nil

//----------------------------------------------------------------------------//

static function ObjComp( cObj, db1, db2, a1, a2, aCmp )

   local nAt, n, c, aa
   local nCol  := If( cObj == "TRIGGER", 3, 2 )

   for n := 1 to Len( a1 )
      aa    := { cObj, a1[ n, 1 ], "", .f. }
      nAt   := AScan( a2, { |a| a[ 1 ] == a1[ n, 1 ] } )
      if nAt > 0
         if nCol > 2
            aa[ 3 ] := a1[ nAt, 2 ]
         endif
         c        := StrTran( a2[ nAt, nCol ], db2, db1 )
         aa[ 4 ]  := ( a1[ n, nCol ] == c )
      endif
      AAdd( aCmp, aa )
   next

return aCmp

This code compares whether all objects are restored or not and also whether the text restored is identical to the original or not.

Result:

These tests on a complex database like `sakila` indicate satisfactory functioning of FWH's Backup/Restore feature.

We welcome more and more tests and feedback.

Note: This functionality is available in the revised build soon to be published.

Posted: **Fri Nov 04, 2016 1:56 pm**

I'm testing a backup of our database (about 2.4 millon records) and I get this error:

Estoy probando respaldar nuestra base de datos ( aprox. 2.4 millones de registros) y estoy obteniendo este error.

Code: Select all

Application
===========
   Path and name: D:\backupsql\SqlBackup.exe (32 bits)
   Size: 3,644,928 bytes
   Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20150603)
   FiveWin  Version: FWHX 16.10
   Windows version: 6.1, Build 7601 Service Pack 1

   Time from start: 0 hours 1 mins 1 secs 
   Error occurred at: 04/11/2016, 10:41:31
   Error description: Error BASE/1068  Error de argumento: acceso al array
   Args:
     [   1] = L   .F.
     [   2] = N   2

Stack Calls
===========
   Called from: .\source\internal\FWMARIA.PRG => FWMARIACONNECTION:BACKUP( 5400 )
   Called from: sqlbackup.prg => RBD( 63 )
   Called from: sqlbackup.prg => (b)RESPALDOBD( 51 )
   Called from: .\source\function\MSGRUN.PRG => (b)MSGRUN( 37 )
   Called from: .\source\classes\DIALOG.PRG => (b)TDIALOG:TDIALOG( 95 )
   Called from:  => TDIALOG:DISPLAY( 0 )
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:HANDLEEVENT( 901 )
   Called from:  => DIALOGBOXINDIRECT( 0 )
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE( 296 )
   Called from: .\source\function\MSGRUN.PRG => MSGRUN( 42 )
   Called from: sqlbackup.prg => RESPALDOBD( 51 )
   Called from: sqlbackup.prg => (b)CREAMENU( 29 )
   Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 1212 )
   Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1086 )
   Called from:  => TWINDOW:HANDLEEVENT( 0 )
   Called from: .\source\classes\WINDOW.PRG => _FWH( 3305 )
   Called from:  => WINRUN( 0 )
   Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 1042 )
   Called from: sqlbackup.prg => SQLBACKUP( 18 )

System
======
   CPU type: Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz 3392 Mhz
   Hardware memory: 7870 megs

   Free System resources: 90 %
        GDI    resources: 90 %
        User   resources: 90 %

   Windows total applications running: 5
      1 ,                                                                                                     
      2 , D:\backupsql\SqlBackup.exe                                                                          
      3 DDE Server Window, C:\Windows\syswow64\OLE32.DLL                                                                       
      4 GDI+ Window, C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.7601.19061_none_72d6d48d866497 
      5 MCI command handling window, C:\Windows\system32\WINMM.DLL                                                                       

Variables in use
================
   Procedure     Type   Value
   ==========================
   FWMARIACONNECTION:BACKUP
     Param   1:    A    Len:   27
     Param   2:    C    "D:\backupsql\stock2.sql"
     Local   1:    U    
     Local   2:    U    
     Local   3:    U    
     Local   4:    O    Class: FWMARIACONNECTION
     Local   5:    C    "stock2"
     Local   6:    U    
     Local   7:    U    
     Local   8:    U    
     Local   9:    N    38433.11
     Local  10:    U    
     Local  11:    N    584
     Local  12:    N    32
     Local  13:    N    31
     Local  14:    U    
     Local  15:    U    
     Local  16:    A    Len:   27
     Local  17:    L    .T.
     Local  18:    L    .F.
     Local  19:    N    2
     Local  20:    U    
     Local  21:    A    Len:   27
   RBD
   (b)RESPALDOBD
     Param   1:    O    Class: TDIALOG
   (b)MSGRUN
     Param   1:    U    
   (b)TDIALOG:TDIALOG
     Param   1:    O    Class: TDIALOG
   TDIALOG:DISPLAY
   TDIALOG:HANDLEEVENT
     Param   1:    N    15
     Param   2:    N    0
     Param   3:    N    0
     Local   1:    O    Class: TDIALOG
   DIALOGBOXINDIRECT
     Param   1:    N    4194304
     Param   2:    C    "€         Ãÿàÿo      R e s p a l d o   M y s q l / M a r i a D B   "
     Param   3:    N    3672556
     Param   4:    O    Class: TDIALOG
   TDIALOG:ACTIVATE
     Param   1:    U    
     Param   2:    U    
     Param   3:    B    {|| ... }
     Param   4:    L    .T.
     Param   5:    U    
     Param   6:    L    .T.
     Param   7:    U    
     Param   8:    U    
     Param   9:    U    
     Param  10:    U    
     Param  11:    U    
     Local   1:    O    Class: TDIALOG
     Local   2:    N    3672556
     Local   3:    U    
     Local   4:    U    
     Local   5:    U    
     Local   6:    O    Class: TDIALOG
   MSGRUN
     Param   1:    C    "Inicia Respaldo de Datos"
     Param   2:    C    "Respaldo Mysql/MariaDB"
     Param   3:    B    {|| ... }
     Local   1:    O    Class: TDIALOG
     Local   2:    N    221
     Local   3:    U    
   RESPALDOBD
   (b)CREAMENU
     Param   1:    O    Class: TMENUITEM
   TMENU:COMMAND
     Param   1:    N    20001
     Local   1:    O    Class: TMENU
     Local   2:    O    Class: TMENUITEM
   TWINDOW:COMMAND
     Param   1:    N    20001
     Param   2:    N    0
     Local   1:    O    Class: TWINDOW
     Local   2:    N    0
     Local   3:    N    20001
     Local   4:    N    0
     Local   5:    U    
   TWINDOW:HANDLEEVENT
     Param   1:    N    273
     Param   2:    N    20001
     Param   3:    N    0
   _FWH
     Param   1:    N    0
     Param   2:    N    273
     Param   3:    N    20001
     Param   4:    N    0
     Param   5:    N    1
     Local   1:    O    Class: TWINDOW
   WINRUN
     Param   1:    N    3672556
   TWINDOW:ACTIVATE
     Param   1:    C    "NORMAL"
     Param   2:    U    
     Param   3:    U    
     Param   4:    U    
     Param   5:    U    
     Param   6:    U    
     Param   7:    U    
     Param   8:    U    
     Param   9:    U    
     Param  10:    U    
     Param  11:    U    
     Param  12:    U    
     Param  13:    U    
     Param  14:    U    
     Param  15:    U    
     Param  16:    U    
     Param  17:    U    
     Param  18:    U    
     Param  19:    U    
     Param  20:    L    .T.
     Local   1:    O    Class: TWINDOW
     Local   2:    U    
     Local   3:    U    
   SQLBACKUP

Linked RDDs
===========
   DBF
   DBFFPT
   DBFBLOB
   DBFCDX
   DBFNTX

DataBases in use
================

Classes in use:
===============
     1 ERROR
     2 HASHENTRY
     3 HBCLASS
     4 HBOBJECT
     5 TWINDOW
     6 TMENU
     7 TMENUITEM
     8 TREG32
     9 TBRUSH
    10 TFONT
    11 TRECT
    12 TDIALOG
    13 FWMARIACONNECTION
    14 TSTRUCT

Memory Analysis
===============
      377 Static variables

   Dynamic memory consume:
      Actual  Value:          0 bytes
      Highest Value:          0 bytes

I export this data to a sql script with other software without problems
Exporto estos datos a un script sql con otro programa sin problemas.

Thanks,
Gracias

Posted: **Fri Nov 04, 2016 3:29 pm**

José/Mr. Nages:

Cuando soluciones los errores podrías compartir qué tan rápido es para
restaurar un respaldo?

Saludos

Mr. Nages, How fast is the restore ?, do you have a TEST?

Regards

Posted: **Fri Nov 04, 2016 5:36 pm**

Yes Armando, of course, results of transfering to dbf are extremely quick, but backup restore for this tables I don't know, but I'm working on it.
Si Armando, por supuesto, los resultados de transferir e importar de dbf son extremadamente rápidos, pero backup/restore aún no lo he podido probar con grandes tablas por el error que comento, les tendré al tanto.

Posted: **Sat Nov 05, 2016 2:54 am**

Mr Jose

The reason must be that the first table in the array of tables does not exist in the database. Please check all table names and try again.

Now we modified the library to skip such invalid table names instead of resulting in run-time error. We'll send you revised libs for testing.

We shall be glad to have feedback on comparison of speeds with other tools using identical tables/database. Feedback we got so far is encouraging. Speeds for tables containing blobs and large _ be slower and that is intentional.

FiveTech Software tech support forums

FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore

Re: FWH 16.06: MariaDb/MySql Backup and Restore

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

Re: FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)