[Solved] Backup SQLite db file from within progam

[bobonwhidbey]

I saw a 6 year old thread on this, but it didn't work for me. I'm trying to make a copy of a SQLite .db file within my app before I make edits/deletes on the file. I get an "Unable to open file" error.  My coding:

Code: Pascal  [Select][+][-]

1. function AFileCopy(const FSrc, FDst: string): boolean;
2. var
3.   sStream, dStream: TFileStream;
4. begin
5.   sStream := TFileStream.Create(FSrc, fmOpenRead);
6.   try
7.     dStream := TFileStream.Create(FDst, fmCreate);
8.     try
9.       dStream.CopyFrom(sStream, 0);
10.       Result := True;
11.     finally
12.       dStream.Free;
13.     end;
14.   finally
15.     sStream.Free;
16.     Result := False;
17.   end;
18. end;
19.  
20. procedure TMyForm.MakeBackupCopy(FileName : string);
21. var
22.   f: string;
23. begin
24.   Trans.Active := False; // this is a TSQLTransaction associated with a DBGrid
25.   f := ChangeFileEXT(FileName, '') + ' | ' +  FormatDateTime('yyyy-mm-dd hh:nn:ss', Now) + '.db';
26.   if not AFileCopy(FileName, f) then
27.     ShowMessage('Copy file error');
28.   Trans.Active := True;
29. end;
30.  

[totya]

This code is completely wrong, becasue AFileCopy return value is always false (finally block).

[Fungus]

Try this:

Code: Pascal  [Select][+][-]

1. function AFileCopy(const FSrc, FDst: string): boolean;
2. var
3.   sStream, dStream: TFileStream;
4. begin
5.   Result := False; //Moved
6.   sStream := TFileStream.Create(FSrc, fmOpenRead);
7.   try
8.     dStream := TFileStream.Create(FDst, fmCreate);
9.     try
10.       dStream.CopyFrom(sStream, sStream.Size); //Define how many bytes should be copied, 0 means nothing..
11.       Result := True;
12.     finally
13.       dStream.Free;
14.     end;
15.   finally
16.     sStream.Free;
17.   end;
18. end;

[totya]

..and this:

anyname | 2016-10-23 20:21:49.db

it isn't a good filename...

[bobonwhidbey]

Totya is right - the file name had illegal characters, so it's now:

Code: Pascal  [Select][+][-]

1.     f := ChangeFileEXT(INI.FileName, '') +
2.       FormatDateTime('[yyyy-mm-dd hh-nn-ss]', Now) + '.sql';  
3.  

And I've made the changes suggest by Fungus, however the problem still remains - "Unable to open file"  I think the problem is that the .db file is open. Trans.Active := false doesn't release it.

[totya]

I suggest to you, create a simple txt file, for example "test.txt" with inside for example: "abcd", and test your AFileCopy function alone with this test file.

I suggest use the FileExists function too, because is the file doesn't exists, you get "unable to open file" message too...

edit1:
You can copy opened file with it:

Code: Pascal  [Select][+][-]

1. fmOpenRead or fmShareDenyNone

But much better if the opened file closed and saved...

edit2:
I use the TSqlite3Dataset component, but I guess you need this too:

Code: Pascal  [Select][+][-]

1. SQLTransaction1.Active:= false;
2. SQLite3Connection1.Close;

[bobonwhidbey]

Thanks totya - the most important change was fmOpenRead or fmShareDenyNone. It made no difference if I set Trans.Active := false, or not. The following is successful code.

Code: Pascal  [Select][+][-]

1. function AFileCopy(const FSrc, FDst: string): boolean;
2. var
3.   sStream, dStream: TFileStream;
4. begin
5.   Result := False;
6.   sStream := TFileStream.Create(FSrc, fmOpenRead or fmShareDenyNone);
7.   try
8.     dStream := TFileStream.Create(FDst, fmCreate);
9.     try
10.       dStream.CopyFrom(sStream, sStream.Size);
11.       Result := True;
12.     finally
13.       dStream.Free;
14.     end;
15.   Except
16.      sStream.Free;
17.   end;
18. end;
19.  
20. procedure TMyForm.MakeBackupCopy(FileName : string);
21. var
22.   f: string;
23. begin
24.   f := ChangeFileEXT(FileName, '') +  FormatDateTime(' [yyyy-mm-dd hh-nn-ss]', Now) + '.sql';
25.   if not AFileCopy(FileName, f) then
26.       ShowMessage('copy file error');
27. end;

[totya]

I highly recommend to you, see the edit2 section in my previous message:

Code: Pascal  [Select][+][-]

1. SQLite3Connection1.Close

See for examples:
http://wiki.freepascal.org/SQLite
"Creating a Database" section.

[kapibara]

Not to be funny, but to copy the file there is also call CopyFile: http://wiki.freepascal.org/CopyFile

[totya]

Not to be funny, but to copy the file there is also call CopyFile: http://wiki.freepascal.org/CopyFile

Hi!

And then what? Many ways available, if you want to copy a file. At the moment, the topic starter (bobonwhidbey) can't close the database, so the FileUtil.CopyFile unusable here, because this simple function cannot copy the opened file. Under windows, Windows.CopyFileW works in this case.

[shobits1]

According to SQLite site this is not recommended (using CopyFile)
https://www.sqlite.org/backup.html

Historically, backups (copies) of SQLite databases have been created using the following method:
    1.Establish a shared lock on the database file using the SQLite API (i.e. the shell tool).
    2.Copy the database file using an external tool (for example the unix 'cp' utility or the DOS 'copy' command).
    3.Relinquish the shared lock on the database file obtained in step 1.

This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings:
    *Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished.
    *It cannot be used to copy data to or from in-memory databases.
    *If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery.

The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database, overwriting the original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue uninterrupted while a backup of an online database is made.

And According to FPC wiki http://wiki.freepascal.org/SQLite#sqlite3backup

sqlite3backup
sqlite3backup is a unit provided with FPC (not in Lazarus but can be used programmatically) that provides backup/restore functionality for SQLite3. It uses SQLDB's sqlite3conn.

it has TSQLite3Backup class which uses Online Backup API

I hope it helps you.

[bobonwhidbey]

Thanks Shobits1. This works great.

Code: Pascal  [Select][+][-]

1. uses
2.   sqlite3backup;
3.  
4. procedure BackupSQL(Conn: TSQLite3Connection; FileName: string);
5. var
6.   f: string;
7.   BK: TSQLite3Backup;
8. begin
9.   f := ChangeFileEXT(FileName, '') + ' [Backup ' +
10.     FormatDateTime('yyyy.mm.dd hh-nn-ss', Now) + '].sql';
11.   BK := TSQLite3Backup.Create;
12.   BK.Backup(Conn, f);
13.   BK.Free;
14. end

;