1 Topic by papafrankc (edited by papafrankc 2020-07-04 03:53:36)

  • Registered: 2020-06-26
  • Posts: 302

Topic: Backup and Restore Tables

I'd like anyones thoughts on my 3 scenarios:
Scenario 1
- I plan to distribute my application to various users. 
- I want them to be able to backup the tables
- So I assume they could just make a copy of the sqlite.db file and save it somewhere?
- However lets just assume that they are non-technical folks and this procedure would be too much for them.
- I'd like to put a button called BACKUP on my main form so that they could just click it to create the backup?
- Has anyone ever done this & if so is there code available?
-
Scenario 2
- If I make changes to my forms and want to send them to my users, I assume I could send them a new program with zero data in the tables.  Then they could just replace/restore sqlite.db with the saved copy from the backup.
- Since they are non-technical folks could I do this with a button and code?
-
- Scenario 3
- Same problem as Scenario 2, except I have changed the structure/fields of one or more of the original tables.
- I assume this will be the most complex scenario since data will have to be taken from the original tables and appended to the new tables.
- Any suggestions?
-
- In an ideal world my tables will always remain the same and the structure will never need updating. However in the real world I can see where changes might have to made to the structures.
- So it looks like Scenario 1 would be a good starting point.
- Any thoughts on 1 or all 3 of the scenarios will be appreciated. (I'm still loving MVD) smile
-I was just thinking, is there any thought of a backup/restore feature for MVD?
-
Thanks, Frank

  • Registered: 2014-08-02
  • Posts: 966

Re: Backup and Restore Tables

Hello papafrankc

For Scenario 1 :

If the sqlite.db file is in the project folder, you will need to create a 'Backup' folder.

procedure Form1_btBackup_OnClick (Sender: string; var Cancel: boolean);
begin
          if 7 = MessageDlg('Create / Update the SqLiteDB? Best choice is every day on the end of the shift back it up.', mtConfirmation, mbYes + mbNo, 0) then
          begin
                Cancel := True;  // I think it is not necessary
                CopyFile('sqlite.db', 'Backup\sqlite.db'); // Application path
                //CopyFile('sqlite.db', 'I:\TP2014\Backup\sqlite.db'); // Reserve path
                //P:\C\CF\CF3\TEST GA en MB\TP2014\Backup\sqlite.db  // Backup path workplace
          end;
end;

or

procedure MenuClick1 (Sender: string);
begin
   CopyFile('sqlite.db', 'backup/backup '+ FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.db');
end;

For Scenario 2 :

If you change your forms (cosmetic changes) without modify structures of your tables, a simple <MyTable(s).dbUpdate> will be enoughe behind
<MyForm(s).OnShow>.

For Scenario 3 :

If you modify your table(s) structures (if you delete or add or new fields (mostly linked fields), data of those new fields will be empty.
Users will have to fill them manually (so new fields will be filled ! LOL !)

Hope this can help you but MVD users could have others ways to do that

JB

3 Reply by derek (edited by derek 2020-07-04 16:54:13)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,835

Re: Backup and Restore Tables

Hello Frank, Salut Jean,
For Scenario 1, I take it out of the users' hands completely and just do a back-up every time the application closes (but these are single users with quite small applications so I don't have to worry about any contention issues or major time delays).  But it's just as easy to assign it to a button.
With regard to the back-up itself, I go 'old school' and simply back up the entire application with a batch file that gets called as the application shuts down. 
Have a look at the simple example in the attachment (just edit 'backup.bat' for your own needs).
But as Jean writes, we probably all have slightly different ways of doing it.
Regards,
Derek.

Post's attachments

Attachment icon contacts.zip 370.9 kb, 441 downloads since 2020-07-04 

  • Registered: 2020-06-26
  • Posts: 302

Re: Backup and Restore Tables

Many thanks to both Derek and Jean,

I'm going to check out your solutions and see about incorporating them into my application(s).
-
This forum is a lifesaver to a newbie like myself smile
-
Derek, you mention a 'backup.bat' file, but I don't see it?
-
Thanks
Frank

5 Reply by derek (edited by derek 2020-07-04 19:29:53)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,835

Re: Backup and Restore Tables

Hi,
It should be in the 'contacts' folder when you unzip contacts.zip.
Derek.

Post's attachments

Attachment icon contacts.jpg 177.88 kb, 154 downloads since 2020-07-04 

  • Registered: 2020-05-11
  • Posts: 32

Re: Backup and Restore Tables

Hi, Derek,
Please give a little more explanation for us beginners.
What means:

"  (just edit 'backup.bat' for your own needs)."

It is not clear to me how backup is achieved.
Thanks in advance.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,835

Re: Backup and Restore Tables

Hi Zlaya,
Using the example of the 'contacts' application (earlier in this thread), at the end of the script, there is the following procedure:
procedure Form1_OnClose (Sender: TObject; Action: string);  //*** full back up on shut-down
begin
  openfile('backup.bat');
end;
This instructs the program, when it shuts down, to run a batch file called 'backup.bat' which is a small external program written in MS-DOS.
It does the following 3 things:
1.  rmdir "C:\Documents and Settings\user\Desktop\contacts backup" /s /q  (removes the current backup -  /s and /q allow folders that aren't empty to be removed without the user being prompted).
2.  mkdir "C:\Documents and Settings\user\Desktop\contacts backup"  (re-creates the folder where the new backup will be copied to).
3.  xcopy *.* "C:\Documents and Settings\user\Desktop\contacts backup" /e  (copies everything from the folder where the batch file is located - /e copies folders even if they are empty).
You can use something like Notepad to edit the batch file and replace the folder names with ones appropriate to your application.. 
I wrote it way back when using MVD Version 1.5 when there wasn't much choice.  These days with options like 'createdir', 'directorycopy', 'copyfile' etc, it might be better to go with something like Jean's suggestion earlier in the thread.
Derek.